SQL 按百分比递减总和

您正在try 随时间传送状态，因此需要使用UDTF来为您执行传送工作.或者使用recursive CTE

with data(transaction_date, type, value) as (
    select to_date(column1, 'dd.mm.yyyy'), column2, column3
    from values
        ('08.01.2023', 1, 5),
        ('07.01.2023', 0, 20),
        ('06.01.2023', 1, 1),
        ('05.01.2023', 0, 50),
        ('04.01.2023', 0, 50),
        ('03.01.2023', 1, 1),
        ('02.01.2023', 1, 1),
        ('01.01.2023', 1, 1)
), pre_process_data as (
    select *
        ,iff(type = 0, 0, value)::number as add
        ,iff(type = 0, value, 0)::number as per
        ,row_number()over(order by transaction_date asc) as rn
    from data 
), rec_cte_block as (
    with recursive rec_sub_cte as (
        select 
            p.*,
            p.add::number(20,4) as deposited
        from pre_process_data as p
        where p.rn = 1
        
        union all 
        
        select 
            p.*,
            round(div0((r.deposited + p.add)*(100-p.per), 100), 2)  as deposited
        from rec_sub_cte as r
        left join pre_process_data as p
        where p.rn = r.rn+1
    )
    select * 
    from rec_sub_cte
)
select * exclude(add, per, rn)
from rec_cte_block
order by 1;

我以这种方式编写递归CTE，因为如果在CTE中使用IFF或CASE，则当前会发生事件.