BigQuery中基于24小时滚动窗口统计用户行数量的查询需求

我在BigQuery中有一张包含userid和TIMESTAMP类型字段createddate的表，需编写查询输出userid、window_start_date，以及按这两个字段分组的count(*)统计值。

window_start_date定义：以上一个24小时窗口结束后出现的首条记录作为当前窗口的起始点，每个窗口时长为24小时。原本希望基于用户首条记录的日期划分窗口，但记录可能随时出现且存在间隔。

示例数据 #

原始数据（单用户） #

期望查询输出 #

现有代码缺陷 #

当前使用的查询存在问题：若记录间未形成24小时间隔，窗口会被错误延长。需要修正为：当上一个窗口起始时间后满24小时1秒时，下一条记录即为新窗口的起始点。现有缺陷查询代码如下：

WITH
  original AS (
  SELECT
    userid,
    createddate
  FROM
    original_data 
  ),
 RollingWindows AS (
  SELECT
    userid, createddate,
    LAG(createddate) OVER (partition by userid ORDER BY createddate) AS prev_createddate
  FROM
    original 
),
window_starts AS (
  SELECT
    userid, createddate AS window_start
  FROM
    RollingWindows
  WHERE
    TIMESTAMP_DIFF(createddate, prev_createddate, SECOND) > 24 * 60 * 60
    OR prev_createddate IS NULL
),
joined_window_starts as (
select original .userid, createddate, max(window_start) window_start
from original 
join window_starts w on (
  def.userid = w.userid
)
where window_start <= createddate
group by 1,2
)
select userid, window_start, count(*) cnt
from joined_window_starts
group by 1,2
order by cnt desc

修正后的解决方案 #

要实现正确的窗口划分，我们需要用递归CTE来追踪每个用户的窗口起始和结束时间，确保每个窗口严格从起始点开始计算24小时，之后的第一条记录作为新窗口的起点。以下是修正后的查询：

WITH original AS (
  SELECT 
    userid, 
    createddate,
    -- 给每个用户的记录按时间排序生成行号
    ROW_NUMBER() OVER (PARTITION BY userid ORDER BY createddate) AS rn
  FROM original_data
),
-- 递归CTE，追踪每个用户的窗口起始与结束时间
recursive_windows AS (
  -- 基础情况：取每个用户的第一条记录作为首个窗口起点
  SELECT 
    userid,
    createddate AS window_start,
    TIMESTAMP_ADD(createddate, INTERVAL 24 HOUR) AS window_end,
    rn
  FROM original
  WHERE rn = 1
  
  UNION ALL
  
  -- 递归步骤：找到当前窗口结束后的第一条记录作为新窗口起点
  SELECT
    o.userid,
    o.createddate AS window_start,
    TIMESTAMP_ADD(o.createddate, INTERVAL 24 HOUR) AS window_end,
    o.rn
  FROM original o
  JOIN recursive_windows rw 
    ON o.userid = rw.userid 
    AND o.rn > rw.rn
    AND o.createddate > rw.window_end
  -- 确保仅取窗口结束后的第一条记录
  WHERE NOT EXISTS (
    SELECT 1 
    FROM original o2
    WHERE o2.userid = o.userid 
      AND o2.rn > rw.rn 
      AND o2.rn < o.rn 
      AND o2.createddate > rw.window_end
  )
),
-- 为每条原始记录匹配对应窗口起始点
matched_records AS (
  SELECT
    o.userid,
    o.createddate,
    -- 匹配当前记录所属的窗口起始点
    (SELECT MAX(window_start) 
     FROM recursive_windows rw 
     WHERE rw.userid = o.userid 
       AND rw.window_start <= o.createddate
       AND o.createddate <= rw.window_end) AS window_start_date
  FROM original o
)
-- 最终分组统计结果
SELECT
  userid,
  window_start_date,
  COUNT(*) AS cnt
FROM matched_records
GROUP BY userid, window_start_date
ORDER BY userid, window_start_date;

代码说明 #

1. original CTE：给每个用户的记录按时间排序并生成行号，为后续递归定位记录顺序提供依据。
2. recursive_windows CTE：通过递归逻辑生成每个用户的所有有效窗口：
   • 基础分支取每个用户的第一条记录作为首个窗口起点，窗口结束时间为起点+24小时。
   • 递归分支找到当前窗口结束时间后的第一条记录，作为新窗口的起点并计算其结束时间。
3. matched_records CTE：将每条原始记录匹配到对应的窗口，确保记录落在窗口的时间范围内。
4. 最终统计：按用户和窗口起始点分组，统计每组的记录数量。

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