Postgres с рекурсивным результатом для включения как дедушки, так и внуков

#postgresql #recursive-query #with-statement

#postgresql #рекурсивный запрос #с помощью-statement

Вопрос:

Мое генеалогическое древо содержит детей и родителей. Я могу написать запрос, который возвращает внуков для данного пользователя, используя postgres with recursive . Но как я могу включить дедушку и бабушку в результаты? Моя цель — сообщить количество внуков, сгруппированных по идентификатору дедушки и бабушки. Как мне сослаться на нерекурсивную часть запроса в конечных результатах?

отредактировано — таблица примеров:

 child parent
  11   null
  12   null
  13   null
  21    11
  22    11
  31    12
  32    12
  33    12
  41    13
  81    21
  82    21
  83    21
  91    33
  92    33

нерекурсивная часть запроса:

 select distinct child where parent is null -- as grandparent

желаемый результат:

 grandparent, grandchildren
     11           3
     12           2
     13           0

Ответ №1:

это подойдет:

 with recursive zzz AS (
  select child AS grandparent, child AS current_child, 1 AS grand_level 
  FROM thetable AS tt
  where parent is null
 UNION
  SELECT grandparent, tt.child, grand_level 1
  FROM thetable AS tt
  JOIN zzz
    ON tt.parent = zzz.current_child
)
SELECT grandparent, COUNT(DISTINCT current_child)FILTER(WHERE grand_level = 3) AS grandchildren
FROM zzz
GROUP BY grandparent;