🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Рекурсивные СТЕ в SQL
Продолжая тему иерархий, напомню, что имеются такие данные:
▶️Активы ➡️ Дебиторская задолженность
▶️Активы ➡️ Денежные средства ➡️ Касса
▶️Активы ➡️ Денежные средства ➡️ Расчётные счета
Если представлять это в виде таблицы, то такая таблица имела бы поля:
— account_id - id статьи
— name - наименование статьи (активы, ден. ср-ва, касса и т.д.)
— parent_id - id родительской статьи
🟠Сегодня речь пойдет о рекурсивных СТЕ как об альтернативе упомянутому ранее методу, потому что CONNECT BY - уникальная фича Oracle, в других стандартных диалектах такое не встречается.
На примере наших данных для построения иерархии можно написать такой запрос:
WITH RECURSIVE tree AS (
SELECT account_id, name,
parent_id, 1 AS level
FROM accounts
WHERE parent_id IS NULL
UNION ALL
SELECT a.account_id, a.name,
a.parent_id, t.level + 1
FROM accounts a
JOIN tree t ON a.parent_id = t.account_id
)
SELECT * FROM tree
Такой запрос является самым простым, можно сказать, даже шаблонным, для построения типичной иерархии таким методом.
🟠Изначально СТЕ задается с помощью WITH RECURSIVE. Главное отличие от обычной СТЕ - это то, что рекурсивная может ссылаться сама на себя.
Что это значит?
В этом примере мы назвали СТЕ именем tree и начали его определять с помощью дальнейшего запроса. Но в JOIN к таблице accounts присоединяется tree, которое мы еще не закончили определять. Если бы запрос был составлен с помощью обычного СТЕ, то при таком JOIN была бы ошибка.
🟠Теперь касаемо логики построения иерархии.
1️⃣В первом SELECT выбираются те строки из accounts, где parent_id IS NULL, то есть выбираются начальные строки для нашей иерархии, прямо как в START WITH parent_id IS NULL из прошлого поста.
В нашем случае - это статья "Активы", т.к. у нее нет родительского id, потому что это самая "старшая" статья.
На данном этапе CTE "tree" содержит только одну строку - это статью "Активы" с account_id = 1 и parent_id IS NULL.
2️⃣Во втором SELECT берется просто таблица accounts, к которой джойнится результат из первого SELECT по parent_id = account_id, то есть со смещением.
Иными словами, статья "Активы" с account_id = 1 присоединяется к статьям "Дебиторская задолженность" и "Денежные средства" с parent_id = 1.
3️⃣После этого результаты селектов объединяются с помощью UNION ALL.
На данном этапе CTE "tree" содержит уже 3 строки - это статью "Дебиторская задолженность" и "Денежные средства" + ранее полученные "Активы".
4️⃣И сейчас запускается рекурсия. Причем запускается она не с первого SELECT - там ей просто не от куда взяться. Запускается она со второго, потому что в нем присутствует JOIN tree, который уже содержит 3 строки.
И снова выбираются данные из accounts, снова к ним со смещением присоединяются строки из "tree", и в итоге получаем уже набор из всех строк (в нашем случае)
5️⃣Заканчивается рекурсия на том моменте, когда между accounts и tree уже нет совпадений и результат ничего не выдает (потому что INNER JOIN).
🟠В итоге получаем таблицу в виде таблицы accounts, только упорядоченную по иерархии и с добавлением уровня иерархии (поле level).