如何在Oracle SQL中使用递归函数处理产品套件转换？

需求说明 #

在Oracle SQL环境中，需根据订单的order_date，通过缓慢变化维度表kit_transition_mapping为每个order_no匹配最终生效的套件，同时关联kit_component表拆解到组件层级，得到包含原始套件、最终套件及组件明细的结果。

核心转换逻辑示例：

• 订单100下单日期为15-Aug-24，ABC套件无转换记录，直接使用原套件ABC
• 订单101下单日期处于ABC转ABD的周期内，更新为ABD
• 订单102下单日期为15-Oct-24，需先将ABC转为ABD，再转为ABE，最终使用ABE

涉及表结构 #

kit_transition_mapping（套件转换表） #

orders（订单表） #

kit_component（套件组件拆解表） #

预期结果 #

实现代码 #

使用递归CTE处理套件的链式转换逻辑，最终关联组件表得到明细结果：

WITH orders (order_no, kit, order_date, qty) AS (
    SELECT 100, 'ABC', DATE '2024-08-15', 1 FROM DUAL UNION ALL
    SELECT 101, 'ABC', DATE '2024-09-15', 1 FROM DUAL UNION ALL
    SELECT 102, 'ABC', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 103, 'ABC', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 104, 'ABD', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 105, 'ABE', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 106, 'ABE', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 107, 'DEX', DATE '2024-12-01', 1 FROM DUAL
),
kit_transition_mapping (old_kit, new_kit, valid_from, valid_to) AS (
    SELECT 'ABC', 'ABD', DATE '2024-09-01', DATE '2024-09-30' FROM DUAL UNION ALL
    SELECT 'ABD', 'ABE', DATE '2024-10-01', DATE '2024-10-31' FROM DUAL UNION ALL
    SELECT 'ABE', 'ABF', DATE '2024-11-01', DATE '2024-11-30' FROM DUAL
),
kit_component (kit, comp, qty) AS (
    SELECT 'ABC', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABC', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABC', 'C', 3 FROM DUAL UNION ALL
    SELECT 'ABD', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABD', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABD', 'D', 4 FROM DUAL UNION ALL
    SELECT 'ABE', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABE', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABE', 'E', 5 FROM DUAL UNION ALL
    SELECT 'ABF', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABF', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABF', 'F', 6 FROM DUAL UNION ALL
    SELECT 'DEX', 'D', 2 FROM DUAL UNION ALL
    SELECT 'DEX', 'E', 3 FROM DUAL UNION ALL
    SELECT 'DEX', 'X', 4 FROM DUAL
),
kit_resolution (order_no, original_kit, kit, order_date, order_qty) AS (
    -- 初始化：取原始订单的套件信息
    SELECT ho.order_no, ho.kit AS original_kit, ho.kit, ho.order_date, ho.qty AS order_qty
    FROM orders ho
    UNION ALL
    -- 递归：匹配当前套件在订单日期下的转换记录，更新为新套件
    SELECT kr.order_no, kr.original_kit, km.new_kit, kr.order_date, kr.order_qty
    FROM kit_resolution kr
    JOIN kit_transition_mapping km ON kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
SELECT 
    kr.order_no, 
    TO_CHAR(kr.order_date, 'DD-Mon-YY') AS order_date,
    kr.original_kit, 
    kr.kit AS new_kit, 
    kr.order_qty, 
    kc.comp, 
    kc.qty * kr.order_qty AS comp_qty
FROM kit_resolution kr
JOIN kit_component kc ON kr.kit = kc.kit
-- 过滤出无后续转换的最终套件
WHERE NOT EXISTS (
    SELECT 1 FROM kit_transition_mapping km
    WHERE kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
ORDER BY kr.order_no, kc.comp;

代码逻辑说明 #

1. 递归CTE kit_resolution：
   • 锚点成员：读取原始订单的套件信息，作为转换的起点
   • 递归成员：不断将当前套件与转换表匹配，若订单日期处于转换周期内，则更新为新套件，直到无可用转换记录
2. 最终查询：
   • 关联组件表，计算组件的总数量（订单数量 × 套件内组件数量）
   • 通过NOT EXISTS过滤出每个订单的最终生效套件，排除中间转换节点

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