Snowflake（ANSI SQL）下30日活跃用户统计的正确SQL实现求助

我来帮你搞定这个需求！你要统计的是每日活跃用户数，其中活跃用户定义为「当日或过去30天内有触发事件的用户」——之前的查询只统计了当天有事件的用户，核心问题是没覆盖到「过去30天窗口内的所有用户」。

核心思路 #

我们需要为每个目标日期，找出所有在该日期往前推29天（含当天，刚好30天窗口）内有过事件记录的唯一用户，然后对这些用户计数。

可行解决方案（Snowflake ANSI SQL） #

这里提供两种直观且高效的实现方式：

方法1：基于日期关联的简洁版

适用于只统计原表中已存在事件的日期（和你的期望输出格式匹配）：

WITH unique_dates AS (
    -- 提取原表中所有有事件记录的日期
    SELECT DISTINCT DATE FROM your_table_name
)
SELECT
    ud.DATE,
    COUNT(DISTINCT t.USERID) AS ACTIVE_USERS
FROM unique_dates ud
LEFT JOIN your_table_name t
    -- 关联条件：用户的事件日期在当前日期的30天窗口内（含当天）
    ON t.DATE BETWEEN DATEADD(day, -29, ud.DATE) AND ud.DATE
GROUP BY ud.DATE
ORDER BY ud.DATE DESC;

方法2：包含连续日期的完整版

如果需要统计所有连续日期（即使某天没有事件发生），可以用GENERATE_SERIES生成完整日期序列：

WITH date_series AS (
    -- 生成从原表最小日期到最大日期的所有连续日期
    SELECT 
        DATEADD(day, seq4(), min_date) AS DATE
    FROM (
        SELECT MIN(DATE) AS min_date, MAX(DATE) AS max_date FROM your_table_name
    ),
    TABLE(GENERATE_SERIES(0, DATEDIFF(day, min_date, max_date)))
)
SELECT
    ds.DATE,
    COUNT(DISTINCT t.USERID) AS ACTIVE_USERS
FROM date_series ds
LEFT JOIN your_table_name t
    ON t.DATE BETWEEN DATEADD(day, -29, ds.DATE) AND ds.DATE
GROUP BY ds.DATE
ORDER BY ds.DATE DESC;

为什么你的之前查询无效？ #

• 第一个查询用CURRENT_DATE() - interval '30 days'只筛选了最近30天的记录，没有针对每个日期单独计算窗口，所以只能得到当前日期往前30天的总活跃用户，不是每日的。
• 第二个窗口函数用了ROWS BETWEEN 30 PRECEDING AND CURRENT ROW，这是按行数取前30行，不是按日期范围取30天的数据——同一个用户可能在30天内有多条记录，或者30天内只有一条但行数不够，所以无法正确覆盖时间窗口。

验证示例数据 #

拿你提供的示例数据测试方法1，会得到和你期望完全一致的结果：

• 2021-08-27：只有用户1在当天有记录，且过去30天内无其他用户，计数1
• 2021-07-25：用户1（当天）、用户2（7-23，30天内）、用户3（7-20，30天内），计数3
• 以此类推，完全匹配你的预期输出。

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