基于可用余额将Release分配至Reverse Release的SQL实现

需求概述 #

需根据RELEASE表的可用额度（当前为20），对Reverse Release表中的记录进行金额分配，超出可用额度的部分仅分配剩余余额。

数据表结构与数据 #

RELEASE表 #

Reverse Release表 #

期望输出 #

已尝试的SQL查询 #

WITH RELEASE AS (
    SELECT 'P1' PID, '2023-05-01' TRANSACTION_DATE, 23 RELEASE 
    FROM DUAL
) ,
REV_RELEASE AS (     
    SELECT 'P1' PID, '2023-05-05' TRANSACTION_DATE, 15 REV_RELEASE
    FROM DUAL
    UNION ALL
    SELECT 'P1' PID, '2023-05-06' TRANSACTION_DATE,  8 REV_RELEASE
    FROM DUAL)
SELECT  *,
        CASE
            WHEN RUNNING_TOTAL >= 0
            THEN
                CASE
                    WHEN LEAD(RUNNING_TOTAL, 1, 0) OVER (PARTITION BY PID ORDER BY TRANSACTION_DATE) >= 0
                    THEN REV_RELEASE
                    ELSE REV_RELEASE + RUNNING_TOTAL
                END
            ELSE 0
        END AS adjusted_reverse_release
FROM (
    SELECT  a.RELEASE,
            b.*, 
            SUM(RELEASE - REV_RELEASE) OVER (PARTITION BY a.PID ORDER BY b.TRANSACTION_DATE) AS RUNNING_TOTAL
    FROM RELEASE a 
    FULL OUTER JOIN REV_RELEASE b ON a.PID=b.PID
)

修正后的SQL解决方案 #

WITH RELEASE AS (
    SELECT 'P1' AS PID, '1-May' AS relDate, 20 AS releaseAmount FROM DUAL
),
REV_RELEASE AS (
    SELECT 'P1' AS PID, '5-May' AS revRelDate, 15 AS reverseRelease FROM DUAL
    UNION ALL
    SELECT 'P1' AS PID, '6-May' AS revRelDate, 8 AS reverseRelease FROM DUAL
),
-- 计算反向释放记录的累计金额
rev_cumulative AS (
    SELECT 
        PID,
        revRelDate,
        reverseRelease,
        SUM(reverseRelease) OVER (PARTITION BY PID ORDER BY revRelDate) AS cumulative_amount
    FROM REV_RELEASE
)
SELECT 
    r.PID,
    r.relDate,
    rc.revRelDate,
    r.releaseAmount,
    -- 计算实际可分配的反向释放金额
    CASE
        -- 累计金额未超过可用额度，取原金额
        WHEN rc.cumulative_amount <= r.releaseAmount THEN rc.reverseRelease
        -- 累计金额超过可用额度，取剩余可用额度
        ELSE r.releaseAmount - (rc.cumulative_amount - rc.reverseRelease)
    END AS reverseRelease
FROM RELEASE r
JOIN rev_cumulative rc ON r.PID = rc.PID
-- 仅保留有可用额度可分配的记录
WHERE (rc.cumulative_amount - rc.reverseRelease) < r.releaseAmount;

逻辑说明 #

1. 累计金额计算：通过窗口函数SUM(reverseRelease) OVER (...)计算每条反向释放记录的累计金额，用于判断是否超出RELEASE的可用额度；
2. 金额分配逻辑：
   • 若当前累计金额未超过RELEASE额度，直接使用原反向释放金额；
   • 若累计金额超过额度，则分配RELEASE的剩余额度（可用额度减去上一条记录的累计金额）；
3. 记录过滤：过滤掉累计金额已完全覆盖RELEASE额度的后续记录，避免出现0金额的无效行。

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