如何用Oracle SELECT语句实现总Burns累计扣除的Net_PTS计算？

可以通过窗口函数结合条件计算实现纯SELECT语句的解法，以下是具体SQL：

WITH city_totals AS (
    SELECT 
        CITY_ID,
        MNTH,
        EARNS,
        SUM(BURNS) OVER (PARTITION BY CITY_ID) AS total_burns,
        SUM(EARNS) OVER (PARTITION BY CITY_ID ORDER BY MNTH ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_cum_earns
    FROM T_PTS_TEST
)
SELECT 
    CITY_ID,
    MNTH,
    EARNS,
    EARNS + LEAST(NVL(prev_cum_earns + total_burns, total_burns), 0) AS NET_PTS
FROM city_totals
ORDER BY MNTH;

逻辑说明 #

1. CTE city_totals 计算基础值：
   • total_burns：通过窗口函数SUM(BURNS) OVER (PARTITION BY CITY_ID)得到每个城市的总扣除额（所有月份BURNS的总和）。
   • prev_cum_earns：通过窗口函数计算当前月份之前所有月份的EARNS累计和，第一个月无前置月份，结果为NULL。
2. 主查询计算NET_PTS：
   • NVL(prev_cum_earns + total_burns, total_burns)：处理第一个月的特殊情况，直接使用总扣除额；后续月份则计算截至上月累计EARNS与总扣除额的和，即剩余待扣除额度。
   • LEAST(..., 0)：如果剩余待扣除额度为正数，说明已完成全部扣除，取0不再继续扣减；如果为负数，则保留该负数继续扣减。
   • 最终用当月EARNS加上上述计算的扣减额度，得到NET_PTS。

执行该SQL后，输出结果与期望完全一致：

内容的提问来源于stack exchange，提问作者satya prakash Panigrahi