Snowflake中按日期分组的分区多条目累积求和实现

基于你当前的查询已经得到正确的累积求和值，下面提供两种对应你需求的格式实现方案：

这种格式会让同一TXN_DATE下的记录共用一个日期值，其余行日期列留空，适合减少冗余展示：

SELECT
    CASE WHEN rn = 1 THEN TXN_DATE ELSE NULL END AS TXN_DATE,
    PK1,
    PK2,
    QTY,
    SUM_QTY
FROM (
    SELECT
        PK1,
        PK2,
        TXN_DATE,
        QTY,
        SUM(QTY) OVER (PARTITION BY PK1,PK2 ORDER BY TXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_QTY,
        -- 标记每个日期分组内的行号
        ROW_NUMBER() OVER (PARTITION BY TXN_DATE ORDER BY PK1, PK2) AS rn
    FROM MY_TABLE
) t
ORDER BY TXN_DATE, PK1, PK2;

如果需要明确按TXN_DATE分组展示所有记录及对应累积值，甚至添加日期级的汇总信息，可使用以下两种写法：

基础明细版 #

仅按日期分组展示所有PK组合的当日数量及累积值：

SELECT
    TXN_DATE,
    PK1,
    PK2,
    QTY,
    SUM_QTY
FROM (
    SELECT
        PK1,
        PK2,
        TXN_DATE,
        QTY,
        SUM(QTY) OVER (PARTITION BY PK1,PK2 ORDER BY TXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_QTY
    FROM MY_TABLE
) t
ORDER BY TXN_DATE, PK1, PK2;

带汇总版 #

额外添加每日总数量及各PK组合的累积值汇总，方便快速查看整体情况：

SELECT
    COALESCE(CAST(TXN_DATE AS VARCHAR), '总计') AS TXN_DATE,
    COALESCE(PK1, '所有PK1') AS PK1,
    COALESCE(PK2, '所有PK2') AS PK2,
    SUM(QTY) AS DAY_QTY,
    MAX(SUM_QTY) AS CUMULATIVE_SUM
FROM (
    SELECT
        PK1,
        PK2,
        TXN_DATE,
        QTY,
        SUM(QTY) OVER (PARTITION BY PK1,PK2 ORDER BY TXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_QTY
    FROM MY_TABLE
) t
GROUP BY ROLLUP(TXN_DATE, PK1, PK2)
ORDER BY TXN_DATE, PK1, PK2;

说明 #

• 方案1适合生成简洁的明细报表，避免日期列重复显示；
• 方案2的基础版适合需要清晰按日期维度查看每条记录累积情况的场景，带汇总版则更适合需要快速掌握每日及整体数据概况的需求。

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