如何在BigQuery中合并同一ID下日期连续的行（保留短区间行）

要实现同一客户（id）下连续日期区间的消费合并（即前一行的end_date等于后一行的start_date），同时保留独立的非连续区间，可以通过窗口函数分组后聚合的方式完成，具体SQL如下：

WITH ranked_data AS (
  SELECT
    id,
    start_date,
    end_date,
    amount,
    -- 获取当前行的上一行结束日期
    LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) AS prev_end_date
  FROM customer_spending
),
grouped_data AS (
  SELECT
    *,
    -- 标记新分组的起始行：无前置行或当前行起始日期与上一行结束日期不连续
    CASE WHEN prev_end_date IS NULL OR start_date != prev_end_date THEN 1 ELSE 0 END AS group_flag,
    -- 累计生成分组ID，同一连续区间的行将拥有相同ID
    SUM(CASE WHEN prev_end_date IS NULL OR start_date != prev_end_date THEN 1 ELSE 0 END) 
      OVER (PARTITION BY id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM ranked_data
)
SELECT
  id,
  MIN(start_date) AS start_date, -- 取分组内最早的起始日期
  MAX(end_date) AS end_date,     -- 取分组内最晚的结束日期
  SUM(amount) AS amount          -- 求和分组内的消费金额
FROM grouped_data
GROUP BY id, group_id
ORDER BY id, start_date;

逻辑说明： #

1. ranked_data CTE：按客户ID分组、起始日期排序，用LAG函数获取每行的上一行结束日期，用于判断区间是否连续。
2. grouped_data CTE：
   • 用group_flag标记每个新分组的起始行（首次出现的行或与上一行不连续的行）。
   • 通过累计求和group_flag生成group_id，同一连续区间的行将被分配相同的分组ID。
3. 最终聚合：按客户ID和分组ID聚合，取分组内最小起始日期、最大结束日期，求和消费金额，得到合并后的结果。

该方案会自动区分独立的非连续区间（如示例中ID=1的2022-02-18至2022-03-20行），因为它的起始日期与前一行的结束日期不连续，会被标记为新分组，不会参与其他连续区间的合并。

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