SQL分组筛选：保留最新状态未恢复的主体首次违约记录

需要从存储主体违约状态的临时表#CreditDefaults中，筛选出两类记录：

• 每个主体**首次出现违约（Credit_Risk_Status_Type = 'Default'）**的记录
• 该主体的最新状态不是Non-Default

原表结构及测试数据 #

-- 创建临时表
CREATE TABLE #CreditDefaults
(
    Party_Id nvarchar(10) null,
    Party_Data_Source_Code nvarchar(3) null,
    Credit_Risk_Status_Type_Group   nvarchar(10) null,
    Credit_Risk_Status_Type  nvarchar(15) null,
    Effective_Timestamp datetime null
)

-- 插入测试数据
INSERT INTO #CreditDefaults 
(Party_Id, Party_Data_Source_Code, Credit_Risk_Status_Type_Group, Credit_Risk_Status_Type, Effective_Timestamp)
VALUES
 ('60005400',   'MDS',  'Default',  'Default',      '2014-12-01 00:10:00.000')
,('60005400',   'MDS',  'Default',  'Default',      '2021-06-04 00:00:01.000')
,('60054040',   'MDS',  'Default',  'Default',      '2014-07-14 00:10:00.000')
,('60054040',   'MDS',  'Default',  'Default',      '2014-07-14 17:36:25.000')
,('60054040',   'MDS',  'Default',  'Default',      '2021-04-15 00:00:01.000')
,('60005495',   'MDS',  'Default',  'Default',      '2019-02-17 00:00:00.000')
,('60005495',   'MDS',  'Default',  'Non-Default',  '2022-03-25 00:00:00.000')
,('60005660',   'MDS',  'Default',  'Default',      '2022-05-24 13:27:47.000')
,('60005672',   'MDS',  'Default',  'Non-Default',  '2020-06-26 00:00:00.000')

SQL实现代码 #

WITH LatestStatus AS (
    -- 获取每个主体的最新状态
    SELECT 
        Party_Id,
        FIRST_VALUE(Credit_Risk_Status_Type) OVER (PARTITION BY Party_Id ORDER BY Effective_Timestamp DESC) AS Latest_Status
    FROM #CreditDefaults
    GROUP BY Party_Id, Effective_Timestamp, Credit_Risk_Status_Type
),
FirstDefault AS (
    -- 标记每个主体的首次违约记录
    SELECT 
        cd.*,
        ROW_NUMBER() OVER (PARTITION BY cd.Party_Id ORDER BY cd.Effective_Timestamp) AS rn
    FROM #CreditDefaults cd
    WHERE cd.Credit_Risk_Status_Type = 'Default'
)
-- 筛选符合条件的记录
SELECT 
    fd.Party_Id,
    fd.Party_Data_Source_Code,
    fd.Credit_Risk_Status_Type_Group,
    fd.Credit_Risk_Status_Type,
    fd.Effective_Timestamp
FROM FirstDefault fd
JOIN LatestStatus ls ON fd.Party_Id = ls.Party_Id
WHERE fd.rn = 1 
  AND ls.Latest_Status <> 'Non-Default'
GROUP BY fd.Party_Id, fd.Party_Data_Source_Code, fd.Credit_Risk_Status_Type_Group, fd.Credit_Risk_Status_Type, fd.Effective_Timestamp
ORDER BY fd.Party_Id;

逻辑说明 #

1. LatestStatus CTE：通过FIRST_VALUE窗口函数按时间倒序取每个主体的最新状态，确保后续只保留未恢复的主体。
2. FirstDefault CTE：对每个主体的违约记录按时间正序排序，用ROW_NUMBER()标记首次违约的记录（rn=1）。
3. 最终查询：关联两个CTE，筛选出首次违约且最新状态未恢复为Non-Default的记录。

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