SQL累计计算逾期余额与逾期起始日期问题求助

现有数据表包含USERID、EOMONTH、Pending、Cleared字段，需派生Overdue_Balance和Overdue_since_date字段，需求如下：

• 按EOMONTH累计计算：Overdue_Balance = SUM(Pending) - SUM(Cleared)（按用户分组，按时间顺序累计）
• 当存在逾期余额（Overdue_Balance < 0）时，将首次出现逾期的EOMONTH设为Overdue_since_date，该值保持至余额清零（Overdue_Balance >= 0）

当前尝试的SQL中Overdue_Balance计算正确，但Overdue_since_date不符合要求，寻求正确的SQL解决方案。

问题分析 #

原SQL的核心问题在于错误地用当月Pending-Cleared的正负标记逾期起始，而实际逾期是累计余额的持续负值。我们需要跟踪用户的连续逾期区间：从首次累计余额为负开始，直到累计余额转正，整个区间内的Overdue_since_date固定为区间的第一个EOMONTH；非逾期区间则设为NULL。

修正后的SQL代码 #

-- 创建示例表
CREATE TABLE sample_data (
    UserID integer,
    EOMONTH date,
    Pending integer,
    Cleared integer
);

-- 插入示例数据
INSERT INTO sample_data (UserID, EOMONTH, Pending, Cleared)
VALUES
    (1, '2024-05-31', 50, 110),
    (1, '2024-04-30', 100, 100),
    (1, '2024-03-31', 50, 0),
    (1, '2024-02-29', 50, 40),
    (1, '2024-01-31', 100, 100),
    (1, '2023-12-31', 50, 70),
    (1, '2023-11-30', 90, 80),
    (1, '2023-10-31', 100, 90);

WITH CumulativeBalance AS (
    -- 第一步：计算累计逾期余额，标记当前记录是否逾期
    SELECT
        UserID,
        EOMONTH,
        Pending,
        Cleared,
        SUM(Pending - Cleared) OVER (PARTITION BY UserID ORDER BY EOMONTH) AS Overdue_balance,
        CASE WHEN SUM(Pending - Cleared) OVER (PARTITION BY UserID ORDER BY EOMONTH) < 0 THEN 1 ELSE 0 END AS is_overdue
    FROM sample_data
),
OverdueGroups AS (
    -- 第二步：为每个连续逾期区间分配分组ID
    SELECT
        *,
        SUM(CASE WHEN is_overdue = 1 AND LAG(is_overdue, 1, 0) OVER (PARTITION BY UserID ORDER BY EOMONTH) = 0 THEN 1 ELSE 0 END) 
        OVER (PARTITION BY UserID ORDER BY EOMONTH) AS overdue_group_id
    FROM CumulativeBalance
)
-- 第三步：生成最终的逾期起始日期
SELECT
    UserID,
    EOMONTH,
    Pending,
    Cleared,
    Overdue_balance,
    CASE WHEN is_overdue = 1 THEN MIN(EOMONTH) OVER (PARTITION BY UserID, overdue_group_id) ELSE NULL END AS Overdue_since_date
FROM OverdueGroups
ORDER BY UserID, EOMONTH;

逻辑说明 #

1. CumulativeBalance CTE：计算每个用户按时间顺序的累计逾期余额，同时标记当前记录是否处于逾期状态（is_overdue）。
2. OverdueGroups CTE：通过累加逾期状态的切换次数（从非逾期到逾期），为每个连续逾期区间分配唯一分组ID，确保同一逾期段的记录归为一组。
3. 最终查询：在每个逾期分组内取最早的EOMONTH作为该组所有记录的Overdue_since_date，非逾期记录设为NULL，完全匹配需求。

验证示例 #

若调整2024-05-31的Cleared值为200，此时累计余额变为-140，输出结果中该月及后续保持逾期的月份，Overdue_since_date都会固定为2024-05-31，直到累计余额转正后该字段变为NULL。

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