Snowflake SQL按递减时间区间逐行统计去重用户数实现方法

需求回顾 #

需要按递减规则生成60组去重用户统计结果：从过去60天全周期去重用户数开始，逐次将统计窗口缩短1天，直到统计过去1天的去重用户数，最终输出两列行结构：第一列为窗口天数，第二列为对应用户数，60天窗口为全周期最大值，随窗口缩短用户数单调不增。
原有手写CASE WHEN的写法存在两个问题：

• 结果为横向列式输出，不符合目标行结构要求
• 需要手动重复编写60段近似逻辑，维护成本极高

原有写法示例：

select
        count(distinct (case when datediff(day,DATE,current_date) <= 60 then USER_ID end)) as day_60,
        count(distinct (case when datediff(day,DATE,current_date) <= 59 then USER_ID end)) as day_59,
        count(distinct (case when datediff(day,DATE,current_date) <= 58 then USER_ID end)) as day_58
FROM Table

原有不符合要求的输出：

Day_60  Day_59  Day_58
209     207     207

目标输出格式：

Day Distinct Users
60  200
59  200
58  188
57  185
56  180
[...]   [...]

可直接使用的SQL写法 #

WITH day_series AS (
    -- 自动生成1-60的窗口天数值，不需要手动枚举
    SELECT seq2()+1 AS window_days
    FROM TABLE(GENERATOR(ROWCOUNT => 60))
),
valid_user AS (
    -- 先筛出近60天的去重活跃用户，提前裁剪数据减少计算量
    SELECT DISTINCT
        USER_ID,
        DATEDIFF(day, DATE, CURRENT_DATE) AS days_ago
    FROM your_table
    WHERE DATEDIFF(day, DATE, CURRENT_DATE) BETWEEN 1 AND 60
)
SELECT
    s.window_days AS "Day",
    COUNT(DISTINCT u.USER_ID) AS "Distinct Users"
FROM day_series s
LEFT JOIN valid_user u
ON u.days_ago <= s.window_days
GROUP BY s.window_days
ORDER BY s.window_days DESC;

写法说明 #

• 不需要手动编写重复的CASE WHEN逻辑，后续如果要调整窗口长度，只需要修改GENERATOR函数的ROWCOUNT参数和WHERE条件里的天数范围即可
• 直接输出要求的两列行结构，不需要额外做行列转换
• 提前对近60天数据做去重裁剪，相比全表重复扫描的写法性能高很多

注意：如果DATE字段带时区，建议先统一转换为和CURRENT_DATE一致的时区再做日期差计算，避免统计结果出现偏差。

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