BigQuery中实现用户近N天滚动出现次数统计的问题

我有一个BigQuery表，需要统计用户在过去n天内的出现次数。原查询语句如下：

SELECT
    t.*,
    COUNT(*) OVER(PARTITION BY user_id ORDER BY UNIX_DATE(date) RANGE BETWEEN 2 PRECEDING
      AND CURRENT ROW) AS cnt 
  FROM
    `mytable` t

但存在问题：若用户连续两天出现，第三天未出现，该查询不会返回该用户第三天的记录，但实际应返回计数2。

数据集示例 #

date       user_ID
---------- -------     
2023-02-15 user1
2023-02-15 user2
2023-02-16 user1
2023-02-16 user2
2023-02-16 user3
2023-02-17 user1
2023-02-17 user3
2023-02-18 user3
2023-02-19 user1
2023-02-19 user2
2023-02-19 user3

期望输出（从2023-02-17开始） #

date       user_ID cnt
---------- ------- ---
2023-02-17 user1   3
2023-02-17 user2   2
2023-02-17 user3   2
2023-02-18 user1   2
2023-02-18 user2   1
2023-02-18 user3   3
2023-02-19 user1   2
2023-02-19 user2   1
2023-02-19 user3   3

核心是先补全所有用户的所有日期记录，再统计过去N天的活跃次数，具体实现如下：

1. 生成所有日期与所有用户的笛卡尔积，确保每个用户在每一个日期都有对应的行
2. 关联原表标记用户当天是否活跃
3. 用窗口函数计算每个用户过去N天的活跃次数总和

完整查询代码：

WITH all_dates_users AS (
    -- 生成所有不重复日期和用户的组合
    SELECT
        d.date,
        u.user_id
    FROM
        (SELECT DISTINCT date FROM `mytable`) d
    CROSS JOIN
        (SELECT DISTINCT user_id FROM `mytable`) u
),
user_activity_flags AS (
    -- 标记用户当天是否有记录（1为活跃，0为不活跃）
    SELECT
        adu.date,
        adu.user_id,
        IF(t.user_id IS NOT NULL, 1, 0) AS is_active
    FROM
        all_dates_users adu
    LEFT JOIN
        `mytable` t ON adu.date = t.date AND adu.user_id = t.user_id
)
SELECT
    date,
    user_id,
    -- 统计过去2天（含当前日期）的活跃次数
    SUM(is_active) OVER(
        PARTITION BY user_id
        ORDER BY UNIX_DATE(date)
        RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS cnt
FROM
    user_activity_flags
-- 根据需求过滤日期范围
WHERE
    date >= '2023-02-17'
ORDER BY
    date, user_id;

关键说明 #

• all_dates_users 解决了原查询中无活跃日期不返回记录的问题，补全了所有用户的日期行
• 用SUM(is_active)替代原查询的COUNT(*)，因为现在每个日期都有记录，即使当天不活跃也能统计过去N天的总次数
• 可以通过调整RANGE BETWEEN n PRECEDING AND CURRENT ROW中的n来改变统计的天数范围

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