Oracle SQL中按票证与亲属关系创建自定义家庭分组

原始数据 #

WITH t (ticket_number,source_person,relationship, destination_person) AS (
  VALUES
    (1 ,'Jim'  ,'Dad', 'Stan'),
    (1 ,'Stan','Son', 'Jim'),
    (1 ,'Jim' ,'Mom', 'Sue'),
    (1, 'Sue', 'Son', 'Jim'),
    (2, 'Larry', 'Brother', 'Earl'),
    (2, 'Earl', 'Sister', 'Edna'),
    (2, 'Sam', 'Sister', 'Jane'),
    (2, 'Jane', 'Sister', 'Sam')
)
SELECT * FROM t

需求说明 #

上述数据中每行记录两人的亲属关系，需为每个ticket_number内的关联家庭成员组生成唯一familynumber：同票证内存在多个独立家庭时，不同家庭对应不同ID；同一关联家庭内的所有成员记录共用同一个ID。此前尝试row_number()函数无法解决关联循环导致的ID错误递增问题，期望输出如下：

期望输出 #

WITH desiredoutput (ticket_number,source_person,relationship, destination_person, familynumber) AS (
  VALUES
    (1 ,'Jim'  ,'Dad', 'Stan',1),
    (1 ,'Stan','Son', 'Jim',1),
    (1 ,'Jim' ,'Mom', 'Sue',1),
    (1, 'Sue', 'Son', 'Jim',1),
    (2, 'Larry', 'Brother', 'Earl',1),
    (2, 'Earl', 'Sister', 'Edna',1),
    (2, 'Sam', 'Sister', 'Jane',2),
    (2, 'Jane', 'Sister', 'Sam', 2)
)
SELECT * FROM desiredoutput

解决方案 #

这本质是图的连通分量识别问题：每个人员是图的节点，亲属关系是节点间的边，同一连通子图即为一个家庭。可以用递归CTE实现：

WITH t (ticket_number, source_person, relationship, destination_person) AS (
  VALUES
    (1, 'Jim', 'Dad', 'Stan'),
    (1, 'Stan', 'Son', 'Jim'),
    (1, 'Jim', 'Mom', 'Sue'),
    (1, 'Sue', 'Son', 'Jim'),
    (2, 'Larry', 'Brother', 'Earl'),
    (2, 'Earl', 'Sister', 'Edna'),
    (2, 'Sam', 'Sister', 'Jane'),
    (2, 'Jane', 'Sister', 'Sam')
),
-- 收集每个票证下的所有人员节点
nodes AS (
  SELECT ticket_number, source_person AS person FROM t
  UNION
  SELECT ticket_number, destination_person AS person FROM t
),
-- 递归遍历，将同一连通家庭的所有节点关联到同一个根节点
recursive_cte AS (
  SELECT 
    ticket_number,
    person,
    person AS root_person
  FROM nodes
  UNION ALL
  SELECT 
    rc.ticket_number,
    rc.person,
    n.root_person
  FROM recursive_cte rc
  JOIN t ON rc.ticket_number = t.ticket_number 
    AND rc.root_person = t.source_person
  JOIN recursive_cte n ON n.ticket_number = t.ticket_number 
    AND n.person = t.destination_person
  WHERE rc.root_person != n.root_person
),
-- 为每个票证内的不同根节点分配唯一家庭ID
family_groups AS (
  SELECT 
    ticket_number,
    root_person,
    DENSE_RANK() OVER (PARTITION BY ticket_number ORDER BY root_person) AS familynumber
  FROM (
    SELECT DISTINCT ticket_number, person, root_person
    FROM recursive_cte
  ) AS distinct_roots
)
-- 关联原始表，输出最终结果
SELECT 
  t.ticket_number,
  t.source_person,
  t.relationship,
  t.destination_person,
  fg.familynumber
FROM t
JOIN family_groups fg 
  ON t.ticket_number = fg.ticket_number 
  AND t.source_person = fg.person
ORDER BY t.ticket_number, fg.familynumber, t.source_person;

方案说明 #

1. nodes CTE：收集每个票证下的所有人员，确保不遗漏任何节点；
2. recursive_cte：通过递归遍历亲属关系，将同一连通家庭内的所有节点映射到同一个根节点；
3. family_groups：使用DENSE_RANK()为每个票证下的不同根节点分配唯一的家庭ID；
4. 最后关联原始表，将家庭ID匹配到每条记录上。

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