标准SQL（BigQuery）如何按指定列条件生成动态变化的Group ID

核心实现逻辑 #

• 按Variable字段分区，保证每个变量的分组编号独立
• 同分区内按Time字段升序排序，计算当前行和前一行的时间差
• 对时间差大于2分钟的行打拆分标记，其余行标记为0
• 同分区内累加拆分标记，累加结果+1即为从1开始计数的GroupID

完整标准SQL示例（原始表仅含Variable、Time字段） #

WITH calc_prev_diff AS (
  SELECT 
    *,
    -- 计算同变量下当前行与上一行的时间差，单位为分钟
    TIMESTAMP_DIFF(Time, LAG(Time) OVER (PARTITION BY Variable ORDER BY Time), MINUTE) AS time_diff_from_prev
  FROM `你的项目名.你的数据集名.你的表名`
),
calc_split_flag AS (
  SELECT
    *,
    -- 时间差超过2分钟则标记为拆分点，首行无前置行标记为0
    IF(time_diff_from_prev > 2, 1, 0) AS split_flag
  FROM calc_prev_diff
)
SELECT
  * EXCEPT(time_diff_from_prev, split_flag),
  -- 累加拆分标记后+1，得到从1开始的分组ID
  SUM(split_flag) OVER (
    PARTITION BY Variable 
    ORDER BY Time 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) + 1 AS GroupID
FROM calc_split_flag
ORDER BY Variable, Time

已提前计算Time_Diff列的简化写法 #

如果你已经提前计算好同变量下当前行与前一行的分钟差，存储为Time_Diff字段，可以直接使用以下语句：

SELECT
  *,
  SUM(IF(Time_Diff > 2, 1, 0)) OVER (
    PARTITION BY Variable 
    ORDER BY Time 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) + 1 AS GroupID
FROM `你的项目名.你的数据集名.你的表名`
ORDER BY Variable, Time

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