Snowflake中如何正确计算两类累计金额（含窗口函数过滤）

核心逻辑梳理 #

要解决这个问题，关键是明确两个累计值的定义：

• CUMULATIVE_AMOUNT：截至统计日期d，所有交易发生日期≤d的交易金额累计总和。
• CUMULATIVE_AMOUNT_KNOWN_AS_OF_DATE：截至统计日期d，所有交易发生日期≤d且入账日期≤d的交易金额累计总和（即会计在d当天已知的所有符合条件的交易累计）。

之前的查询错误，是因为没有正确筛选出“入账日期≤当前统计日期”的交易，导致像(2024-02-10, 2024-02-11, 50)这类入账日期等于统计日期的交易未被计入。

方法1：高效聚合+窗口函数（推荐） #

这种方式先生成所有需要统计的日期范围，再关联交易数据计算每日符合条件的金额，性能更优：

WITH date_range AS (
    -- 提取交易表中所有唯一的交易日期作为统计日期，如需连续日期可替换为日期生成逻辑
    SELECT DISTINCT TRANSACTION_DATE AS report_date
    FROM TRANSACTIONS
    ORDER BY report_date
),
daily_calculations AS (
    SELECT
        dr.report_date,
        -- 截至当前统计日期的所有交易总金额（即CUMULATIVE_AMOUNT）
        SUM(t.AMOUNT) AS cumulative_amount,
        -- 截至当前统计日期、已入账的交易总金额（即CUMULATIVE_AMOUNT_KNOWN_AS_OF_DATE）
        SUM(CASE WHEN t.BOOKED_DATE <= dr.report_date THEN t.AMOUNT ELSE 0 END) AS cumulative_amount_known_as_of_date
    FROM date_range dr
    LEFT JOIN TRANSACTIONS t 
        ON t.TRANSACTION_DATE <= dr.report_date
    GROUP BY dr.report_date
)
SELECT
    report_date,
    cumulative_amount,
    cumulative_amount_known_as_of_date
FROM daily_calculations
ORDER BY report_date;

逻辑说明

1. date_range CTE：提取交易表中所有唯一的交易日期作为统计基准，确保每个交易日期都被覆盖。
2. daily_calculations CTE：关联每个统计日期与所有发生日期≤该日期的交易，用CASE筛选出入账日期≤统计日期的交易，分别计算两类累计值。
3. 最终查询直接输出结果——因为SUM(t.AMOUNT)已经是截至当前统计日期的所有交易总和，无需额外累加。

方法2：自连接（直观易理解） #

如果数据量不大，也可以用自连接的方式直接计算每个统计日期的累计值：

WITH date_range AS (
    SELECT DISTINCT TRANSACTION_DATE AS report_date
    FROM TRANSACTIONS
    ORDER BY report_date
)
SELECT
    dr.report_date,
    -- 计算截至当前日期的所有交易累计
    (SELECT SUM(AMOUNT) FROM TRANSACTIONS t WHERE t.TRANSACTION_DATE <= dr.report_date) AS cumulative_amount,
    -- 计算截至当前日期、已入账的交易累计
    (SELECT SUM(AMOUNT) FROM TRANSACTIONS t 
     WHERE t.TRANSACTION_DATE <= dr.report_date 
       AND t.BOOKED_DATE <= dr.report_date) AS cumulative_amount_known_as_of_date
FROM date_range dr
ORDER BY dr.report_date;

逻辑说明

对每个统计日期，通过子查询直接筛选符合条件的交易并求和，逻辑直观，但数据量大时性能不如方法1。

验证你的例子 #

对于交易(2024-02-10, 2024-02-11, 50)：

• 当统计日期为2024-02-10时，其入账日期2024-02-11>10，不会被计入cumulative_amount_known_as_of_date。
• 当统计日期为2024-02-11时，交易发生日期≤11且入账日期≤11，会被正常计入累计值，符合预期。

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