如何在日期缺失时持续递增PASTDUE_DAYS数值？

问题背景 #

我有一张贷款数据表和一张账号逾期日期表，需要计算用户从逾期日起的未还款天数。当前查询结果存在LOAN_DATE日期缺失的情况，导致PASTDUE_DAYS无法连续计数，不符合需求。

当前查询结果：

LOAN_DATE   ACC_NO      PASTDUE_DATE    PASTDUE_DAYS
2022-08-11  1079696087  null                 0
2022-08-12  1079696087  null                 0
2022-08-13  1079696087  null                 0
2022-08-14  1079696087  null                 0
2022-08-15  1079696087  null                 0
2022-08-16  1079696087  2022-08-16           1
2022-08-18  1079696087  2022-08-18           2
2022-08-19  1079696087  2022-08-19           3
2022-08-20  1079696087  2022-08-20           4
2022-08-22  1079696087  2022-08-22           5
2022-08-23  1079696087  2022-08-23           6
2022-08-24  1079696087  2022-08-24           7
2022-08-25  1079696087  2022-08-25           8
2022-08-26  1079696087  2022-08-26           9
2022-08-27  1079696087  2022-08-27           10
2022-08-29  1079696087  2022-08-29           11
2022-08-30  1079696087  2022-08-30           12
2022-09-01  1079696087  null                 0
2022-09-02  1079696087  2022-09-02           1

期望结果（日期缺失时PASTDUE_DAYS持续计数）：

LOAN_DATE   ACC_NO      PASTDUE_DATE    PASTDUE_DAYS
2022-08-11  1079696087  null                 0
2022-08-12  1079696087  null                 0
2022-08-13  1079696087  null                 0
2022-08-14  1079696087  null                 0
2022-08-15  1079696087  null                 0
2022-08-16  1079696087  2022-08-16           1
2022-08-18  1079696087  2022-08-18           3
2022-08-19  1079696087  2022-08-19           4
2022-08-20  1079696087  2022-08-20           5
2022-08-22  1079696087  2022-08-22           7
2022-08-23  1079696087  2022-08-23           8
2022-08-24  1079696087  2022-08-24           9
2022-08-25  1079696087  2022-08-25           10
2022-08-26  1079696087  2022-08-26           11
2022-08-27  1079696087  2022-08-27           12
2022-08-29  1079696087  2022-08-29           14
2022-08-30  1079696087  2022-08-30           15
2022-09-01  1079696087  null                 0
2022-09-02  1079696087  2022-09-02           1

解决方法 #

核心思路是基于实际日期差计算逾期天数，而非按记录行号递增。通过窗口函数划分逾期周期，以每个周期的首次逾期日期为起点计算天数：

WITH grouped_data AS (
    SELECT 
        LOAN_DATE,
        ACC_NO,
        PASTDUE_DATE,
        -- 标记连续逾期的周期：当前有逾期且上一行无逾期时，开启新周期
        SUM(CASE WHEN PASTDUE_DATE IS NOT NULL AND LAG(PASTDUE_DATE) OVER (PARTITION BY ACC_NO ORDER BY LOAN_DATE) IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY ACC_NO ORDER BY LOAN_DATE) AS overdue_period
    FROM your_loan_table
),
period_start AS (
    SELECT 
        gd.*,
        -- 获取每个逾期周期的首次逾期日期，作为计数起点
        MIN(PASTDUE_DATE) OVER (PARTITION BY ACC_NO, overdue_period) AS first_pastdue_date
    FROM grouped_data gd
)
SELECT 
    LOAN_DATE,
    ACC_NO,
    PASTDUE_DATE,
    CASE 
        WHEN PASTDUE_DATE IS NULL THEN 0
        -- 计算当前日期与首次逾期日期的天数差，加1得到逾期天数
        ELSE (LOAN_DATE - first_pastdue_date) + 1
    END AS PASTDUE_DAYS
FROM period_start
ORDER BY LOAN_DATE;

代码解释 #

1. grouped_data CTE：使用LAG函数获取上一行的PASTDUE_DATE，通过SUM窗口函数将连续的逾期记录划分为同一个周期。
2. period_start CTE：为每个逾期周期提取最早的逾期日期，作为计数的起始点。
3. 最终查询：无逾期时显示0，有逾期时计算当前LOAN_DATE与周期起始日期的天数差并加1，确保日期缺失时计数仍连续。

内容的提问来源于stack exchange，提问作者H. D. U.