如何计算Postgres中图形中所有连接的节点（行）？ [英] How to count all the connected nodes (rows) in a graph on Postgres?

问题描述

我的表格包含 account_id 和 device_id 。一个 account_id 可能有多个 device_id s，反之亦然。我试图计算每个连接的多对多关系的深度。

例如：

  account_id | device_id 
 1 | 10 
 1 | 11 
 1 | 12 
 2 | 10 
 3 | 11 
 3 | 13 
 3 | 14 
 4 | 15 
 5 | 15 
 6 | 16 
  

我如何构建一个知道将帐户1-3一起组合到一起的查询4-5 ，并自己离开6？帐户1-3的所有7个条目应该组合在一起，因为它们在某个时刻都触及相同的account_id或device_id。我试图将它们组合在一起并输出计数。

帐户1在设备10,11,12上使用。这些设备也使用其他帐户，所以我们希望包括他们在小组中。他们使用了额外的帐户2和3.但帐户3还有另外2个设备使用，所以我们也会包含它们。该团体的扩展带来了任何其他账户或设备，这些账户或设备也触动了该组中的账户或设备。

下图显示了一个图表：

解决方案

您可以使用递归cte：

pre code $带递归t（account_id，device_id）as（
select 1，10 union all
select 1，11 union all
select 1，12 union all
select 2，10 union all
select 3，11 union all
select 3，13 union all
select 3，14 union all
select 4,15 union all
select 5,15 union all
select 6，16
），
a as
选择不同的t.account_id作为a，t2.account_id作为a2
从t加入
t t2
t2.device_id = t.device_id和t.account_id> = t2.account_id
），
cte as（
从$ b中选择aa，a.a2作为mina
$ b union all
从cte连接选择aa，cte.a
在a.a2 = cte.a和aa上连接
a
&a> cte.a
）
选择grp，array_agg（a）
from（选择a，min（mina）as grp
from cte
group by a
）由grp组成的
组; b


$ b

2462rel =nofollow noreferrer>这里是一个SQL小提琴。

My table has account_id and device_id. One account_id could have multiple device_ids and vice versa. I am trying to count the depth of each connected many-to-many relationship.

Ex:

account_id | device_id
1 | 10
1 | 11
1 | 12
2 | 10
3 | 11
3 | 13
3 | 14
4 | 15
5 | 15
6 | 16

How do I construct a query that knows to combine accounts 1-3 together, 4-5 together, and leave 6 by itself? All 7 entries of accounts 1-3 should be grouped together because they all touched the same account_id or device_id at some point. I am trying to group them together and output the count.

Account 1 was used on device's 10, 11, 12. Those devices used other accounts too so we want to include them in the group. They used additional accounts 2 and 3. But account 3 was further used by 2 more devices so we will include them as well. The expansion of the group brings in any other account or device that also "touched" an account or device already in the group.

A diagram is shown below:

解决方案

You can use a recursive cte:

with recursive t(account_id, device_id) as (
       select 1, 10 union all
       select 1, 11 union all
       select 1, 12 union all
       select 2, 10 union all
       select 3, 11 union all
       select 3, 13 union all
       select 3, 14 union all
       select 4, 15 union all
       select 5, 15 union all
       select 6, 16
     ),
     a as (
      select distinct t.account_id as a, t2.account_id as a2
      from t join
           t t2
           on t2.device_id = t.device_id and t.account_id >= t2.account_id
     ),
     cte as (
      select a.a, a.a2 as mina
      from a
      union all
      select a.a, cte.a
      from cte join
           a
           on a.a2 = cte.a and a.a > cte.a
     )
select grp, array_agg(a)
from (select a, min(mina) as grp
      from cte
      group by a
     ) a
group by grp;

Here is a SQL Fiddle.

这篇关于如何计算Postgres中图形中所有连接的节点（行）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！