PostgreSQL按分区逐行扣减计算expected_revised字段的实现咨询

完整SQL代码 #

with test_cte (month, company, probability, expected, actual) as (values
('2024-03','CompA',100,4,13),
('2024-03','CompA',80,7,13),
('2024-03','CompA',50,7,13)
)
select
    month,
    company,
    probability,
    expected,
    actual,
    -- 计算最终的expected_revised
    greatest(
        0,
        expected - greatest(
            0,
            total_actual - running_total_prev
        )
    ) as expected_revised
from (
    select
        *,
        -- 获取分区内的总actual值
        max(actual) over (partition by month, company) as total_actual,
        -- 计算到当前行的累计expected
        sum(expected) over (partition by month, company order by probability desc) as running_total_expected,
        -- 计算到上一行的累计expected，无前置行则为0
        coalesce(
            sum(expected) over (partition by month, company order by probability desc rows between unbounded preceding and 1 preceding),
            0
        ) as running_total_prev
    from test_cte
) t
order by probability desc;

逻辑解释 #

1. 分区与排序：通过partition by month, company将数据按月份和公司分组，order by probability desc确保按概率从高到低处理行。
2. 总actual提取：用max(actual) over (...)获取每个分组的总actual值（同一分组内actual值一致）。
3. 累计expected计算：
   • running_total_expected：从分组第一行到当前行的expected累计和，用于判断当前行是否在actual的覆盖范围内。
   • running_total_prev：从分组第一行到当前行上一行的expected累计和，用于计算当前行需要扣减的actual剩余量。
4. expected_revised计算：
   • 先计算剩余未扣减的actual：total_actual - running_total_prev，如果结果为负说明actual已经耗尽，取0。
   • 用当前行的expected减去剩余未扣减的actual，再用greatest(0, ...)确保结果不小于0，得到最终的expected_revised。

执行结果 #

结果完全符合预期的扣减逻辑：

• 第一行：greatest(0,4-13)=0
• 第二行：greatest(0,7-(13-4))=0
• 第三行：greatest(0,7-(13-4-7))=5

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