在BigQuery中合并多条记录为单条记录的问题排查

输入表 #

期望输出表 #

当前尝试的SQL语句 #

SELECT pdt, amount, MIN(start_dt) start_dt, MAX(end_dt) end_dt FROM (
  SELECT *, dt - COUNT(1) OVER (PARTITION BY pdt ORDER BY start_dt) AS part
    FROM table, UNNEST (GENERATE_ARRAY(
      UNIX_DATE(start_dt), UNIX_DATE(end_dt))
    ) AS dt
) GROUP BY pdt, amount, part

实际输出表 #

问题分析 #

原SQL仅按pdt分区生成分组标识，未结合amount的变化逻辑，导致同一pdt下所有同amount的记录被强制合并，忽略了中间不同amount的间隔（比如商品a的0.5记录被2.2记录隔开，本应分为两组，却被合并成了一组）。

修正后的SQL语句 #

WITH ordered_data AS (
  SELECT 
    pdt, 
    amount, 
    start_dt, 
    end_dt,
    -- 按商品和金额分组，按开始时间排序，获取上一条同组记录的结束日期
    LAG(end_dt) OVER (PARTITION BY pdt, amount ORDER BY start_dt) AS prev_end_dt
  FROM `your_table_name` -- 替换为实际表名
),
grouped_data AS (
  SELECT
    *,
    -- 当前记录与上一条同组记录时间不连续时，标记为新组，累加生成分组ID
    SUM(CASE WHEN start_dt = DATE_ADD(prev_end_dt, INTERVAL 1 DAY) THEN 0 ELSE 1 END) 
      OVER (PARTITION BY pdt, amount ORDER BY start_dt) AS group_id
  FROM ordered_data
)
SELECT
  pdt,
  amount,
  MIN(start_dt) AS start_dt,
  MAX(end_dt) AS end_dt
FROM grouped_data
GROUP BY pdt, amount, group_id
ORDER BY pdt, start_dt;

说明 #

1. ordered_data：按pdt和amount双维度分区，对记录按start_dt排序，通过LAG函数获取同商品同金额的上一条记录的结束日期。
2. grouped_data：判断当前记录的start_dt是否与上一条同组记录的end_dt连续（即次日），不连续则标记为新组，通过累加生成唯一的group_id。
3. 最后按pdt、amount和group_id聚合，取每组的最小开始日期和最大结束日期，得到期望的合并结果。

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