🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Иерархии в SQL Oracle
Допустим, у нас есть данные из бухгалтерского баланса, представленные иерархически:
▶️Активы ➡️ Дебиторская задолженность
▶️Активы ➡️ Денежные средства ➡️ Касса
▶️Активы ➡️ Денежные средства ➡️ Расчётные счета
🟠Если представлять это в виде таблицы, то такая таблица имела бы поля:
— account_id - id статьи
— name - наименование статьи (активы, ден. ср-ва, касса и т.д.)
— parent_id - id родительской статьи. Например, если у статьи "Активы" account_id = 1, то у статьи "Денежные средства":
parent_id = account_id ("Активы") = 1
потому что "Денежные средства" - это вид статьи "Активы", стоящий на 1 ступень ниже.
Аналогично будет и со статьей "Касса". Если у "Денежные средства" account_id = 2, то у статьи "Касса":
parent_id = account_id ("Денежные средства") = 2
Вообще, если брать в расчет банковский контекст, то для таких данных, связанных с фин. отчетностью, наиболее часто пишутся иерархические скрипты.
🟠На примере этих данных разберемся, как можно работать с иерархиями. Строятся они в оркале с помощью такого синтаксиса:
SELECT
account_id,
name,
parent_id,
LEVEL
FROM accounts
START WITH parent_id IS NULL
CONNECT BY PRIOR account_id = parent_id
ORDER SIBLINGS BY name
где:
▶️LEVEL - уровень иерархии. Для статьи "Активы" он = 1, для статьи "Денежные средства" он = 2 и т.д.
▶️START WITH parent_id IS NULL. Говорит о том, что бы иерархия начинала строиться с той статьи, у которой parent_id IS NULL. То есть выбирается статья "Активы", т.к. у нее нет родительского id, потому что это самая "старшая" статья. И далее идет parent_id =1, потом 2 и т.д.
▶️CONNECT BY PRIOR account_id = parent_id - строка, с которой начинается именно само построение иерархии. Она берет account_id из родителя и ищет строки, где parent_id равен этому значению.
Возвращаясь к нашему примеру, начинается всё со статьи "Активы", которая была выбрана в прошлом шаге. У нее account_id = 1. И потом ищутся те статьи, у которых parent_id тоже равен 1.
▶️ORDER SIBLINGS BY name. Обычный ORDER BY не работает в иерархических запросах - только ORDER SIBLINGS BY. Это означает, что внутри каждой родительской группы дочерние значения сортируются по порядку/алфавиту.
То есть, если брать группу "Денежные средства", то дочерние значения в ней - "Касса" и "Расчётные счета". И именно они сортируются по алфавиту.
🟠На выходе после такого скрипта получится таблица, упорядоченная по иерархии сверху-вниз + с доп. полем LEVEL
🟠Дополнительно к запросу можно добавить еще:
CONNECT BY NOCYCLE PRIOR
account_id = parent_id
С помощью этого можно избежать зацикливания, если, например, по ошибке было указано, что "Расчётные счета" являются подкатегорией "Денежных средств", а потом "Денежные средства" - подкатегорией "Расчётных счетов".
В этом случае NOCYCLE пометит такие записи и пойдет дальше. Чтобы увидеть помеченные строки, в SELECT нужно дополнительно прописать поле CONNECT_BY_ISCYCLE.
Если же NOCYCLE не писать, оракл выдаст ошибку.
SELECT
LPAD(' ', (LEVEL - 1) * 2) || name
AS tree_name,
Добавление этой строки в селект позволит визуализировать иерархическое дерево, а не выводить его в виде обычной таблицы.