如何使用teradata sql对无向图的所有节点进行分组/列出 [英] How to group/List all nodes of a undirected graph using teradata sql

问题描述

我有许多差异的数据.表中的一组无向图(如相邻列表关系，一个节点连接所有节点)，我需要将所有单独的无向图分组.

I have data for many diff. set of undirected graphs in a table (like adjacent list relationship, one node is connected which all node) and I need to group all individual undirected graphs.

例如:特定无向图的所有节点都将在一个组中组名将是最小值.节点.

Eg: all nodes of the particular undirected graphs will be in a group & group name will be the min. of the node.

sel d.adj_node, min(d.adj_node) Over (Partition By a.node) as grp
table a 
left join table b
on a.adj_node=b.node
left join table c
on b.adj_node=c.node
​left join table d
​on c.adj_node=d.node​;

现在，我进行了 4,5 次自联接，然后在该查询之上对其进行分区以获得所需的输出.但是进行 4 5 次自连接会产生性能问题.

Now, I am doing a self-join for 4,5 times and then on top that query doing partitioning it to get the desired output. But doing self-join 4 5 times is creating performance issue.

因此，需要一些递归 sql、存储过程或其他一些逻辑来对所有级别执行相同的操作.输入数据所需的输出将是这样的 link 寻找一些建议.

So, need some recursive sql, stored procedure or some other logic to do the same for all levels. Input Data & Required Output will be like this link Looking for some suggestions.

Input Table

node    adj_node
1       2
2       1
2       3
2       5
2       6
2       7
3       2
3       4
4       3
4       5
4       6
4       7
5       2
5       4
6       2
6       4
6       8
7       2
7       4
8       6
1       1
2       2
3       3
4       4
5       5
6       6
7       7
8       8
10      11
11      10
11      13
11      14
12      13
12      14
13      11
13      12
13      14
14      11
14      12
14      13
10      10
11      11
12      12
13      13
14      14


Output
node    grp
1       1
2       1
3       1
4       1
5       1
6       1
7       1
8       1
10      10
11      10
12      10
13      10
14      10

推荐答案

我只记得我在对临时表使用更新之前做过类似的事情.

I just remembered that I did something similar before using updates on a temp table.

实现这一点的最佳方法是一个带有循环的存储过程:

The best way to implement this would be a Stored Procedure with a loop in it:

CREATE VOLATILE TABLE vt_tab AS
 (
   SELECT DISTINCT NODE , adj_node, NODE AS grp
   FROM tab AS t1
   WHERE adj_node <> NODE
 ) WITH DATA
ON COMMIT PRESERVE ROWS
;

-- REPEAT this update UNTIL activity_count = 0
UPDATE vt_tab FROM
 ( 
  SELECT t2.NODE, MIN(t1.grp) AS mingrp
  FROM vt_tab AS t1 JOIN vt_tab AS t2
  ON t1.adj_node = t2.NODE
  AND t1.grp < t2.grp
  GROUP BY t2.NODE
 ) x
SET grp = mingrp
WHERE vt_tab.NODE = x.NODE
;

--get the final result
SEL DISTINCT NODE,grp
FROM vt_tab
ORDER BY 1
;

递归可能是可能的，但很有可能它会破坏您的线轴，因为您需要重复 m:n 连接，并且只有最终的 Select 允许减少结果行.

Recursion might be possible, but there's a high probability that it will blow your spool because you need repeated m:n joins and only the final Select allows to reduce the result rows.

这篇关于如何使用teradata sql对无向图的所有节点进行分组/列出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！