在Snowflake中将数值转换为累计值的SQL实现需求

现有如下结构的表（示例数据）：

需要使用Snowflake SQL生成如下结果：

补充规则：

• 2023-12月的座位变动为1，因此该月期初值为0，期末值为1；
• 2024-01月期初值为1（因47个座位的增加发生在1月2日），期末值为1+47+24=72；
• 2024-03至2024-11月无操作，每月期初、期末值均为72；
• 2024-12月期初值为72（12月22日新增72座次日取消），期末值仍为72；
• 2025-01月期初、期末值均为72。

实现思路 #

1. 生成月份序列：构建覆盖目标时间范围的所有月份，避免遗漏；
2. 计算月度变动：将座位变动记录映射到对应发生月份，统计每月总变动量；
3. 计算期初/期末值：通过累计求和得到期末值，期初值取上月期末值，无变动时延续上月数值；
4. 处理边界情况：初始月份期初值设为0，抵消类变动（如新增后取消）不改变期末值。

Snowflake SQL代码 #

WITH date_range AS (
    -- 生成2023-11至2025-01的所有月份
    SELECT
        'ABCDEF1111' AS ref,
        DATE_TRUNC('MONTH', DATEADD('MONTH', seq4(), '2023-11-01')) AS month_start
    FROM TABLE(GENERATOR(ROWCOUNT => 13)) -- 13个月：2023-11到2025-01
),
monthly_changes AS (
    -- 按月份聚合座位变动总和
    SELECT
        ref,
        DATE_TRUNC('MONTH', start_date) AS month_start,
        SUM(seat) AS total_change
    FROM your_table_name -- 替换为实际表名
    GROUP BY ref, DATE_TRUNC('MONTH', start_date)
),
combined_data AS (
    -- 关联月份序列与变动数据，无变动月份填充0
    SELECT
        dr.ref,
        TO_CHAR(dr.month_start, 'YYYY-MM') AS month,
        COALESCE(mc.total_change, 0) AS total_change
    FROM date_range dr
    LEFT JOIN monthly_changes mc
        ON dr.ref = mc.ref AND dr.month_start = mc.month_start
),
running_totals AS (
    -- 计算累计变动得到期末值，期初值取上月期末值
    SELECT
        ref,
        month,
        COALESCE(LAG(running_total) OVER (PARTITION BY ref ORDER BY month), 0) AS begin,
        running_total AS end
    FROM (
        SELECT
            ref,
            month,
            SUM(total_change) OVER (PARTITION BY ref ORDER BY month) AS running_total
        FROM combined_data
    ) t
)
SELECT * FROM running_totals ORDER BY month;

代码说明 #

• date_range：用GENERATOR函数生成指定范围的月份序列，ROWCOUNT根据所需月份数调整；
• monthly_changes：按月份聚合所有座位变动，确保同月变动求和；
• combined_data：将月份序列与变动数据关联，无变动的月份变动量设为0；
• running_totals：通过窗口函数SUM(...) OVER (...)计算累计变动得到期末值，用LAG函数获取上月期末值作为本月期初值；
• 自动处理无变动月份的数值延续，以及变动抵消场景（如2024-12月+72与-72总和为0，期末值保持72）。

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