基于值与日期变化的BigQuery SQL分组查询方案请求

原始数据 #

尝试的SQL语句 #

WITH temp AS (
  SELECT
    id,
    value,
    start_date,
    end_date,
    LAG(end_date) OVER (PARTITION BY id, value ORDER BY start_date) AS prev_end_date
  FROM
    table_a
)    
SELECT
  id,
  value,
  MIN(start_date) AS start_date,
  MAX(end_date) AS end_date
FROM
  temp
GROUP BY
  id,
  value,
  DATE_DIFF(start_date, prev_end_date, DAY) IS NULL
ORDER BY
  id,
  start_date;

期望输出 #

问题分析 #

原有SQL的PARTITION BY id, value会把同一id下所有相同value的行归到同一分区，无法区分同一value非连续出现的分段（比如id=1的value=100分两段出现），导致聚合后结果不符合Type 2维度表的分段要求。

正确的BigQuery SQL语句 #

采用分组岛屿技术，标记连续相同value的行组后再聚合：

WITH ranked_data AS (
  SELECT
    id,
    value,
    start_date,
    end_date,
    -- 标记当前行与前一行value是否不同，不同则触发新组
    CASE WHEN LAG(value) OVER (PARTITION BY id ORDER BY start_date) != value THEN 1 ELSE 0 END AS group_flag
  FROM table_a
),
grouped_data AS (
  SELECT
    id,
    value,
    start_date,
    end_date,
    -- 累加标记得到唯一组ID，同一连续value段的组ID一致
    SUM(group_flag) OVER (PARTITION BY id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM ranked_data
)
SELECT
  id,
  value,
  MIN(start_date) AS start_date,
  MAX(end_date) AS end_date
FROM grouped_data
GROUP BY id, value, group_id
ORDER BY id, start_date;

结果说明 #

1. 第一步通过LAG(value)判断当前行与前一行的value是否变化，生成组起始标记
2. 第二步累加标记得到每个连续value段的唯一ID
3. 最后按id、value、组ID聚合，得到每个连续分段的起止日期，完全匹配期望输出

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