SQL中对比两个running total并调整其中一个值的实现需求

问题背景 #

已查阅running total相关问题未找到匹配案例，使用SQL Developer，仅拥有SELECT权限，无法使用游标、循环或创建函数。需要对表中两列计算累计求和，其中commission列的累计有特殊约束。

测试数据 #

with my_table as
(
select 673 as customer, to_date('30.06.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.07.2021','dd.mm.yyyy') as report_date,210 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.10.2021','dd.mm.yyyy') as report_date,210 as fee,310 as commission from dual union all
select 673 as customer, to_date('30.11.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.12.2021','dd.mm.yyyy') as report_date,210 as fee,0  as commission from dual union all
select 673 as customer, to_date('31.01.2022','dd.mm.yyyy') as report_date,210 as fee, 943.08 as commission from dual union all
select 673 as customer, to_date('28.02.2022','dd.mm.yyyy') as report_date,320 as fee,236.6 as commission from dual union all
select 673 as customer, to_date('31.03.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.04.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.05.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.06.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.07.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual
)

计算规则 #

• Running_fee：fee列的基础累计求和，使用SUM OVER分区函数即可。
• Running_com：commission列的累计求和需满足约束：每一行的累计值不能超过该行的Running_fee；若累计后超过，则取Running_fee作为该行的Running_com，下一行的累计从该值开始计算。

预期结果 #

尝试过的方法 #

使用LAG函数获取上一行commission值再求和，但无法实现循环累计逻辑，结果仅为简单求和，不符合约束要求。

解决方案 #

利用Oracle的递归CTE实现符合约束的累计求和，仅需SELECT权限：

with my_table as
(
select 673 as customer, to_date('30.06.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.07.2021','dd.mm.yyyy') as report_date,210 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.10.2021','dd.mm.yyyy') as report_date,210 as fee,310 as commission from dual union all
select 673 as customer, to_date('30.11.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.12.2021','dd.mm.yyyy') as report_date,210 as fee,0  as commission from dual union all
select 673 as customer, to_date('31.01.2022','dd.mm.yyyy') as report_date,210 as fee, 943.08 as commission from dual union all
select 673 as customer, to_date('28.02.2022','dd.mm.yyyy') as report_date,320 as fee,236.6 as commission from dual union all
select 673 as customer, to_date('31.03.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.04.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.05.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.06.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.07.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual
),
ranked_data as (
    select 
        customer,
        report_date,
        fee,
        commission,
        sum(fee) over(partition by customer order by report_date) as running_fee,
        row_number() over(partition by customer order by report_date) as rn
    from my_table
),
recursive_calc as (
    select 
        customer,
        report_date,
        fee,
        commission,
        running_fee,
        least(commission, running_fee) as running_com,
        rn
    from ranked_data
    where rn = 1
    union all
    select 
        rd.customer,
        rd.report_date,
        rd.fee,
        rd.commission,
        rd.running_fee,
        least(rc.running_com + rd.commission, rd.running_fee) as running_com,
        rd.rn
    from ranked_data rd
    join recursive_calc rc on rd.customer = rc.customer and rd.rn = rc.rn + 1
)
select 
    customer,
    to_char(report_date, 'dd.mm.yyyy') as report_date,
    fee,
    commission,
    running_fee,
    running_com
from recursive_calc
order by rn;

方案说明 #

1. ranked_data：先计算每行的running_fee，同时按日期给每行分配行号，为递归遍历做准备。
2. recursive_calc：
   • 锚点成员处理第一行，取commission和running_fee的较小值作为初始running_com。
   • 递归成员逐行计算：用上一行的running_com加上当前行的commission，再和当前行的running_fee取较小值，确保累计值不超过约束，同时下一行基于此结果继续累计。
3. 最终查询输出排序后的结果，完全匹配预期。

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