如何将BigQuery自连接查询替换为窗口函数以优化性能？

当然可以用窗口函数来优化这个场景！核心思路是先填充缺失的日期-事件类型行，让每个事件类型在所有日期都有记录（count为0），之后就能用LAG()窗口函数轻松获取N天前的计数了。而且这个方法比自连接高效得多，完全符合BigQuery的最佳实践。

具体实现步骤 #

1. 生成完整的日期-事件类型网格：先从原表中提取所有事件类型，再生成覆盖所有存在数据的日期范围，交叉连接得到每个类型在每个日期的组合。
2. 填充缺失的计数：将这个完整网格左连接原表，把没有数据的行的count填充为0。
3. 用窗口函数获取历史值：按事件类型分区、日期排序，用LAG()函数直接取N天前的计数，最后用COALESCE()处理边界情况（比如日期范围内前N天没有数据的情况）。

优化后的查询代码 #

WITH events AS (
 SELECT DATE('2019-06-08') AS day, 'a' AS type, 1 AS count
 UNION ALL
 SELECT '2019-06-09', 'a', 2
 UNION ALL
 SELECT '2019-06-10', 'a', 3
 UNION ALL
 SELECT '2019-06-07', 'b', 4
 UNION ALL
 SELECT '2019-06-09', 'b', 5
),
-- 步骤1：获取所有事件类型和完整日期范围
all_types AS (SELECT DISTINCT type FROM events),
date_range AS (
 SELECT day
 FROM UNNEST(GENERATE_DATE_ARRAY(
   (SELECT MIN(day) FROM events),
   (SELECT MAX(day) FROM events),
   INTERVAL 1 DAY
 )) AS day
),
-- 步骤2：生成完整的日期-类型网格，填充count为0
full_events AS (
 SELECT dt.day, t.type, COALESCE(e.count, 0) AS count
 FROM date_range dt
 CROSS JOIN all_types t
 LEFT JOIN events e ON dt.day = e.day AND t.type = e.type
)
-- 步骤3：用LAG窗口函数获取2天前的计数
SELECT 
 type,
 day,
 count,
 COALESCE(LAG(count, 2) OVER (PARTITION BY type ORDER BY day), 0) AS prev_count
FROM full_events
ORDER BY type, day

为什么这个方法比自连接更快？ #

• 自连接是**O(n²)的操作，当数据量增大时性能会急剧下降；而窗口函数是O(n)**的线性操作，BigQuery对窗口函数的优化非常成熟。
• 生成完整网格的操作中，GENERATE_DATE_ARRAY和CROSS JOIN在BigQuery中都是高效的向量操作，远快于自连接的笛卡尔积计算。
• 即使事件类型数量很多，DISTINCT type的计算成本也远低于自连接带来的重复匹配开销。

额外说明 #

如果你的日期范围需要固定（比如最近90天），可以直接替换GENERATE_DATE_ARRAY的参数，比如GENERATE_DATE_ARRAY(CURRENT_DATE()-90, CURRENT_DATE())，不需要依赖原表的最小/最大日期。

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