Postgresql ltree查询以找到最多子级的父级；不包括根 [英] Postgresql ltree query to find parent with most children; excluding root

问题描述

我正在使用PostgreSQL，并且有一个表，该表的路径列的类型为 ltree 。

I am using PostgreSQL and have a table with a path column that is of type ltree.

我要解决的问题是：给定整个树结构，
父级中除根以外的子级最多。

The problem I am trying to solve is: given the whole tree structure, what parent has the most children excluding the root.

示例数据如下：

path column = ; has a depth of 0 and has 11 children its id is 1824 # dont want this one because its the root
path column = ; has a depth of 0 and has 1 children its id is 1823
path column = 1823; has a depth of 1 and has 1 children its id is 1825
path column = 1823.1825; has a depth of 2 and has 1 children its id is 1826
path column = 1823.1825.1826; has a depth of 3 and has 1 children its id is 1827
path column = 1823.1825.1826.1827; has a depth of 4 and has 1 children its id is 1828
path column = 1824.1925.1955.1959.1972.1991; has a depth of 6 and has 5 children its id is 2001
path column = 1824.1925.1955.1959.1972.1991.2001; has a depth of 7 and has 1 children its id is 2141
path column = 1824.1925.1955.1959.1972.1991.2001; has a depth of 7 and has 0 children its id is 2040
path column = 1824.1925.1955.1959.1972.1991.2001; has a depth of 7 and has 1 children its id is 2054
path column = 1824.1925.1955.1959.1972.1991.2001; has a depth of 7 and has 0 children its id is 2253
path column = 1824.1925.1955.1959.1972.1991.2001; has a depth of 7 and has 1 children its id is 2166
path column = 1824.1925.1955.1959.1972.1991.2001.2054; has a depth of 8 and has 0 children its id is 2205
path column = 1824.1925.1955.1959.1972.1991.2001.2141; has a depth of 8 and has 0 children its id is 2161
path column = 1824.1925.1955.1959.1972.1991.2001.2166; has a depth of 8 and has 1 children its id is 2389
path column = 1824.1925.1955.1959.1972.1991.2001.2166.2389; has a depth of 9 and has 0 children its id is 2402
path column = 1824.1925.1983; has a depth of 3 and has 1 children its id is 2135
path column = 1824.1925.1983.2135; has a depth of 4 and has 0 children its id is 2239
path column = 1824.1926; has a depth of 2 and has 5 children its id is 1942
path column = 1824.1926; has a depth of 2 and has 11 children its id is 1928 # this is the row I am after
path column = 1824.1926; has a depth of 2 and has 2 children its id is 1933
path column = 1824.1926; has a depth of 2 and has 2 children its id is 1989
path column = 1824.1926.1928; has a depth of 3 and has 3 children its id is 2051
path column = 1824.1926.1928; has a depth of 3 and has 0 children its id is 2024
path column = 1824.1926.1928; has a depth of 3 and has 2 children its id is 1988

因此，在此示例中，该行ID为 1824 （根）的孩子有11个，ID为 1928 的行有11个孩子的深度为2；这是我要去的行。

So, in this example, the row with id 1824 (the root) has 11 children and the row with id 1928 has 11 children with a depth of 2; this is the row I am after.

对于这个问题，我是ltree和sql的新手。

I am new to ltree and sql for that matter.

（这是经过修订的问题，其中在 Ltree找到拥有最多子项的父级后，添加了示例数据已关闭）。

(This is a revised question with added sample data after Ltree find parent with most children postgresql was closed).

推荐答案

解决方案

查找子节点最多的节点：

Solution

To find the node with the most children:

SELECT subpath(path, -1, 1), count(*) AS children
FROM   tbl
WHERE  path <> ''
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  1;

...并排除根节点：

... and exclude root nodes:

SELECT *
FROM  (
   SELECT ltree2text(subpath(path, -1, 1))::int AS tbl_id, count(*) AS children
   FROM   tbl
   WHERE  path <> ''
   GROUP  BY 1
   ) ct
LEFT   JOIN (
   SELECT tbl_id
   FROM   tbl
   WHERE  path = ''
   ) x USING  (tbl_id)
WHERE  x.tbl_id IS NULL
ORDER  BY children DESC
LIMIT  1

假设根节点具有空的 ltree （'' ）作为路径。可能是 NULL 。然后使用 path IS NULL ...

Assuming that root nodes have an empty ltree ('') as path. Might be NULL. Then use path IS NULL ...

示例中的赢家实际上是 2001 ，有5个孩子。

The winner in your example is actually 2001, with 5 children.

-> SQLfiddle

• 使用函数 子路径（...） 由其他模块 ltree 。

获取路径中的最后一个节点并带有负偏移量，该偏移量是

Get the last node in the path with a negative offset, which is the direct parent of the element.

计算该父级出现的频率，排除根节点并取剩余的具有最高计数的节点。

Count how often that parent appears, exclude root nodes and take the remaining one with the highest count.

使用 ltree2text（） 从 ltree 中提取值。

如果多个节点的子节点数最多，则在示例中选择一个任意的子节点。

If multiple nodes have equally the most children an arbitrary one is picked in the example.

这是我要做的一项工作，以找到一个有用的测试用例（在消除了一些杂音之后）：

This is the work I had to do to get to a useful test case (after trimming some noise):

请参见 SQLfiddle 。

换句话说：请记得下次提供一个有用的测试用例。

In other words: please remember to provide a useful test case next time.

答案交流ent。

首先，扩展测试用例：

Answer to comment.
First, expand the test case:

ALTER TABLE tbl ADD COLUMN postal_code text
              , ADD COLUMN whatever serial;
UPDATE tbl SET postal_code = (1230 + whatever)::text;

看看：

SELECT * FROM tbl;

将 JOIN 结果简单地传递给父级基本表：

Simply JOIN result to the parent in the base table:

SELECT ct.*, t.postal_code
FROM  (
   SELECT ltree2text(subpath(path, -1, 1))::int AS tbl_id, count(*) AS children
   FROM   tbl
   WHERE  path <> ''
   GROUP  BY 1
   ) ct
LEFT   JOIN (
   SELECT tbl_id
   FROM   tbl
   WHERE  path = ''
   ) x USING  (tbl_id)
JOIN  tbl t USING (tbl_id)
WHERE  x.tbl_id IS NULL
ORDER  BY children DESC
LIMIT  1;

这篇关于Postgresql ltree查询以找到最多子级的父级；不包括根的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！