如何按日期列排序构建分组，生成主副索赔关联数据

核心思路 #

要解决这个问题，关键是先把所有通过RELATED关联（包括间接关联）的CLAIM_NO归为同一组，再在每组内按规则选出主索赔，最后让主索赔对应组内所有其他索赔作为副索赔。

完整SQL代码 #

WITH claim_groups AS (
    -- 初始化：所有唯一的索赔与关联值
    SELECT 
        CLAIM_NO,
        CLAIM_NO AS root_claim,
        RELATED
    FROM (
        SELECT DISTINCT CLAIM_NO, RELATED
        FROM your_table  -- 替换为你的表名
    ) t
    UNION ALL
    -- 递归关联：找出所有通过RELATED连通的索赔
    SELECT 
        c.CLAIM_NO,
        g.root_claim,
        c.RELATED
    FROM your_table c
    JOIN claim_groups g ON c.RELATED = g.RELATED AND c.CLAIM_NO <> g.CLAIM_NO
),
unique_groups AS (
    -- 去重，确保每个索赔仅归属一个组
    SELECT DISTINCT 
        CLAIM_NO,
        root_claim
    FROM claim_groups
),
claim_ranking AS (
    -- 按规则给组内索赔排名，第1名为该组主索赔
    SELECT 
        t.CLAIM_NO,
        ug.root_claim,
        ROW_NUMBER() OVER (
            PARTITION BY ug.root_claim 
            ORDER BY t.DATE_REPORTED, t.OCCURRED_DATE, t.CLOSURE_DATE, t.CLAIM_NO
        ) AS rn
    FROM your_table t
    JOIN unique_groups ug ON t.CLAIM_NO = ug.CLAIM_NO
),
primary_claims AS (
    -- 提取每组的主索赔
    SELECT 
        root_claim AS group_id,
        CLAIM_NO AS Primary_Claim
    FROM claim_ranking
    WHERE rn = 1
)
-- 生成主副索赔对应关系
SELECT 
    pc.Primary_Claim,
    cr.CLAIM_NO AS "Secondary Claim"
FROM primary_claims pc
JOIN claim_ranking cr ON pc.group_id = cr.root_claim
WHERE cr.CLAIM_NO <> pc.Primary_Claim
ORDER BY pc.Primary_Claim, cr.CLAIM_NO;

代码说明 #

1. claim_groups递归CTE：解决索赔的连通关联问题——把所有共享RELATED、或通过RELATED间接关联的CLAIM_NO归为同一组，用root_claim标识组。
2. unique_groups去重：避免同一索赔被重复分配到多个组。
3. claim_ranking排名：在每个组内按DATE_REPORTED→OCCURRED_DATE→CLOSURE_DATE→CLAIM_NO的顺序排序，排名第1的就是主索赔。
4. 最终关联：将每组主索赔与组内其他索赔配对，排除主索赔自身，得到需求的结果格式。

对原方案问题的说明 #

你之前的row_number()分区错误地用了Claim_no，导致每个索赔单独成组，完全没处理RELATED的关联逻辑；自连接失效是因为没解决跨RELATED的间接关联问题，递归CTE是处理这类连通分组的标准方案，全量数据下只要索引合理就能稳定运行。

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