使用窗口函数SUM OVER计算群组累计营收的SQL问题排查

你的核心问题是窗口函数中嵌套了两层SUM()聚合，以及可能存在的分组逻辑不匹配，导致累计求和失效，最终返回群组总营收而非逐次累计值。

为什么原语句失效？ #

当你写sum(SUM(i.subtotal)) OVER (...)时：

1. 内层SUM(i.subtotal)已经是分组后的单期营收总和（比如按群组+日期）；
2. 外层sum()会将整个群组分区内的所有单期营收再次求和，最终每个行都返回该群组的总营收，而非从第一笔到当前笔的累计值。
3. 如果你的外层查询未将invoice_date纳入GROUP BY，会导致每个群组仅返回一行总营收，窗口函数自然无法生成逐次累计效果。

修正方案 #

方案1：针对已按「群组+日期」分组的查询

直接去掉窗口函数中的外层sum()，或引用分组后的营收列做累计：

SELECT
    cft.cohort_start,
    i.invoice_date,
    SUM(i.subtotal) AS daily_revenue,
    -- 正确写法：对分组后的单期营收做累计求和
    ROUND(SUM(SUM(i.subtotal)) OVER (PARTITION BY cft.cohort_start ORDER BY invoice_date), 2) AS accrual_cum
    -- 等价写法（部分数据库支持引用别名）：
    -- ROUND(SUM(daily_revenue) OVER (PARTITION BY cft.cohort_start ORDER BY invoice_date), 2) AS accrual_cum
FROM 
    -- 替换为你的实际表关联逻辑
    cohort_table cft
    JOIN invoice_table i ON cft.user_id = i.user_id
GROUP BY
    cft.cohort_start,
    i.invoice_date
ORDER BY
    cft.cohort_start,
    i.invoice_date;

方案2：针对未按日期分组的原始查询

先通过CTE做基础的「群组+日期」营收聚合，再在结果上计算累计：

WITH daily_cohort_revenue AS (
    SELECT
        cft.cohort_start,
        i.invoice_date,
        SUM(i.subtotal) AS daily_revenue
    FROM 
        cohort_table cft
        JOIN invoice_table i ON cft.user_id = i.user_id
    GROUP BY
        cft.cohort_start,
        i.invoice_date
)
SELECT
    cohort_start,
    invoice_date,
    daily_revenue,
    ROUND(SUM(daily_revenue) OVER (PARTITION BY cohort_start ORDER BY invoice_date), 2) AS accrual_cum
FROM daily_cohort_revenue
ORDER BY cohort_start, invoice_date;

额外注意事项 #

如果同一日期内有多笔交易，建议在ORDER BY中补充唯一排序键（如invoice_id），避免累计顺序混乱：

ORDER BY invoice_date, invoice_id

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