在BigQuery中按指定时间范围与采样间隔统计行数

刚好之前处理过类似的需求，我来给你拆解下怎么实现这个按固定时间间隔统计行数的功能，核心思路是先生成你需要的时间窗口序列，再把原始数据映射到对应的窗口里统计数量。

具体步骤&SQL示例 #

假设你的表结构和示例数据一致，time_column是TIME类型（如果是带日期的TIMESTAMP类型可以跳过时间转换步骤），我们以时间范围[13:00, 15:00]（120分钟，8个15分钟窗口）为例：

-- 第一步：生成15分钟间隔的时间窗口
WITH time_windows AS (
  SELECT
    window_start,
    window_start + INTERVAL 15 MINUTE AS window_end
  FROM
    UNNEST(GENERATE_TIMESTAMP_ARRAY(
      PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-05-20 13:00:00'), -- 你的t1起始时间
      PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-05-20 15:00:00'), -- 你的t2结束时间
      INTERVAL 15 MINUTE -- 采样间隔，可按需调整
    )) AS window_start
),
-- 第二步：把原始的TIME类型转换成带日期的TIMESTAMP（如果已是TIMESTAMP可省略此CTE）
raw_data_with_timestamp AS (
  SELECT
    username,
    TIMESTAMP_ADD(PARSE_TIMESTAMP('%H:%M', time_column), INTERVAL '2024-05-20' DAY) AS event_timestamp
  FROM
    `your_dataset.your_table` -- 替换成你的表名
)
-- 第三步：关联窗口和原始数据，统计每个窗口的行数
SELECT
  FORMAT_TIMESTAMP('%H:%M', tw.window_start) AS window_start_time,
  FORMAT_TIMESTAMP('%H:%M', tw.window_end) AS window_end_time,
  COUNT(rd.event_timestamp) AS row_count
FROM
  time_windows tw
LEFT JOIN
  raw_data_with_timestamp rd
ON
  rd.event_timestamp >= tw.window_start
  AND rd.event_timestamp < tw.window_end
GROUP BY
  tw.window_start, tw.window_end
ORDER BY
  tw.window_start;

代码解释 #

1. 生成时间窗口：用GENERATE_TIMESTAMP_ARRAY生成从t1到t2、步长15分钟的时间数组，再通过UNNEST转换成每行一个窗口起始时间，同时计算出窗口结束时间（起始+15分钟）。
2. 时间格式转换：如果你的time_column只有时分没有日期，需要给它补一个日期转换成TIMESTAMP，这样才能和窗口时间做比较；如果原始字段已经是带日期的TIMESTAMP，直接用原始字段即可。
3. 关联统计：用LEFT JOIN关联窗口和原始数据，判断每条数据的时间是否落在当前窗口内，最后按窗口分组统计行数，LEFT JOIN能保证即使窗口内没有数据也会显示0，不会漏掉窗口。

针对你的示例数据的输出结果 #

按照你给出的4条数据，运行上面的SQL后会得到类似这样的结果：

扩展：按用户分组统计 #

如果你需要按每个用户分别统计每个窗口的行数，只需要在GROUP BY和SELECT里加上username即可：

SELECT
  rd.username,
  FORMAT_TIMESTAMP('%H:%M', tw.window_start) AS window_start_time,
  FORMAT_TIMESTAMP('%H:%M', tw.window_end) AS window_end_time,
  COUNT(rd.event_timestamp) AS row_count
FROM
  time_windows tw
LEFT JOIN
  raw_data_with_timestamp rd
ON
  rd.event_timestamp >= tw.window_start
  AND rd.event_timestamp < tw.window_end
GROUP BY
  rd.username, tw.window_start, tw.window_end
ORDER BY
  rd.username, tw.window_start;

这样就能得到每个用户在每个15分钟窗口内的行数统计啦～

备注：内容来源于stack exchange，提问作者curiousengineer