按交易日期计算交易累计额并判定逾期的SQL实现需求

需求说明 #

需要按交易日期生成累计交易额（running total），并针对每一笔累计额，判定构成该金额的交易是否已逾期，最终生成「current?」字段（形式不限，日期、金额、文本均可）。示例数据如下：

现有问题 #

已通过分析函数实现累计交易额计算，但无法根据累计额回溯排序后的记录，确定该金额对应的最早到期日，进而无法准确判定账户是否逾期。

尝试过的思路与代码 #

此前尝试按到期日（due_dt）分组，若累计额大于该到期日的总欠款则判定逾期，SQL代码如下：

select   
    transacton_date, 
    due_dt,  
    trans_amt, 
    sum(trans_amt) over (order by due_Dt) as running_total, 
    sum(trans_amt) over (order by transacton_date rows unbounded preceding ) as run_tot_by_freeze,
    sum(trans_amt) over (partition by due_dt order by due_Dt) as tot_for_the_due 
from ( 
    select  
        transacton_date,    
        sum(trans_amt) trans_amt, 
        due_dt 
    from ( 
        select                        
            transacton_date,                         
            fin.trans_amt,                      
            case when due_dt is null then transacton_date else due_dt end as due_dt    
        FROM                        
            act act 
            inner join act_type st on st.act_type_cd = act.act_type_cd                        
            inner join fin fin on fin.act_id = act.act_id AND fin.transacton_date IS NOT NULL                        
            left outer join bill bill on bill.bill_id = fin.bill_id                     
        WHERE                        
            acct_id = '12340000'                         
    )                         
    group by                 
        transacton_date,                 
        ft_type_flg,                 
        due_dt                  
    HAVING  SUM(transacton_date) != 0                    
    order by transacton_date          
)          
order by transacton_date

解决方案思路与SQL示例 #

核心思路 #

要判定逾期，关键是跟踪每笔交易后未结清欠款对应的最早到期日：

1. 先整理所有交易并按交易日期排序，关联每笔交易对应的到期日；
2. 用递归CTE逐笔处理交易，按照「先到期先结清」的规则，跟踪剩余欠款的最早到期日；
3. 对比当前交易日期与剩余欠款的最早到期日：若交易日期晚于到期日则判定逾期，否则为正常。

示例SQL（基于递归CTE） #

WITH sorted_trans AS (
    -- 整理并排序交易，生成行号用于递归
    SELECT 
        transacton_date,
        due_dt,
        trans_amt,
        ROW_NUMBER() OVER (ORDER BY transacton_date) AS rn
    FROM (
        SELECT                        
            transacton_date,                         
            fin.trans_amt,                      
            CASE WHEN due_dt IS NULL THEN transacton_date ELSE due_dt END AS due_dt    
        FROM                        
            act act 
            INNER JOIN act_type st ON st.act_type_cd = act.act_type_cd                        
            INNER JOIN fin fin ON fin.act_id = act.act_id AND fin.transacton_date IS NOT NULL                        
            LEFT OUTER JOIN bill bill ON bill.bill_id = fin.bill_id                     
        WHERE                        
            acct_id = '12340000'                         
    ) t
),
running_balance AS (
    -- 初始化递归第一行数据
    SELECT 
        rn,
        transacton_date,
        due_dt,
        trans_amt,
        trans_amt AS running_total,
        CASE WHEN trans_amt > 0 THEN due_dt ELSE NULL END AS earliest_due_date
    FROM sorted_trans
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续每一笔交易
    SELECT 
        st.rn,
        st.transacton_date,
        st.due_dt,
        st.trans_amt,
        rb.running_total + st.trans_amt AS running_total,
        CASE 
            WHEN rb.running_total + st.trans_amt <= 0 THEN NULL  -- 欠款全部结清，无到期日
            WHEN rb.running_total <= 0 THEN  -- 之前无欠款，新增欠款取当前到期日
                CASE WHEN st.trans_amt > 0 THEN st.due_dt ELSE NULL END
            ELSE  -- 之前有欠款，按先到期先抵扣规则更新
                CASE 
                    WHEN st.trans_amt >= 0 THEN LEAST(rb.earliest_due_date, st.due_dt)  -- 新增欠款，取最早到期日
                    ELSE  -- 还款抵扣，若还清则无到期日，否则保留原最早到期日
                        CASE WHEN ABS(st.trans_amt) >= rb.running_total THEN NULL ELSE rb.earliest_due_date END
                END
        END AS earliest_due_date
    FROM running_balance rb
    JOIN sorted_trans st ON rb.rn + 1 = st.rn
)
-- 生成最终结果与current?字段
SELECT 
    transacton_date AS trans_date,
    due_dt AS due_date,
    trans_amt AS transaction_amt,
    running_total,
    CASE 
        WHEN running_total <= 0 THEN '是 - 累计额为0，账户正常'
        WHEN earliest_due_date > transacton_date THEN '是 - 客户欠款' || running_total || '，到期日为' || TO_CHAR(earliest_due_date, 'DD-MON-YY')
        ELSE '否 - 仍欠' || running_total || '，到期日为' || TO_CHAR(earliest_due_date, 'DD-MON-YY') || '，当前为' || TO_CHAR(transacton_date, 'DD-MON-YY')
    END AS "current?"
FROM running_balance
ORDER BY transacton_date;

说明 #

• 递归CTE逐笔跟踪剩余欠款的最早到期日，确保每一步都能准确判断逾期状态；
• TO_CHAR函数可根据数据库类型调整（如MySQL使用DATE_FORMAT）；
• 若数据库支持高级窗口函数（如PostgreSQL的FILTER、Oracle的MODEL子句），可简化递归逻辑。

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