使用SQL查询获取关联订单链并确定baseline订单

需求说明 #

关联Order表与Connection表生成订单关联链，规则为：当一个订单依赖多个订单时，将关联订单中created_date最早的订单设为该关联链的baseline基准订单。

基础表结构 #

Order表

Connection表

预期结果 #

注：73397的baseline为73396，因为73396是其关联订单中created_date最早的订单。

实现SQL #

WITH order_full_connections AS (
    -- 整合正向+反向关联关系
    SELECT 
        c.order_id,
        c.connection_id
    FROM Connection c
    WHERE c.connection_id IS NOT NULL
    UNION ALL
    SELECT 
        c.connection_id AS order_id,
        c.order_id AS connection_id
    FROM Connection c
    WHERE c.connection_id IS NOT NULL
),
order_groups AS (
    -- 递归生成关联链分组，将同一链的订单归为同一组
    WITH RECURSIVE chain_groups AS (
        SELECT 
            ofc.order_id,
            ofc.order_id AS group_root
        FROM order_full_connections ofc
        -- 先取关联链中无上游依赖的订单作为组根
        WHERE NOT EXISTS (
            SELECT 1 FROM order_full_connections oc 
            WHERE oc.connection_id = ofc.order_id
        )
        UNION ALL
        SELECT 
            ofc.order_id,
            cg.group_root
        FROM order_full_connections ofc
        JOIN chain_groups cg ON ofc.connection_id = cg.order_id
        WHERE ofc.order_id != cg.group_root
    )
    SELECT * FROM chain_groups
    -- 补充无关联的独立订单，自身作为组根
    UNION ALL
    SELECT 
        o.order_id,
        o.order_id AS group_root
    FROM Order o
    WHERE NOT EXISTS (
        SELECT 1 FROM order_full_connections ofc WHERE ofc.order_id = o.order_id
    )
),
baseline_mapping AS (
    -- 为每个分组找到最早创建的订单作为baseline
    SELECT 
        og.order_id,
        FIRST_VALUE(o.order_id) OVER (
            PARTITION BY og.group_root 
            ORDER BY STR_TO_DATE(o.created_date, '%m-%d-%Y') ASC
        ) AS baseline
    FROM order_groups og
    JOIN Order o ON og.order_id = o.order_id
)
-- 关联生成最终结果
SELECT 
    ofc.order_id,
    ofc.connection_id,
    bm.baseline
FROM order_full_connections ofc
JOIN baseline_mapping bm ON ofc.order_id = bm.order_id
-- 补充独立订单的结果行
UNION ALL
SELECT 
    o.order_id,
    NULL AS connection_id,
    o.order_id AS baseline
FROM Order o
WHERE NOT EXISTS (
    SELECT 1 FROM order_full_connections ofc WHERE ofc.order_id = o.order_id
)
ORDER BY order_id, connection_id;

逻辑说明 #

1. order_full_connections：整合所有订单的正向、反向关联关系，确保关联链的双向都被覆盖。
2. order_groups：通过递归CTE将同一关联链的订单归为同一分组，以链中无上游依赖的订单（或独立订单自身）作为分组根节点。
3. baseline_mapping：在每个分组内，按created_date排序取最早的订单作为该组所有订单的baseline。
4. 最后关联所有数据并补充独立订单行，输出符合预期的结果集。

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