时间戳

首页 > 解决方案 > 根据关联创建分组

sql - 根据关联创建分组

问题描述

假设我有一张名为“活动”的表格

activity_id
-----------
1
2
3
4
5
6
7

和一个名为“序列”的表

activity_id associated_activity_id
----------- ----------------------
1           2 
1           3
2           4
5           6
6           7

我想写一个选择查询来输出这个：

group_number activity_id
------------ -----------
1            1
1            2
1            3
1            4
2            5
2            6
2            7

活动 1-4 都在组 1 中，因为它们都以某种方式相关（即活动 4 属于组 1，因为它与活动 2 相关，而活动 2 与活动 1 相关）。活动 5 - 7 属于第 2 组，因为它们都是相关的，但它们都与 1 - 4 无关（即它们形成了一个新组）。

标签： sqlpostgresql

解决方案

为此，您可以使用递归 CTE：

with recursive cte as (
      select row_number() over (order by activity_id) as group_id, activity_id
      from activity a
      where not exists (select 1 from sequences s where s.associated_activity_id = a.activity_id)
      union all
      select cte.group_id, s.associated_activity_id
      from cte join
           sequences s
           on s.activity_id = cte.activity_id
     )
select *
from cte
order by group_id, activity_id;

这是一个 db<>fiddle。

推荐阅读