如何用Snowflake SQL基于每日增减列计算各ID订阅者累计数？

核心逻辑是按ID分组，按日期顺序累计每日的净订阅变化量（新增订阅者 - 流失订阅者），得到当日的累计订阅总数。

假设你的表结构 #

-- 示例表结构（替换为你实际的表名和字段名）
CREATE OR REPLACE TABLE daily_subscriber_metrics (
    id VARCHAR(50),
    report_date DATE,
    subscriber_gained INT,
    subscriber_lost INT
);

正确查询语句 #

SELECT
    id,
    report_date,
    subscriber_gained,
    subscriber_lost,
    -- 计算当日净增减量
    (subscriber_gained - subscriber_lost) AS daily_net,
    -- 按ID分区、日期排序，累计净变化得到当日累计订阅数
    SUM(subscriber_gained - subscriber_lost) OVER (
        PARTITION BY id
        ORDER BY report_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_subscribers
FROM daily_subscriber_metrics
ORDER BY id, report_date;

关键说明 #

• PARTITION BY id：确保仅对同一ID的记录进行独立累计，避免不同ID的数据混淆。
• ORDER BY report_date：保证按时间顺序从早到晚计算累计，结果符合时间逻辑。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确指定累计范围为当前ID的第一条记录到当前日期的记录（Snowflake默认窗口范围即为该值，显式写出可提升可读性）。

带初始订阅数的场景 #

如果你的ID在统计起始日期前已有初始订阅者，可关联初始数据后计算：

-- 假设初始订阅数存储在initial_subscribers表中
SELECT
    m.id,
    m.report_date,
    m.subscriber_gained,
    m.subscriber_lost,
    (m.subscriber_gained - m.subscriber_lost) AS daily_net,
    -- 初始数 + 累计净变化
    i.initial_subscribers + SUM(m.subscriber_gained - m.subscriber_lost) OVER (
        PARTITION BY m.id
        ORDER BY m.report_date
    ) AS cumulative_subscribers
FROM daily_subscriber_metrics m
LEFT JOIN initial_subscribers i ON m.id = i.id
ORDER BY m.id, m.report_date;

常见错误排查 #

• 未添加PARTITION BY id：导致所有ID的累计数据混在一起，结果完全错误。
• 日期排序错误：如果日期字段是字符串类型，需转换为DATE类型后再排序，否则会出现顺序错乱。
• 未计算净变化：直接累计subscriber_gained而忽略subscriber_lost，导致结果偏高。

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