SQL:如何找到相关项的集群？

Question

我们有以下形式的模式:

CREATE TABLE p (
  id int(11) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO p (id) VALUES (1), (2), (3), (4), (5), (6);

CREATE TABLE IF NOT EXISTS pi (
  product_id int(11) NOT NULL,
  value varchar(10) NOT NULL,
  PRIMARY KEY (product_id, value)
);

INSERT INTO pi (product_id, value) VALUES
(1, 'a'),
(1, 'b'),
(2, 'a'),
(3, 'b'),
(4, 'c'),
(5, 'd'),
(5, 'e'),
(6, 'd');
(6, 'e');

一个产品可以有多个标识符。

我们想要创建共享相同标识符的产品集群。例如:

Product 1
  ID a
  ID b

Product 2
  ID a

Product 3
  ID b

虽然产品 2 和 3 看似无关，但通过检查产品 1 我们发现所有三个产品都属于彼此，因为它们属于同一标识符组。

结果应该是断开连接的产品集群，其中每个产品都属于一个组。对于上面的示例数据，我想获得以下产品集群:

1, 2, 3
4
5, 6

有什么方法可以在纯 SQL 中实现吗？

我们还没有决定引擎，所以任何开源 SQL 数据库方言都是可以接受的。

Answer 1

这主要是为了好玩，并且只聚集了一层。它适用于提供的示例数据，可能不是您的真实数据。性能可能也不好。假设使用 PostgreSQL。

select
array_agg(gp.product_id)
from (
  select
  productat_id,
  array_agg(value)
  from pi
  group by product_id
) as gp
left join (
  select
  product_id,
  array_agg(value)
  from pi
  group by product_id
  having count(*)>1
) gp2
on gp.array_agg && gp2.array_agg 
and gp.product_id <> gp2.product_id
group by coalesce(gp2.array_agg, gp.array_agg);

 array_agg 
-----------
 {1,3,2}
 {5,6}
 {4}
(3 rows)