BigQuery中按时间窗口统计唯一ID首次出现次数的最优方案

有一个包含ID和DateTime字段、重复度极高的数据集，需要统计**不同时间窗口（如5分钟、20分钟）**内每个唯一ID的首次出现次数。

示例数据 #

1, 2022-09-26 20:00:00.000000
1, 2022-09-26 20:01:00.000000
1, 2022-09-26 20:02:00.000000
1, 2022-09-26 20:03:00.000000
1, 2022-09-26 20:04:00.000000
1, 2022-09-26 20:05:00.000000
1, 2022-09-26 20:06:00.000000
1, 2022-09-26 20:07:00.000000
1, 2022-09-26 20:08:00.000000
1, 2022-09-26 20:09:00.000000
2, 2022-09-26 20:00:00.000000
2, 2022-09-26 20:01:00.000000
2, 2022-09-26 20:02:00.000000
2, 2022-09-26 20:03:00.000000
2, 2022-09-26 20:04:00.000000
2, 2022-09-26 20:05:00.000000
2, 2022-09-26 20:06:00.000000
2, 2022-09-26 20:07:00.000000
2, 2022-09-26 20:08:00.000000
2, 2022-09-26 20:09:00.000000

预期结果 #

5分钟窗口 #

2022-09-26, 4

20分钟窗口 #

2022-09-26, 2

用户尝试过分区（partitioning）和行号（rownumber）方法但未成功，寻求最优实现方案。

核心思路是通过窗口函数追踪每个ID的时间间隔，标记新窗口的起始，最终统计唯一的窗口首次出现次数，适合大数据量、高重复度的数据集。

SQL实现（以MySQL为例） #

5分钟窗口统计 #

WITH ranked_data AS (
    SELECT 
        id,
        datetime,
        -- 计算当前记录与同ID上一条记录的时间差（分钟）
        TIMESTAMPDIFF(MINUTE, LAG(datetime) OVER (PARTITION BY id ORDER BY datetime), datetime) AS time_diff
    FROM your_table
),
window_markers AS (
    SELECT 
        id,
        datetime,
        -- 第一条记录或与上一条间隔超过5分钟，标记为新窗口
        CASE 
            WHEN time_diff IS NULL OR time_diff > 5 THEN 1 
            ELSE 0 
        END AS is_new_window
    FROM ranked_data
),
window_groups AS (
    SELECT 
        id,
        datetime,
        -- 累加标记，为每个ID生成独立的窗口分组ID
        SUM(is_new_window) OVER (PARTITION BY id ORDER BY datetime) AS window_group
    FROM window_markers
)
-- 按日期聚合，统计唯一的(ID+窗口)组合数
SELECT 
    DATE(datetime) AS date,
    COUNT(DISTINCT CONCAT(id, '-', window_group)) AS first_occurrence_count
FROM window_groups
GROUP BY DATE(datetime);

切换到20分钟窗口 #

只需将TIMESTAMPDIFF(MINUTE, ...)中的5替换为20即可，其余逻辑完全一致。

逻辑说明 #

1. ranked_data：用LAG()窗口函数获取每个ID的上一条记录时间，计算时间差，判断是否进入新窗口。
2. window_markers：标记哪些记录是新窗口的起点（第一条记录或间隔超过窗口阈值）。
3. window_groups：对每个ID的标记进行累加，生成唯一的窗口分组ID，同一个窗口内的记录会共享同一个分组ID。
4. 最终统计：通过CONCAT(id, '-', window_group)生成唯一的窗口标识，统计总数即为每个时间窗口内ID的首次出现次数，按日期聚合得到结果。

方案优势 #

• 高效处理高重复度数据集：仅需三次窗口函数计算，避免了低效的笛卡尔积或全表扫描操作。
• 灵活切换窗口大小：只需修改时间差阈值，即可适配任意时间窗口需求。
• 兼容大多数SQL引擎：LAG()、SUM() OVER()是标准SQL窗口函数，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库。

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