Postgresql ltree запрос, чтобы найти родителя с большинством детей; исключая корень

Я использую 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 детей, а строка с идентификатором 1928 имеет 11 детей с глубиной 2; это строка, за которой я следую.

Я новичок в ltree и sql, если на то пошло.

(Это пересмотренный вопрос с добавленными образцами данных после того, как Ltrit find parent с большинством детей 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;