递归聚合PostgreSQL节点

Question

我的Postgres表如下所示。将数组中的值解释为有向图中相连节点的ID。我想要得到可能路径的列表（使每行的最后一个ID与其他行的第一个ID匹配）。

数据：

  foo
-------
 {1}
 {2,7}
 {3,4}
 {4,6}
 {5}
 {6,8}
 {7}
 {8}

期望结果：

{1}
{2,7}
{3,4,6,8}
{5}

我尝试使用递归查询和窗口函数，但结果并不如我所预期的那样。

- pastDexter

---

所以，只有在后面的行中满足一对中的最后一个整数时，它们才会加入？ - Vao Tsun

是的，确切地说，我们将每行的最后一个整数与其他行的第一个整数进行匹配。 - pastDexter

Answer 1

你是否在寻找类似这样的东西：

WITH RECURSIVE x AS (
        -- choose first level - without more connections 
        SELECT id, id AS full_id, 1 AS level
          FROM foo
         WHERE NOT EXISTS (
               SELECT 1
                 FROM foo AS foo2
                WHERE foo.id != foo2.id
                  AND foo.id[1] = foo2.id[array_length(foo2.id, 1)])
        -- add tail
        UNION ALL
        SELECT x.id, x.full_id || foo.id[2:array_length(foo.id, 1)], level + 1
          FROM x
          JOIN foo ON (
               foo.id != x.id
               AND foo.id[1] = x.full_id[array_length(x.full_id, 1)]
               AND array_length(foo.id, 1) != 1)
), z AS ( 
   -- looks for maximum length
   SELECT max(level) OVER (PARTITION BY id), * FROM x
)
-- choose only with maximum length
SELECT full_id FROM z WHERE max = level    

- Roman Tkachuk