Рекурсивные подзапросы

Листовые строки CONNECT_BY_ISLEAF

Строку в рекурсивном запросе называют листом, если в рекурсивной части подзапроса для нее не найдено дочерних строк.

В Oracle в рекурсивных запросах через CONNECT BY есть элегантный способ определения листовых строк - псевдостолбец CONNECT_BY_ISLEAF. Он равен 1 для листовых строк, и 0 для родительских.

К сожалению, Postgres нам такой роскоши не предоставляет. Поэтому придется что-то придумывать самим.

Возьмем иерархию категорий товаров

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level
  FROM lv_hierarchy c
 ORDER BY path_sort
#category_idfull_namelevel
111. Товары для дома1
23....1.1. Бытовая техника2
322. Цифровая техника1
47....2.1. Аудиотехника2
56....2.2. Игровые консоли2
64....2.3. Ноутбуки и аксессуары2
79........2.3.1. Ноутбуки3
810........2.3.2. Рюкзаки3
98....2.4. Сотовые телефоны2
105....2.5. Фотоаппараты2

Способ 1. Подзапрос not exists

Какие категории являются листьями? Те, у которых нет подкатегорий.

Определить категории, в которых нет подкатегорий, можно с помощью подзапроса not exists:

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       not exists (
         SELECT 1
           FROM category ch
          WHERE ch.parent_category_id = c.category_id
       ) AS is_leaf
  FROM lv_hierarchy c
 ORDER BY path_sort
#category_idfull_nameis_leaf
111. Товары для домаFALSE
23....1.1. Бытовая техникаTRUE
322. Цифровая техникаFALSE
47....2.1. АудиотехникаTRUE
56....2.2. Игровые консолиTRUE
64....2.3. Ноутбуки и аксессуарыFALSE
79........2.3.1. НоутбукиTRUE
810........2.3.2. РюкзакиTRUE
98....2.4. Сотовые телефоныTRUE
105....2.5. ФотоаппаратыTRUE

Способ 2. Сравнить уровень вложенности текущей и следующей строки.

Посмотри внимательно на уровень вложенности в отсортированных строках иерархии.

#category_idfull_namelevel
111. Товары для дома1
23....1.1. Бытовая техника2
322. Цифровая техника1
47....2.1. Аудиотехника2
56....2.2. Игровые консоли2
64....2.3. Ноутбуки и аксессуары2
79........2.3.1. Ноутбуки3
810........2.3.2. Рюкзаки3
98....2.4. Сотовые телефоны2
105....2.5. Фотоаппараты2

Если у текущего каталога есть вложенные подкаталоги, то на следующей строке будет располагаться один из них. Уровень вложенности подкаталога будет больше уровня вложенности текущего каталога на 1.

Получить значение поля в следущей строке можно с помощью аналитической функции lead.

lead(value, shift, default_value) over (order by sorting)

где:

  • value - какое значение взять, ссылается на следующую строку.
  • shift - через сколько строк смотреть.
  • default_value - какое значение использовать, если следующей строки нет. Ссылается на текущую строку.
  • sorting - в каком порядке расположить строки для вычисления значения.
WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level,
       lead(c.level, 1, c.level) over (order by c.path_sort) AS level_next
  FROM lv_hierarchy c
 ORDER BY path_sort
#category_idfull_namelevellevel_next
111. Товары для дома12
23....1.1. Бытовая техника21
322. Цифровая техника12
47....2.1. Аудиотехника22
56....2.2. Игровые консоли22
64....2.3. Ноутбуки и аксессуары23
79........2.3.1. Ноутбуки33
810........2.3.2. Рюкзаки32
98....2.4. Сотовые телефоны22
105....2.5. Фотоаппараты22

Осталось сравнить level и level_next:

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level >= lead(c.level, 1, c.level) over (order by c.path_sort) AS is_leaf
  FROM lv_hierarchy c
 ORDER BY path_sort
#category_idfull_nameis_leaf
111. Товары для домаFALSE
23....1.1. Бытовая техникаTRUE
322. Цифровая техникаFALSE
47....2.1. АудиотехникаTRUE
56....2.2. Игровые консолиTRUE
64....2.3. Ноутбуки и аксессуарыFALSE
79........2.3.1. НоутбукиTRUE
810........2.3.2. РюкзакиTRUE
98....2.4. Сотовые телефоныTRUE
105....2.5. ФотоаппаратыTRUE

P.S. Зачем вообще решать одну задачу разными способами? Когда в таблицах очень много строк, тогда запросы могут выполняться медленно. В зависимости от плана выполнения запроса время выполнения может различаться во много раз. Чтобы запрос выполнялся по разному, нужно уметь получать один и тот же результат разными способами.

ПредыдущаяСледующая