PostgreSQL的ltree查询发现大多数孩子父母; 不包括根(Postgresql ltr

我使用PostgreSQL和有一个路径列，它是类型的表ltree 。

我想解决的问题是：给出整个树状结构，什么父母有大多数孩子不包括根。

样本数据是这样的：

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

所以，在这个例子中，编号为1824（根目录）的行有11名儿童和ID为1928有11名儿童深度为2的行; 这就是我以后的行。

我是新来ltree和SQL对这一问题。

（这是添加了样本数据的修订问题后Ltree找到大多数孩子的PostgreSQL父被关闭）。

解

为了找到最子女节点：

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

...并排除根节点：

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 ...

在您的示例中的赢家其实是2001 ，有5个孩子。

- > SQLfiddle

怎么样？

使用功能的subpath(...)通过所提供的附加模块ltree 。
获取与负偏移 ，这是该元素的直接父路径中的最后一个节点 。
怎么算常常是父母出现，排除根节点，并在剩下的具有最高计数。
使用ltree2text()来提取值ltree 。
如果多个节点有同样的大多数孩子的任意一个在示例中挑选的。

测试用例

这是我必须做得到一个有用的测试案例（修剪一些噪音后）的工作：

见SQLfiddle 。

换句话说：请记住提供一个有用的测试情况下一次。

其他列

答发表评论。
一是扩大测试案例：

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

看一看：

SELECT * FROM tbl;

只要JOIN结果在基表中的父：

 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;