Как из иерархического запроса вывести только самую длинную цепочку?

Question

Есть таблица comments такого вида:

ID	Created date	Text	Post_ID	User_ID	Parent_Comment_ID
...	...	...	...	...	...

На текущий момент используется такой запрос:

select
    level,
    id,
    lpad(' ', 2 * level) || text as text,
    post_id, 
    child_comment_id
from
    "comment"
start with
    child_comment_id is null
connect by prior
    id = child_comment_id;

Как можно вывести не все цепочки, а только самую длинную?

Answer 1

На таких данных:

select 1, 'comment post 1', 1, 1, null from dual union all
    select 2, 'comment post 1', 1, 2,    1 from dual union all
    select 3, 'comment post 1', 1, 3,    2 from dual union all
    select 4, 'comment post 2', 2, 1, null from dual union all
    select 5, 'comment post 2', 2, 2,    4 from dual union all
    select 6, 'comment post 2', 2, 3,    5 from dual union all
    select 7, 'comment post 2', 2, 4,    6 from dual
/

Можно получить самую длинную цепочку таким запросом:

select id, text, parentId, path 
from (
    select c.*, level lvl,
        sys_connect_by_path(id, '/') path,
        max (length (sys_connect_by_path(null, '.'))) over () maxlen,
        max (length (sys_connect_by_path(null, '.'))) over (partition by postId) grplen
    from comments c
    start with parentID is null
    connect by prior id = parentId
) where grplen = maxlen order by id
/

Результат:

        ID TEXT           PARENTID PATH            
---------- -------------- -------- ----------------
         4 comment post 2     null /4              
         5 comment post 2        4 /4/5            
         6 comment post 2        5 /4/5/6          
         7 comment post 2        6 /4/5/6/7

Answer 2

Не всегда представляется возможным вычислить длину пути для определённой группы, например, по пользователям или постам.

Поэтому, более универсальное решение, считать только макс. глубину всех веток (в вопросе - цепочек), далее вывести только ветки с листьями, которые находятся на макс, глубине.

Например, для таких данных таблицы:

        ID TEXT      POSTID   USERID PARENTID
---------- ------- -------- -------- --------
         1 comment        1        1     null
         2 comment        1        2        1
         3 comment        1        3        2
         4 comment        1        4        1
         5 comment        1        5        4
         6 comment        1        6        5
         7 comment        1        7        6

Чтобы вывести все узлы самой(ых) длинной(ых) ветки(ок), просто сделать реверсный (обратный) иерархический запрос:

select id, text, parentId, path
from (
    select c.*, level lvl, connect_by_isleaf isleaf,
        sys_connect_by_path(id, '/') path,
        max (level) over () maxlvl
    from comments c
    start with parentID is null
    connect by prior id = parentId)
start with isleaf = 1 and lvl = maxlvl
connect by prior parentid = id
--where lvl = maxlvl
order by id
/

        ID TEXT    PARENTID PATH            
---------- ------- -------- ----------------
         1 comment     null /1              
         4 comment        1 /1/4            
         5 comment        4 /1/4/5          
         6 comment        5 /1/4/5/6        
         7 comment        6 /1/4/5/6/7

Для вывода только листьев с полным путём, вместо start with ... connect by ... добавте закомментированое условие WHERE. Результат будет:

        ID TEXT    PARENTID PATH            
---------- ------- -------- ----------------
         7 comment        6 /1/4/5/6/7

БЛОГ НА HUSL

Как из иерархического запроса вывести только самую длинную цепочку?

Ответы (2 шт):