Oracle分析函数实现多顺序限额优惠券抵扣计算求助

问题背景 #

我正尝试用分析函数解决优惠券限额计算问题，但遇到了瓶颈。相关数据及规则如下：

1. 优惠券列表（按字母顺序依次使用） #

2. 每日总限额（Cap） #

3. 优惠券与限额的绑定关系（含应用顺序） #

每张优惠券受1个或2个限额约束，若受2个约束则需按指定顺序应用限额：

4. 预期计算结果 #

需要算出每日限额耗尽前的优惠券使用额（Coupon Usage）及剩余限额（Cap Rem），示例结果如下：

逻辑说明 #

• 行1：优惠券A价值100小于Cap1限额150，全额使用，Cap1剩余50
• 行2：优惠券A已通过Cap1全额使用，无需消耗Cap2
• 行3：优惠券B价值40小于Cap2限额70，全额使用，Cap2剩余30
• 行4：优惠券C先应用剩余30的Cap2，使用30后Cap2耗尽，C剩余90价值
• 行5：优惠券C剩余90应用剩余50的Cap1，使用50后Cap1耗尽
• 行6-8：限额已耗尽，无可用额度

现有代码 #

我已实现单限额场景下的分析函数逻辑，但带不同应用顺序的双限额场景无法解决，现有SQL代码如下：

with coupon_data(coupon, value)as 
(
select 'A',100 from dual union all
select 'B',40 from dual union all
select 'C',120 from dual union all
select 'D',10 from dual union all
select 'E',200 from dual 
)
, cap_data(cap_name, cap_limit) as
(
select 'Cap1', 150 from dual union all
select 'Cap2', 70 from dual 
)
, coupon_cap_mapping(coupon, cap_sequence, cap_name) as
(
select 'A',1,'Cap1' from dual union all
select 'A',2,'Cap2' from dual union all
select 'B',1,'Cap2' from dual union all
select 'C',1,'Cap2' from dual union all
select 'C',2,'Cap1' from dual union all
select 'D',1,'Cap1' from dual union all
select 'E',1,'Cap1' from dual union all
select 'E',2,'Cap2' from dual 
)
SELECT cd.coupon,
       cd.value,
       cap_d.cap_name,
       ccm.cap_sequence,
       cap_d.cap_limit
       --, coupon_usage, cap_remaining 
FROM coupon_data cd
JOIN coupon_cap_mapping ccm ON ( cd.coupon = ccm.coupon )
JOIN cap_data           cap_d ON ( cap_d.cap_name = ccm.cap_name )
ORDER BY cd.coupon,
         ccm.cap_sequence;

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