Как подсчитать количество записей в цепочке?

Question

У меня ессть упрощенная таблица перемещения пассажирских поездов, которые показывают его перемещение из города в город:

create table testt (
    ID NUMBER,
    VALST VARCHAR2(255),
    VALEND VARCHAR2(255),
    NEXTID NUMBER
)

   ID VALST  VALEND NEXTID
----- ----- ------- ------
    1   MOS     SAN      2
    2   SAN     MOS      
    3   MOS     EKB      4
    4   EKB     RYA      5
    5   RYA     SAN      6
    6   SAN     MOS      
    7   MOS     KRA      8
    8   KRA     VOR      9
    9   VOR     MOS

Работает она следующим образом: В ячейке VALST показано, откуда выезжает поезд, в VALEND - куда он приезжает. В ячейке NEXTID написан идентификатор следующего рейса. То есть, например, по ID 1 поезд поедет сначала из MOS в SAN, затем, в следующем рейсе под ID 2 он поедет из SAN в MOS, и цепочка на этом закончится, так как нет следующего идентификатора.

Задача состоит в следующем: определить количество рейсов в цепочке, то есть, когда поезд выезжает из MOS, и в итоге возвращается в MOS.

В примере сверху из ID 1 размер цепочки будет равен два, а из ID 3 цепочка будет равна четырем.

Пробовал через функцию LEAD, но, к сожалению, не получилось, прошу помощи.

Answer 1

Вот так это выглядит в MySQL (извини, для ORACLE не стал делать, там сущее мучение с вводом данных в fiddle, а за диагностику проблем так и вовсе руки оторвать, но выглядеть будет практически так же):

WITH RECURSIVE
cte1 AS ( SELECT ID, VALST, VALEND, NEXTID, 1 AS length, ROW_NUMBER() OVER (ORDER BY ID) chain
          FROM testt
          WHERE NEXTID IS NULL
          UNION ALL
          SELECT testt.ID, testt.VALST, cte1.VALEND, testt.NEXTID, cte1.length + 1, cte1.chain
          FROM testt
          JOIN cte1 ON testt.NEXTID = cte1.ID ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY chain ORDER BY length DESC) rn
          FROM cte1 )
SELECT ID, VALST, length 
FROM cte2
WHERE rn = 1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a41425e1b0c3cb532c410351a95f8ace

Answer 2

Данные представляют собой обратную иерархию (reverse hierarchy).

Решатся простым иерархическим запросом (на db<>fiddle):

select valst, level numoftrips, sys_connect_by_path (valst, ' >> ') trips
from testt t
where connect_by_isleaf = 1
start with nextid is null 
connect by nextid = prior id

Результат:

VALST  NUMOFTRIPS TRIPS                            
------ ---------- --------------------------------
MOS             2  >> SAN >> MOS                  
MOS             4  >> SAN >> RYA >> EKB >> MOS    
MOS             3  >> VOR >> KRA >> MOS

БЛОГ НА HUSL

Как подсчитать количество записей в цепочке?

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