如何在BigQuery中实现周期归一化以合并多条记录

要实现将同一产品下连续日期的相同状态记录合并为包含起止日期的单条记录，可通过窗口函数结合分组聚合完成，以下是具体实现方案：

核心思路 #

利用窗口函数标记连续日期的分组：同一产品（pdt）+ 同一状态（status）下，连续的日期会被分配相同的分组键；非连续的日期分组键不同，以此区分不同的周期。之后按分组聚合得到每个周期的起止日期。

实现SQL #

假设你的数据存储在表your_project.your_dataset.your_table中，字段为pdt、status、dt（字符串格式YYYY/MM/DD），执行以下查询：

WITH processed_data AS (
  SELECT
    pdt,
    -- 修正输入中的拼写错误，原表数据正确可移除该替换
    REPLACE(status, 'Inctive', 'Inactive') AS status,
    PARSE_DATE('%Y/%m/%d', dt) AS dt_date,
    -- 生成连续周期的分组键
    DATE_SUB(
      PARSE_DATE('%Y/%m/%d', dt),
      INTERVAL ROW_NUMBER() OVER (PARTITION BY pdt, status ORDER BY PARSE_DATE('%Y/%m/%d', dt)) DAY
    ) AS group_key
  FROM `your_project.your_dataset.your_table`
)
SELECT
  pdt,
  status,
  FORMAT_DATE('%Y/%m/%d', MIN(dt_date)) AS start_dt,
  FORMAT_DATE('%Y/%m/%d', MAX(dt_date)) AS end_dt
FROM processed_data
GROUP BY pdt, status, group_key
ORDER BY pdt, start_dt;

代码说明 #

1. 预处理CTE（processed_data）

   • 将字符串类型的日期dt转换为BigQuery的DATE类型，便于日期运算。
   • 修正输入中status的拼写错误（Inctive→Inactive），若原表数据无误可删除REPLACE函数。
   • 通过DATE_SUB与ROW_NUMBER()的组合生成group_key：连续日期的group_key值相同，非连续日期则不同，以此划分独立周期。

2. 聚合查询

   • 按pdt、status、group_key分组，取每组的最小日期作为周期开始（start_dt），最大日期作为周期结束（end_dt）。
   • 最终结果按产品和开始日期排序，与示例输出一致。

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