我正在使用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
因此,在本例中,id 1824(根)的行有11个子行,id 1928的行有11个子行,深度为2;这是我要查找的行。
我对ltree和sql还不熟悉。
(这是一个修改过的问题,在关闭Ltree find parent with most children 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
。。。你例子中的赢家实际上是有5个孩子的
2001
。-> SQLfiddle
怎么用?
使用
subpath(...)
提供的功能the additional module 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;
只需将结果发送给基表中的父表:
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;