oracle 比较两个运行总计并在sql中更改其中一个的值

我已经调查了运行总问题/问题在这里问，但找不到类似的。我使用sql开发人员，并有选择语句权限，没有访问游标或循环，或创建函数。
我有一个表，其中有两列需要运行总计：

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
)

我不认为这可以只通过窗口函数来实现。无论如何，我们需要对数据集进行某种迭代，这样我们才能对累积佣金做出正确的决定。
在SQL中，这通常通过递归查询来完成。我们可以遵循以下逻辑：

with  
    dat (customer, report_date, fee, commission, seq, r_fee) as (
        select customer, report_date, fee, commission,
            row_number() over(partition by customer order by report_date),
            sum(fee)     over(partition by customer order by report_date)
        from my_table
    ),
    rec (customer, report_date, fee, commission, seq, r_fee, r_commission) as (
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, commission 
        from dat d 
        where seq = 1
        union all
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, 
            least(r.r_commission + d.commission, d.r_fee) 
        from rec r
        inner join dat d on d.customer = r.customer and d.seq = r.seq + 1 
    )
select * from rec order by customer, report_date

第一个公用表表达式（dat）只枚举每个客户（seq）的行，直接计算运行费用（r_fee）。
第二个CTE rec进行迭代，使用seq从一个步骤跳到下一个步骤;在每一步，根据商业规则更新运行佣金。
下面是一个包含示例数据的demo on DB Fiddle，其结果为：
| 客户|报告日期|费用|佣金|序列号|R_费用|R_佣金|
| - -|- -|- -|- -|- -|- -|- -|
| 六百七十三|二十一年六月三十日|二百一十|二百一十|一个|二百一十|二百一十|
| 六百七十三|2021年7月31日|二百一十|第0页|2个|四百二十人|二百一十|
| 六百七十三|二十一年八月三十一日|二百一十|二百一十|三个|六三○|四百二十人|
| 六百七十三|2021年10月31日|二百一十|310个|四个|八百四十|七三○|
| 六百七十三|二十一年十一月三十日|二百一十|二百一十|五个|小千五|九百四十|
| 六百七十三|二十一年十二月三十一日|二百一十|第0页|六个|小行星1260|九百四十|
| 六百七十三|二十二年一月三十一日|二百一十|九百四十三块零八|七个|小行星1470|小行星1470|
| 六百七十三|2022年2月28日|三百二十人|二百三十六点六|八个|小行星1790|小行星1706.6|
| 六百七十三|二十二年三月三十一日|三百二十人|第0页|九个|小行星2110|小行星1706.6|
| 六百七十三|2022年4月30日|三百二十人|第0页|10个|小行星2430|小行星1706.6|
| 六百七十三|2022年5月31日|三百二十人|第0页|十一|小行星2750|小行星1706.6|
| 六百七十三|2022年6月30日|三百二十人|第0页|十二个|小行星3070|小行星1706.6|
| 六百七十三|2022年7月31日|三百二十人|第0页|十三个|小行星3390|小行星1706.6|
| 六百七十三|2022年8月31日|三百二十人|第0页|十四|小行星3710|小行星1706.6|

oracle 比较两个运行总计并在sql中更改其中一个的值

1条答案

相关问题

热门标签

最新问答