如何用SQL实现基于前置行求和与减法计算FEE列

数据集示例 #

计算规则 #

针对同一CUSTOMER和YEAR，按MONTH顺序，FEE计算公式为：
FEE = REVENUE - 该客户同年所有前置月份的FEE之和
若结果为负则取0（示例中未出现此情况，但公式已兼容）

解决方案：递归CTE #

普通窗口函数无法处理这种依赖已计算出的FEE值的递推逻辑，需用递归CTE实现：

WITH RECURSIVE customer_data AS (
    -- 给每个客户、年份下的月份生成排序行号
    SELECT 
        CUSTOMER,
        YEAR,
        MONTH,
        REVENUE,
        ROW_NUMBER() OVER (PARTITION BY CUSTOMER, YEAR ORDER BY MONTH) AS rn
    FROM your_table_name -- 替换为你的实际表名
),
recursive_fee AS (
    -- 递归起始：处理第一个月份，无前置FEE，直接取REVENUE或0
    SELECT 
        CUSTOMER,
        YEAR,
        MONTH,
        REVENUE,
        CASE WHEN REVENUE >= 0 THEN REVENUE ELSE 0 END AS FEE,
        rn
    FROM customer_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归计算后续月份：用窗口函数累加前置FEE，再计算当前FEE
    SELECT 
        cd.CUSTOMER,
        cd.YEAR,
        cd.MONTH,
        cd.REVENUE,
        CASE 
            WHEN cd.REVENUE - SUM(rf.FEE) OVER (PARTITION BY cd.CUSTOMER, cd.YEAR ORDER BY cd.rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= 0 
            THEN cd.REVENUE - SUM(rf.FEE) OVER (PARTITION BY cd.CUSTOMER, cd.YEAR ORDER BY cd.rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
            ELSE 0 
        END AS FEE,
        cd.rn
    FROM customer_data cd
    JOIN recursive_fee rf 
        ON cd.CUSTOMER = rf.CUSTOMER 
        AND cd.YEAR = rf.YEAR 
        AND cd.rn = rf.rn + 1
)
-- 按顺序输出结果
SELECT 
    CUSTOMER,
    YEAR,
    MONTH,
    REVENUE,
    FEE
FROM recursive_fee
ORDER BY CUSTOMER, YEAR, MONTH;

代码说明 #

1. 基础CTE（customer_data）：生成行号确保月份顺序，为递归遍历做准备。
2. 递归起始点：处理每个客户、年份的首月，此时无前置FEE，直接取REVENUE（或0）。
3. 递归步骤：逐行计算后续月份的FEE，用窗口函数累加之前所有已算出的FEE总和，再用当前REVENUE减去该总和。
4. 结果验证：运行后会完全匹配示例中的FEE值，例如第9月：7000 - 5000 = 2000，第10月：150000 - (5000+2000) = 143000。

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