从邻接表将元素的祖先写入Postgres表 [英] Write element's ancestry to Postgres table from adjacency list

问题描述

我想将1到n的层次结构作为邻接表存储到一个列出每个元素祖先的表中。我使用的是Postgres数据库（Postgres 10，但要在其上部署代码的计算机运行Postgres 9.x）。

I want to write a 1 to n-hierarchy that's stored as an adjacency list to a table that lists each of an element's ancestors. I'm using a Postgres database (Postgres 10, but the machine on which the code is to be deployed runs Postgres 9.x).

样本输入表（相邻列表） ）：

Sample input table (adjacency list):

INSERT INTO public.test (id, name, parent_id)
VALUES (1, 't', 1),
   (11, 't1', 1),
   (12, 't2', 1),
   (13, 't3', 1),
   (111, 't11', 11),
   (112, 't12', 11),
   (121, 't21', 12),
   (14, 't4', 1),
   (141, 't41', 14),
   (142, 't42', 14)

因此，我想要一个看起来像这样的表（仅显示了几行；此外，我要解决的现实问题有七个层次级别，而不是两个层次）： / p>

As a result I would like a table that looks like this (just a few rows shown; furthermore, the real-life problem I'm trying to solve has seven hierarchical levels instead of just two):

+-----+-------+--------+--------+
| id  | level | level0 | level1 |
+-----+-------+--------+--------+
|   1 |     0 | NULL   | NULL   |
|  11 |     1 | 1      | NULL   |
|  12 |     1 | 1      | NULL   |
| 111 |     2 | 1      | 11     |
+-----+-------+--------+--------+

id 是元素的ID， level 是该元素位于层次结构中的级别（0为根级别）， level0 / 1 是该元素在相应级别的祖先。

id is the element's id, level is the level at which this element is located within the hierarchy (0 being the root level), level0/1 is the element's ancestor at the respective level.

我是SQL的新手，所以我没有任何可以向您显示的代码。 Googling告诉我，我可能不太愿意使用递归CTE来获得所需的结果并执行自联接，但我一直无法弄清楚该怎么做。谢谢您的帮助。

I'm new to SQL, so I haven't got any code I could show you. Googling has told me that I probably neet to use a recursive CTE to obtain the desired result and perform a self-join, but I haven't been able to figure out how to do it. Thanks for your help.

编辑

这就是我尝试过的远：

WITH RECURSIVE cte AS
(
SELECT m.id AS id,
    0 AS level,
    m.parent_id AS level0,
    m.parent_id AS level1,
    m.parent_id AS parent
    FROM public.test AS m
    WHERE m.parent_id IS NULL

UNION ALL

SELECT 
    m.id,
    cte.level + 1,
    cte.parent AS level0,
    cte.parent AS level1,
    m.parent_id AS parent
    FROM public.test AS m 
        INNER JOIN cte
            ON m.parent_id = cte.id 
)
SELECT *
FROM cte;

当然，设置 level0 和 level1 到元素的父元素不会产生期望的结果，但是我不得不将其设置为某种东西，并且没有比这更进一步的东西了。

Of course, setting level0 and level1 to the element's parent doesn't yield the desired result, but I had to set it to something and haven't got further than this.

推荐答案

SQL是严格类型化的语言，不允许从 SELECT 返回的列数变化根据它所作用的数据。参见例如将逗号分隔的列数据拆分为其他列以进行讨论。

SQL is a strictly typed language that does not allow the number of columns returned from a SELECT to vary depending on the data it is acting upon. See e.g. Split comma separated column data into additional columns for a discussion.

但是，PostgreSQL为您提供了数组类型，可用于将动态大小的值收集到单个列中。以下递归CTE将每一行的所有祖先收集到这样的数组中：

However, PostgreSQL offers you an array type that you can use to collect values of dynamic size into a single column. The following recursive CTE collects all ancestors of every row into such an array:

with recursive rec(id, level, parent_id, ancestors) as (
  select id, 0, parent_id, array[] :: int[]
  from test
  where parent_id = id
  union all
  select t.id, rec.level + 1, t.parent_id, rec.ancestors || array[t.parent_id]
  from test t
  join rec on t.parent_id = rec.id
  where t.parent_id <> t.id
)
select 
  rec.id,
  rec.level,
  rec.ancestors
from rec;

如果级别有已知限制，则可以从数组的每一列中选择元素：

If there's a known limit to the levels, you can select the elements from the array per column:

select
  rec.id,
  rec.level,
  rec.ancestors[1] level1,
  rec.ancestors[2] level2,
  ...

SQL小提琴

这篇关于从邻接表将元素的祖先写入Postgres表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！