带条件的累计求和实现：SQL处理cumulative_total至160的需求

现有一张数据表，包含唯一字段Item和quantity字段，需要生成新增cumulative_total字段的结果表，规则如下：

• 按quantity降序排序后累加
• 当累计值达到160时，最后一条的cumulative_total固定设为160，对应quantity改为160 - 此前累计值
• 完成后终止，不再处理后续数据

目前卡在条件逻辑处理环节，无法实现上述规则。

可以用CTE结合窗口函数实现这个逻辑，以下是可运行的SQL代码（假设原始表名为inventory）：

WITH running_totals AS (
    -- 先按quantity降序，计算初始累计值
    SELECT
        Item,
        quantity,
        SUM(quantity) OVER (ORDER BY quantity DESC) AS cum_total
    FROM inventory
),
adjusted_rows AS (
    SELECT
        Item,
        -- 调整quantity：如果累计超160，取差值；否则保留原数量
        CASE
            WHEN cum_total > 160 THEN 160 - LAG(cum_total) OVER (ORDER BY quantity DESC)
            ELSE quantity
        END AS quantity,
        -- 调整累计值：超160则设为160，否则保留原累计
        CASE
            WHEN cum_total > 160 THEN 160
            ELSE cum_total
        END AS cumulative_total,
        -- 标记是否触发终止条件
        CASE WHEN cum_total >= 160 THEN 1 ELSE 0 END AS stop_trigger
    FROM running_totals
)
-- 取到触发终止的行为止，后续行不处理
SELECT Item, quantity, cumulative_total
FROM adjusted_rows
WHERE (SELECT MIN(CASE WHEN stop_trigger = 1 THEN cum_total END) FROM running_totals) IS NULL 
   OR cum_total <= (SELECT MIN(CASE WHEN stop_trigger = 1 THEN cum_total END) FROM running_totals)
ORDER BY cumulative_total;

关键逻辑说明 #

1. running_totals：用窗口函数SUM() OVER()计算按quantity降序后的累计值，得到初始的累计结果。
2. adjusted_rows：通过CASE语句调整超阈值行的quantity和cumulative_total，同时标记触发终止的行。
3. 最后筛选时，只保留到第一次触发终止的行（包括该行），自动截断后续数据。

如果你的SQL方言支持QUALIFY（比如BigQuery、Snowflake），可以简化最后一步的筛选：

WITH running_totals AS (
    SELECT
        Item,
        quantity,
        SUM(quantity) OVER (ORDER BY quantity DESC) AS cum_total
    FROM inventory
),
adjusted_rows AS (
    SELECT
        Item,
        CASE
            WHEN cum_total > 160 THEN 160 - LAG(cum_total) OVER (ORDER BY quantity DESC)
            ELSE quantity
        END AS quantity,
        CASE
            WHEN cum_total > 160 THEN 160
            ELSE cum_total
        END AS cumulative_total,
        cum_total AS original_cum
    FROM running_totals
)
SELECT Item, quantity, cumulative_total
FROM adjusted_rows
QUALIFY original_cum <= (SELECT MIN(cum_total) FROM running_totals WHERE cum_total >= 160)
   OR (SELECT MIN(cum_total) FROM running_totals WHERE cum_total >= 160) IS NULL
ORDER BY cumulative_total;

这样处理后，就能精准实现你要的累计终止和字段调整逻辑。

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