SQL中如何按分组实现先负向总和再正向累加的累积求和？

需求分析 #

按group_name分组后，需遵循以下规则计算累积值：

1. 先计算分组内所有负数的总和；
2. 负数行按value升序排列时，每行结果 = 分组总负和 - 此前所有负数的和；
3. 正数行从最后一个负数行的结果开始，依次累加正数。

解决方案 #

通过嵌套窗口函数分步骤计算关键中间值，再通过条件判断分别处理负数行和正数行的结果：

WITH grouped_data AS (
    SELECT 
        group_name,
        value,
        -- 计算分组内所有负数的总和
        SUM(CASE WHEN value < 0 THEN value ELSE 0 END) OVER (PARTITION BY group_name) AS total_neg,
        -- 计算到当前行为止的负数累积和（仅包含负数）
        SUM(CASE WHEN value < 0 THEN value ELSE 0 END) OVER (PARTITION BY group_name ORDER BY value ASC) AS neg_running,
        -- 计算到当前行为止的正数累积和（仅包含正数）
        SUM(CASE WHEN value >= 0 THEN value ELSE 0 END) OVER (PARTITION BY group_name ORDER BY value ASC) AS pos_running,
        -- 获取分组内最后一个负数的value（即最后一个负数行的desired值）
        MAX(CASE WHEN value < 0 THEN value END) OVER (PARTITION BY group_name) AS last_neg_val,
        -- 标记当前行是否为负数
        CASE WHEN value < 0 THEN 1 ELSE 0 END AS is_negative
    FROM dbo.tb
)
SELECT 
    group_name,
    value,
    -- 原查询生成的result列
    SUM(value) OVER (PARTITION BY group_name ORDER BY value ASC) AS result,
    -- 按规则计算的desired列
    CASE
        WHEN is_negative = 1 THEN total_neg - (neg_running - value)
        ELSE COALESCE(last_neg_val, 0) + pos_running
    END AS desired
FROM grouped_data
ORDER BY group_name, value;

结果验证 #

执行上述查询后，将得到与期望一致的desired列结果：

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