聚合连接的节点/边集 [英] Aggregating connected sets of nodes / edges
问题描述
我有一组具有唯一节点的连接边。它们使用父节点连接。请考虑以下示例代码和说明:
I have a connected set of edges with unique nodes. They are connected using a parent node. Consider the following example code and illustration:
CREATE TABLE network (
node integer PRIMARY KEY,
parent integer REFERENCES network(node),
length numeric NOT NULL
);
CREATE INDEX ON network (parent);
INSERT INTO network (node, parent, length) VALUES
(1, NULL, 1.3),
(2, 1, 1.2),
(3, 2, 0.9),
(4, 3, 1.4),
(5, 4, 1.6),
(6, 2, 1.5),
(7, NULL, 1.0);
在视觉上,可以识别出两组边缘。如何使用PostgreSQL 9.1识别这两个组,并且将 length
相加?显示了预期的结果:
Visually, two groups of edges can be identified. How can the two groups be identified using PostgreSQL 9.1, and length
summed? The expected result is shown:
edges_in_group | total_edges | total_length
----------------+-------------+--------------
{1,2,3,4,5,6} | 6 | 7.9
{7} | 1 | 1.0
(2 rows)
我什至不知道从哪里开始。我是否需要自定义聚合或窗口函数?我可以使用 WITH RECURSIVE
迭代地收集连接的边吗?我的现实情况是一个拥有245,000条边的流网络。我希望 edges_in_group
的最大数量小于200,并且有数百个聚合组(行)。
I don't even know where to begin. Do I need a custom aggregate or window function? Could I use WITH RECURSIVE
to iteratively collect edges that connect? My real world case is a stream network of 245,000 edges. I expect the maximum number of edges_in_group
to be less than 200, and a couple hundred aggregated groups (rows).
推荐答案
递归查询是一种方法:
with recursive tree as (
select node, parent, length, node as root_id
from network
where parent is null
union all
select c.node, c.parent, c.length, p.root_id
from network c
join tree p on p.node = c.parent
)
select root_id, array_agg(node) as edges_in_group, sum(length) as total_length
from tree
group by root_id;
重要的是在每次递归中保留根节点的ID,以便您可以分组最终结果中的那个ID。
The important thing is to keep the id of the root node in each recursion, so that you can group by that id in the final result.
这篇关于聚合连接的节点/边集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!