BigQuery高效实现按ID获取每行近6个月历史日期数组

场景说明 #

待处理表为DATES_EVENTS，总数据量20T，表结构与样例数据如下：

ID      DATE   
1   '2022-04-01'
1   '2022-03-02'
1   '2022-03-01'
2   '2022-05-01'
3   '2021-12-01'
3   '2021-11-11'
3   '2020-11-11'
3   '2020-10-01'

需求为按ID分组，为每一行返回当前行日期向前追溯6个月（含当日）范围内所有历史日期组成的数组，期望输出样例如下：

ID      DATE                   DATE_list
1   '2022-04-01'   ['2022-04-01','2022-03-02','2022-03-01']
1   '2022-03-02'   ['2022-03-02','2022-03-01']
1   '2022-03-01'   ['2022-03-01']
2   '2022-05-01'   ['2022-05-01']
3   '2021-12-01'   ['2021-12-01','2021-11-11']
3   '2021-11-11'   ['2021-11-11']
3   '2020-11-11'   ['2020-11-11','2020-10-01']
3   '2020-10-01'   ['2020-10-01']

现有无范围限制的窗口聚合逻辑性能可接受，但自连接实现的6个月限制方案存在严重性能问题，无法适配20T级数据量。

核心实现 #

直接使用窗口函数的范围滑动帧语法，完全避免自连接产生的O(n²)级计算量，性能和无范围限制的窗口聚合基本一致：

SELECT 
  ID, 
  DATE, 
  ARRAY_AGG(DATE) OVER (
    PARTITION BY ID 
    ORDER BY UNIX_DATE(DATE)
    RANGE BETWEEN 180 PRECEDING AND CURRENT ROW
  ) AS DATE_list
FROM DATES_EVENTS

*注：上述写法按180天近似6个月，若需要严格自然月规则可调整帧定义。

各主流引擎适配 #

• Spark SQL：将UNIX_DATE(DATE)替换为DATEDIFF(DATE, '1970-01-01')即可，其余语法不变
• Hive：需使用2.3及以上版本（低版本不支持数值型排序键的RANGE帧），日期转天数值写法同Spark SQL
• Trino/Presto/BigQuery：原生支持INTERVAL类型的范围帧，可直接写严格自然月逻辑，无需转天数：

  SELECT 
    ID, 
    DATE, 
    ARRAY_AGG(DATE) OVER (
      PARTITION BY ID 
      ORDER BY DATE
      RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW
    ) AS DATE_list
  FROM DATES_EVENTS
  

性能原理 #

• 自连接方案：对每个ID分区内的数据做笛卡尔匹配，单ID下k条记录的计算复杂度为O(k²)，大ID分区下会产生天量中间shuffle数据，20T量级下基本无法跑完
• RANGE滑动窗口方案：仅需对每个ID分区内的数据按日期做一次排序，顺序遍历行时维护滑动聚合集合，计算复杂度为O(k log k)（仅排序开销），中间数据量和原表基本持平，可支撑20T级数据计算

额外优化建议 #

• 若表可提前按ID分桶、按DATE排序存储，可直接跳过窗口计算时的分区排序步骤，性能可提升30%以上
• 若单ID下6个月窗口内的日期条数过多（如单ID每日产生记录，单窗口超200条），建议提前过滤无效日期，降低ARRAY_AGG的数组构建开销

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