如何在BigQuery中查询当前ID对应其余ID的最大日期

原方案性能瓶颈核心是将全量明细行与ID维度聚合结果做CROSS JOIN，产生的笛卡尔积行数为「明细总行数 * ID数量」，数据量越大性能损耗越严重，可通过提前聚合+轻量计算的思路彻底避免笛卡尔积操作。

优化核心思路 #

• 先按ID维度做一次聚合，得到每个ID对应的最大日期，仅保留ID数量级的行数，远小于明细行数量
• 在ID维度的小结果集上计算每个ID对应的「其他ID最大日期」，无大表关联开销
• 将计算好的两个日期值关联回原明细行即可得到最终结果

通用兼容实现（支持所有主流SQL引擎） #

WITH t AS (
    -- 此处替换为你的原表逻辑
    SELECT 1 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-09-01','2021-09-09', INTERVAL 1 DAY)) rep_date
    UNION ALL 
    SELECT 2 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-08-20','2021-09-03', INTERVAL 1 DAY)) rep_date
    UNION ALL 
    SELECT 3 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-08-25','2021-09-05', INTERVAL 1 DAY)) rep_date    
),
-- 按ID聚合得到每个ID的最大日期，行数为ID总数
t_id_max AS (
    SELECT id, MAX(rep_date) AS id_max_date
    FROM t
    GROUP BY 1
),
-- 计算全局最大日期统计值，仅返回1行
global_date_stats AS (
    SELECT
        MAX(id_max_date) AS first_max,
        MAX(CASE WHEN id_max_date < (SELECT MAX(id_max_date) FROM t_id_max) THEN id_max_date END) AS second_max,
        COUNT(DISTINCT CASE WHEN id_max_date = (SELECT MAX(id_max_date) FROM t_id_max) THEN id END) AS first_max_id_count
    FROM t_id_max
),
-- 为每个ID计算其他ID的最大日期，仅处理ID数量级的行数
t_id_with_other_max AS (
    SELECT
        a.id,
        a.id_max_date,
        CASE
            WHEN a.id_max_date < b.first_max THEN b.first_max
            WHEN b.first_max_id_count > 1 THEN b.first_max
            ELSE b.second_max
        END AS max_date_over_others
    FROM t_id_max a
    CROSS JOIN global_date_stats b
)
-- 关联回原明细行得到结果
SELECT 
    a.id,
    a.rep_date,
    b.id_max_date AS max_date,
    b.max_date_over_others
FROM t a
JOIN t_id_with_other_max b USING(id)

简洁写法（支持窗口函数EXCLUDE语法的引擎，如BigQuery、PostgreSQL 11+） #

WITH t AS (
    -- 此处替换为你的原表逻辑
    SELECT 1 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-09-01','2021-09-09', INTERVAL 1 DAY)) rep_date
    UNION ALL 
    SELECT 2 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-08-20','2021-09-03', INTERVAL 1 DAY)) rep_date
    UNION ALL 
    SELECT 3 AS id, rep_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-08-25','2021-09-05', INTERVAL 1 DAY)) rep_date    
),
t_id_max AS (
    SELECT id, MAX(rep_date) AS id_max_date
    FROM t
    GROUP BY 1
),
t_id_with_other_max AS (
    SELECT
        id,
        id_max_date,
        MAX(id_max_date) OVER (
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            EXCLUDE CURRENT ROW
        ) AS max_date_over_others
    FROM t_id_max
)
SELECT a.id, a.rep_date, b.id_max_date max_date, b.max_date_over_others
FROM t a
JOIN t_id_with_other_max b USING(id)

性能提升说明 #

• 原方案复杂度为O(N*M)，N为明细行数，M为ID数量，新方案复杂度为O(N+M)，ID越多性能提升越明显
• 避免了大表笛卡尔积的生成，内存占用也会大幅降低

内容的提问来源于stack exchange，提问作者Timogavk