使用Databricks SQL生成层级结构并查找首个祖先记录

问题描述 #

需要关联订单表与关联表生成层级结构，为每个订单找到首个祖先（基准订单）；若一个订单依赖多个订单，取created_date最小的记录作为基准订单，最终输出需包含order_id、connection_id、baseline、order_type、prior_date、crnt_dte字段，符合给定规则。

测试数据准备 #

先创建临时视图模拟源数据：

-- 创建订单表
CREATE OR REPLACE TEMP VIEW orders AS
SELECT * FROM VALUES
  (69980, 'table', '11-12-2024'),
  (69981, 'stool', '11-15-2024'),
  (69982, 'phone', '11-23-2024'),
  (73396, 'car', '11-11-2024'),
  (73395, 'bike', '11-10-2024'),
  (73397, 'door', '11-17-2024')
AS orders(order_id, product, created_date);

-- 创建关联表并清理空值
CREATE OR REPLACE TEMP VIEW connections_clean AS
SELECT 
  order_id,
  CASE WHEN connection_id = '' THEN NULL ELSE connection_id END AS connection_id
FROM (
  SELECT * FROM VALUES
    (69980, ''),
    (69981, '69980'),
    (69981, '69982'),
    (69982, '69981'),
    (73395, ''),
    (73396, '73395'),
    (73396, '73397'),
    (73397, '73396')
  AS connections(order_id, connection_id)
);

核心SQL实现 #

WITH recursive_order_hierarchy AS (
  -- 锚点：定义基准订单（无前置依赖的订单）
  SELECT
    o.order_id,
    o.created_date AS crnt_dte,
    o.order_id AS baseline,
    o.created_date AS baseline_date,
    ARRAY(o.order_id) AS visited_orders
  FROM orders o
  LEFT JOIN connections_clean c ON o.order_id = c.order_id
  WHERE c.connection_id IS NULL

  UNION ALL

  -- 递归遍历关联关系，确定每个订单的基准
  SELECT
    c.order_id,
    o.created_date AS crnt_dte,
    -- 选择日期最早的基准订单
    CASE 
      WHEN rh.baseline_date < COALESCE(o_baseline.baseline_date, '9999-12-31') THEN rh.baseline
      ELSE COALESCE(o_baseline.baseline, rh.baseline)
    END AS baseline,
    LEAST(rh.baseline_date, COALESCE(o_baseline.baseline_date, rh.baseline_date)) AS baseline_date,
    ARRAY_APPEND(rh.visited_orders, c.order_id)
  FROM connections_clean c
  JOIN orders o ON c.order_id = o.order_id
  JOIN recursive_order_hierarchy rh ON c.connection_id = rh.order_id
  LEFT JOIN recursive_order_hierarchy o_baseline ON c.order_id = o_baseline.order_id
  -- 避免循环递归
  WHERE NOT array_contains(rh.visited_orders, c.order_id)
),

-- 去重获取每个订单的唯一基准
order_baseline AS (
  SELECT
    order_id,
    crnt_dte,
    MIN(baseline) KEEP (DENSE_RANK FIRST ORDER BY baseline_date) AS baseline
  FROM recursive_order_hierarchy
  GROUP BY order_id, crnt_dte
),

-- 构建最终输出数据集
final_output AS (
  -- 处理子订单记录
  SELECT
    c.order_id,
    c.connection_id,
    ob.baseline,
    'child' AS order_type,
    -- 取关联订单中早于当前订单的日期作为prior_date
    CASE WHEN o_conn.created_date < ob.crnt_dte THEN o_conn.created_date ELSE NULL END AS prior_date,
    ob.crnt_dte
  FROM connections_clean c
  JOIN order_baseline ob ON c.order_id = ob.order_id
  LEFT JOIN orders o_conn ON c.connection_id = o_conn.order_id
  WHERE ob.baseline != c.order_id

  UNION ALL

  -- 处理基准订单记录（关联其所有子订单）
  SELECT
    ob.baseline AS order_id,
    c.order_id AS connection_id,
    ob.baseline,
    'baseline' AS order_type,
    NULL AS prior_date,
    ob.crnt_dte
  FROM order_baseline ob
  JOIN connections_clean c ON ob.baseline = c.connection_id
  WHERE ob.baseline = ob.order_id
)

-- 排序输出匹配预期格式
SELECT * FROM final_output
ORDER BY baseline, order_type DESC, order_id;

逻辑说明 #

1. 递归层级计算：
   • 锚点部分筛选出无前置依赖的基准订单，初始化基准为自身，记录已访问订单防止循环关联导致的无限递归。
   • 递归部分遍历每个关联关系，对比关联订单的基准日期，选择最早的基准作为当前订单的最终基准。
2. 基准去重：通过分组聚合确保每个订单只保留一个基准订单，解决多依赖场景下的基准选择问题。
3. 字段生成：
   • order_type：判断当前订单是否为基准订单，标记为baseline或child。
   • prior_date：仅子订单生效，取关联订单中日期早于当前订单的日期；基准订单设为NULL。
   • 基准订单的connection_id替换为其关联的子订单ID，匹配预期输出格式。
4. 排序：按基准订单、订单类型（基准在前）、订单ID排序，确保输出顺序与预期一致。

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