如何在BigQuery中按3分钟差值规则将时间戳归类为时间组

需求规则 #

• 首行的time_group与logged_time完全一致
• 后续每行按以下规则分配time_group：
  • 若当前logged_time与前一行的time_group时间差≤3分钟，沿用前一行的time_group
  • 若时间差超过3分钟，将当前logged_time设为新的time_group

预期输出 #

BigQuery实现代码 #

WITH recursive_time_groups AS (
  -- 初始化：取排序后的第一行，设置初始time_group
  SELECT
    logged_time,
    logged_time AS time_group,
    ROW_NUMBER() OVER (ORDER BY logged_time) AS row_num
  FROM
    `your-project.your-dataset.your-table`
  ORDER BY logged_time
  LIMIT 1

  UNION ALL

  -- 递归处理后续每一行
  SELECT
    curr.logged_time,
    CASE
      WHEN TIMESTAMP_DIFF(curr.logged_time, prev.time_group, MINUTE) <= 3 THEN prev.time_group
      ELSE curr.logged_time
    END AS time_group,
    curr.row_num
  FROM (
    SELECT
      logged_time,
      ROW_NUMBER() OVER (ORDER BY logged_time) AS row_num
    FROM
      `your-project.your-dataset.your-table`
  ) curr
  JOIN recursive_time_groups prev
    ON curr.row_num = prev.row_num + 1
)

-- 输出最终结果
SELECT logged_time, time_group
FROM recursive_time_groups
ORDER BY logged_time;

代码说明 #

1. 递归CTE的初始化部分先按logged_time排序，取第一行数据，将time_group设为自身的logged_time；
2. 递归关联部分逐行处理后续数据，通过TIMESTAMP_DIFF计算时间差，根据规则判断是否沿用前一行的分组值；
3. 最后按时间顺序输出结果，即可得到符合要求的time_group。

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