PostgreSQL SQL查询，用于遍历整个无向图并返回找到的所有边 [英] PostgreSQL SQL query for traversing an entire undirected graph and returning all edges found

问题描述

我的PostgreSQL数据库中有一个 edges 表，该表代表有向图的边缘，有两列： node_from 和 node_to （值是节点的ID）。

I have an edges table in my PostgreSQL database that represents the edges of a directed graph, with two columns: node_from and node_to (value is a node's id).

给出一个节点（ initial_node ），我希望能够来遍历整个图，但是以无向的方式。

Given a single node (initial_node) I'd like to be able to traverse the entire graph, but in an undirected way.

我的意思是，例如下面的图：

What I mean is, for instance for the following graph :

(a->b)
(c->b)
(c->d)

如果 initial_node 是 a ，则 b ， c 或 d ，无论如何，我都会得到[ a ， b ， c ， d ]。

If initial_node is a, b, c, or d, in any case, I would get [a, b, c, d].

我使用了以下SQL查询（基于 http://www.postgresql.org/docs/8.4/static/queries-with.html ）：

I used the following SQL query (based on http://www.postgresql.org/docs/8.4/static/queries-with.html ):

WITH RECURSIVE search_graph(uniq, depth, path, cycle) AS (
        SELECT
          CASE WHEN g.node_from = 'initial_node' THEN g.node_to ELSE g.node_from END,
          1,
          CASE WHEN g.node_from = 'initial_node' THEN ARRAY[g.node_from] ELSE ARRAY[g.node_to] END,
          false
        FROM edges g
        WHERE 'initial_node' in (node_from, node_to)
      UNION ALL
        SELECT
          CASE WHEN g.node_from = sg.uniq THEN g.node_to ELSE g.node_from END,
          sg.depth + 1,
          CASE WHEN g.node_from = sg.uniq THEN path || g.node_from ELSE path || g.node_to END,
          g.node_to = ANY(path) OR g.node_from = ANY(path)
        FROM edges g, search_graph sg
        WHERE sg.uniq IN (g.node_from, g.node_to) AND NOT cycle
)
SELECT * FROM search_graph

工作得很好...直到我遇到一个在各个方向上都连接在一起的12个节点的情况（对于每对，我都有（a-> b）和（b-> a）），这使查询无限期地循环。 （将UNION ALL更改为UNION并不能消除循环。）

It worked fine... Until I had a case with 12 nodes that are all connected together, in all directions (for each pair I have both (a->b) and (b->a)), which makes the query loops indefinitely. (Changing UNION ALL to UNION doesn't eliminate the looping.)

有没有人可以解决这个问题？

Has anyone any piece of advice to handle this issue?

干杯，

安东尼。

推荐答案

I做到这一点，就不应该陷入任何类型数据的无限循环：

I got to this, it should not get into infinite loops with any kind of data:

--create temp table edges ("from" text, "to" text);
--insert into edges values ('initial_node', 'a'), ('a', 'b'), ('a', 'c'), ('c', 'd');

with recursive graph(points) as (
  select array(select distinct "to" from edges where "from" = 'initial_node')
  union all
  select g.points || e1.p || e2.p
  from graph g
  left join lateral (
    select array(
      select distinct "to"
      from edges 
      where "from" =any(g.points) and "to" <>all(g.points) and "to" <> 'initial_node') AS p) e1 on (true)
  left join lateral (
    select array(
      select distinct "from"
      from edges 
      where "to" =any(g.points) and "from" <>all(g.points) and "from" <> 'initial_node') AS p) e2 on (true)
  where e1.p <> '{}' OR e2.p <> '{}'
  )
select distinct unnest(points)
from graph
order by 1

递归查询在可以选择的内容方面非常有限，并且由于不允许在子选择中使用递归结果，因此不能使用NOT IN（选择ct *来自递归位置...）。将结果存储在数组中，使用LEFT JOIN LATERAL并使用= ANY（）和<> ALL（）解决了这个难题。

Recursive queries are very limiting in terms of what can be selected, and since they don't allow using the recursive results inside a subselect, one can't use NOT IN (select * from recursive where...). Storing results in an array, using LEFT JOIN LATERAL and using =ANY() and <>ALL() solved this conundrum.

这篇关于PostgreSQL SQL查询，用于遍历整个无向图并返回找到的所有边的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！