Snowflake中累计求和与滚动窗口SQL编写求助

假设前提 #

假设我们有一张用户注册明细表 user_registrations，结构如下：

• user_id: 用户唯一标识（对应需求中的Column1，用于计算唯一值）
• registration_date: 用户注册日期（格式：YYYY-MM-DD）

同时定义 Month1 的起始日期为你业务中的具体日期，比如 '2023-01-01'，可自行替换。

完整SQL代码 #

-- 定义Month1的起始日期，根据实际业务调整
SET month1_start = '2023-01-01';

WITH
-- 1. 计算Month1之前的总注册唯一用户数
pre_month1_total AS (
    SELECT COUNT(DISTINCT user_id) AS pre_total_users
    FROM user_registrations
    WHERE registration_date < $month1_start
),
-- 生成M1到M13的日期序列，每个日期为当月起始日
month_sequence AS (
    SELECT
        DATEADD(month, seq4(), $month1_start) AS month_period
    FROM TABLE(GENERATOR(ROWCOUNT => 13))
),
-- 计算每个月的新增唯一用户数（从Month1开始）
monthly_new_users AS (
    SELECT
        DATE_TRUNC('month', ur.registration_date) AS month_period,
        COUNT(DISTINCT ur.user_id) AS new_unique_users
    FROM user_registrations ur
    WHERE ur.registration_date >= $month1_start
    GROUP BY DATE_TRUNC('month', ur.registration_date)
),
-- 关联日期序列与新增用户数据，补全无新增的月份
monthly_data AS (
    SELECT
        ms.month_period,
        COALESCE(mnu.new_unique_users, 0) AS new_unique_users
    FROM month_sequence ms
    LEFT JOIN monthly_new_users mnu
        ON ms.month_period = mnu.month_period
),
-- 计算13个月滚动累计唯一用户数，以Month1前的总数为起始
rolling_cumulative AS (
    SELECT
        md.month_period,
        -- 滚动窗口覆盖当前月及往前12个月（共13个月），叠加初始总数
        pm1.pre_total_users + SUM(md.new_unique_users) OVER (
            ORDER BY md.month_period
            ROWS BETWEEN 12 PRECEDING AND CURRENT ROW
        ) AS rolling_13month_total
    FROM monthly_data md
    CROSS JOIN pre_month1_total pm1
)
SELECT * FROM rolling_cumulative;

代码说明 #

1. pre_month1_total: 筛选Month1之前的所有注册用户，计算唯一用户总数，作为滚动累计的起始基准值。
2. month_sequence: 利用Snowflake内置的GENERATOR函数生成连续13个月的日期序列，对应需求中的[M1,M2,…,M13]。
3. monthly_new_users: 按月份聚合，统计每个月的新增唯一用户数。
4. monthly_data: 关联日期序列与月度新增数据，确保每个月份都有记录（无新增用户的月份补0）。
5. rolling_cumulative: 通过窗口函数SUM() OVER()实现13个月的滚动求和，将初始总数与滚动窗口内的新增用户数相加，得到每个周期的累计唯一用户数。

灵活调整点 #

• 如果需求中的Column1是月度注册人数（而非用户ID），只需将COUNT(DISTINCT user_id)替换为SUM(column1)即可。
• 可根据业务习惯调整日期序列的格式（比如改为当月最后一天），修改DATEADD和DATE_TRUNC的参数即可。

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