从邻接列表将元素的祖先写入 Postgres 表

Question

我想将1至n层结构写为邻接列表，该列表列出了列出每个元素祖先的表。我正在使用Postgres数据库(Postgres 10，但是要部署代码的机器运行Postgres 9.x(。

样本输入表(邻接列表(：

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)

因此，我想要一个看起来像这样的表(仅显示几行；此外，我要解决的现实生活中的问题具有七个层次结构级别，而不仅仅是两个(：

+-----+-------+--------+--------+
| 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是相应级别的元素的祖先。

我是SQL的新手，所以我没有任何可以向您展示的代码。Google告诉我，我可能会使用递归的CTE获得所需的结果并进行自我加入，但是我无法弄清楚如何做。感谢您的帮助。

编辑

这是我到目前为止尝试的：

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设置为元素的父母不会产生所需的结果，但是我必须将其设置为某物，并且不超过此。

Answer 1

sql是一种严格键入的语言，不允许从 SELECT返回的列数量根据其作用的数据而变化。参见例如分开逗号将列数据分为其他列进行讨论。

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

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;

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

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

SQL小提琴

Answer 2

如果对象的位置没有及时更改(即，如果从级别开始将其放置在第6级，它将永远保持在该级别上(，您可以使用7引入一些理智的ID数字显示了7个级别，通过说semicolon(：(：

分开

'1：1：1：1：1：1：1：1'

然后引入一些功能索引，例如：

CREATE INDEX level1_idx ON main_table USING (regexp_split_to_array(id, '\:')[1])
CREATE INDEX level2_idx ON main_table USING (regexp_split_to_array(id, '\:')[2])
CREATE INDEX level3_idx ON main_table USING (regexp_split_to_array(id, '\:')[3])

然后您可以进行有效的查询：

SELECT id, regexp_split_to_array(id, '\:')[1] as level1, regexp_split_to_array(id, '\:')[2] as level2, ...
ORDER BY level1, level2, level3 ...