排查#temp表中InstNo递增但EMIDate未递增的数据并计算日期差

问题背景 #

现有临时表#temp，包含字段AccountID、LoanSeries、InstNo（期数）、EMIDate（还款日），数据规则如下：

• 每个AccountID对应1或2个LoanSeries
• 每个LoanSeries至少有30条InstNo记录
• 正常逻辑：InstNo递增时，EMIDate应同步递增

需求：找出所有InstNo递增但EMIDate持平/递减的异常记录，同时计算当前记录与前序记录的日期差，需精准展示异常的当前记录、前序记录及后续记录，且在1亿+数据量下避免重复记录。

此前尝试CTE结合LAG函数或自连接的方案，小数据样本有效，但大数据量下出现大量重复记录。

解决方案 #

使用窗口函数LAG()和LEAD()实现，避免自连接带来的性能问题和重复记录，SQL语句如下：

WITH EMI_With_Neighbors AS (
    SELECT
        AccountID,
        LoanSeries,
        -- 当前记录信息
        InstNo AS Current_InstNo,
        EMIDate AS Current_EMIDate,
        -- 前序记录信息
        LAG(InstNo) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo) AS Prev_InstNo,
        LAG(EMIDate) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo) AS Prev_EMIDate,
        -- 后续记录信息
        LEAD(InstNo) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo) AS Next_InstNo,
        LEAD(EMIDate) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo) AS Next_EMIDate,
        -- 计算当前与前序的日期差（天数）
        DATEDIFF(day, LAG(EMIDate) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo), EMIDate) AS Date_Diff
    FROM #temp
)
SELECT
    AccountID,
    LoanSeries,
    Prev_InstNo,
    Prev_EMIDate,
    Current_InstNo,
    Current_EMIDate,
    Date_Diff,
    Next_InstNo,
    Next_EMIDate
FROM EMI_With_Neighbors
-- 筛选异常：日期差<=0（持平或递减），排除无前置记录的首行
WHERE Date_Diff <= 0 AND Prev_InstNo IS NOT NULL
ORDER BY AccountID, LoanSeries, Current_InstNo;

关键说明 #

• 分区与排序：通过PARTITION BY AccountID, LoanSeries确保每个账户的不同贷款系列独立计算，ORDER BY InstNo保证按期数顺序获取前后记录，避免跨系列干扰。
• 避免重复：窗口函数基于单表线性遍历，不会像自连接那样产生笛卡尔积，从根源避免重复记录。
• 性能优化：1亿+数据量下，窗口函数执行效率远高于自连接；若#temp上创建(AccountID, LoanSeries, InstNo)复合索引，可进一步提升查询速度。
• 精准筛选：Date_Diff <= 0精准定位EMIDate未递增的异常，Prev_InstNo IS NOT NULL排除每个系列的首条无前置记录的行。

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