Snowflake数据库中基于record_id与next_record_id生成分组列的SQL实现

问题描述 #

现有一张包含record_id和next_record_id的表，需要生成包含record_id和record_id_group的结果集：将链式关联的记录归为同一组，分组ID取该链的起始节点record_id，且需包含链中所有节点（包括仅出现在next_record_id中的节点，如示例中的4、7）。

示例输入 #

record_id | next_record_id
----------|---------------
1         | 2
2         | 3
3         | 4
6         | 7

期望输出 #

record_id | record_id_group
----------|----------------
1         | 1
2         | 1
3         | 1
4         | 1
6         | 6
7         | 6

Snowflake 实现方案 #

使用递归CTE遍历链式结构，同时追踪每个节点的根起始节点：

-- 替换为你的实际表名
WITH sample_data AS (
    SELECT 1 AS record_id, 2 AS next_record_id UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 6, 7
),
recursive_chain AS (
    -- 锚点：找出所有链的起始节点（无前置节点的record_id）
    SELECT 
        record_id, 
        next_record_id, 
        record_id AS record_id_group
    FROM sample_data
    WHERE record_id NOT IN (SELECT next_record_id FROM sample_data)
    
    UNION ALL
    
    -- 递归：遍历后续节点，继承根节点作为分组ID
    SELECT 
        s.record_id, 
        s.next_record_id, 
        rc.record_id_group
    FROM sample_data s
    JOIN recursive_chain rc ON s.record_id = rc.next_record_id
    
    UNION ALL
    
    -- 补充链尾节点（仅出现在next_record_id中的节点）
    SELECT 
        rc.next_record_id AS record_id, 
        NULL AS next_record_id, 
        rc.record_id_group
    FROM recursive_chain rc
    WHERE rc.next_record_id NOT IN (SELECT record_id FROM sample_data)
)
-- 去重并输出目标列
SELECT DISTINCT
    record_id,
    record_id_group
FROM recursive_chain
ORDER BY record_id;

逻辑说明 #

1. 锚点成员：筛选出所有没有被其他节点指向的record_id（即链的起始点），将其分组ID设为自身。
2. 递归成员：通过关联原表和递归结果，将后续节点的分组ID继承为链的起始节点ID。
3. 链尾补充：单独提取那些只出现在next_record_id中的节点（链的最后一个节点），赋予对应的分组ID。
4. 最后去重排序，得到符合要求的结果。

通用SQL方案（支持递归CTE的数据库） #

适用于PostgreSQL、MySQL 8.0+等支持WITH RECURSIVE的数据库，逻辑与Snowflake版本一致，仅调整结构使代码更通用：

WITH RECURSIVE sample_data AS (
    SELECT 1 AS record_id, 2 AS next_record_id UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 6, 7
),
recursive_chain AS (
    SELECT 
        record_id, 
        next_record_id, 
        record_id AS record_id_group
    FROM sample_data
    WHERE record_id NOT IN (SELECT next_record_id FROM sample_data)
    
    UNION ALL
    
    SELECT 
        s.record_id, 
        s.next_record_id, 
        rc.record_id_group
    FROM sample_data s
    INNER JOIN recursive_chain rc ON s.record_id = rc.next_record_id
),
-- 单独提取链尾节点
chain_tails AS (
    SELECT 
        next_record_id AS record_id, 
        record_id_group
    FROM recursive_chain
    WHERE next_record_id NOT IN (SELECT record_id FROM sample_data)
)
-- 合并主链与链尾节点
SELECT record_id, record_id_group FROM recursive_chain
UNION
SELECT record_id, record_id_group FROM chain_tails
ORDER BY record_id;

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