Тема 6. CTE. Рекурсивные запросы.

1. Теоретическая справка

Общие табличные выражения, CTE (Common Table Expression) — именованный временный набор данных, используемый в запросе. По сути, синоним к подзапросу: живет ровно в момент исполнения одного конкретного запроса.

WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

Синтаксис:

WITH [RECURSIVE] cte_name 
    AS (cte_query)
main_query;

Это способ задания временного набора результатов для использования в инструкциях DML (Data Manipulation Language).

Позволяет:

• Упростить запрос
• Реализовать рекурсивные запросы

Например, запрос:

WITH regional_sales AS (
    SELECT 
        region,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT 
    region, 
    product,
    SUM(quantity) AS product_units,
    SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

выводит итоги по продажам только для передовых регионов. Предложение WITH определяет два дополнительных оператора regional_sales и top_regions так, что результат regional_sales используется в top_regions, а результат top_regions используется в основном запросе SELECT. Этот пример можно было бы переписать без WITH, но тогда нам понадобятся два уровня вложенных подзапросов SELECT. Показанным выше способом это можно сделать немного проще.

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

Необязательное указание RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE, запрос WITH может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:

WITH RECURSIVE t(n) AS (
    VALUES (1) -- Нерекурсивное выражение
     UNION ALL
    SELECT n+1 FROM t WHERE n < 100 -- Рекурсивное выражение, может ссылаться на результат запроса
)
SELECT sum(n) FROM t;

Давайте вычислим числа Фибоначчи до 30:

WITH RECURSIVE fibonacci(val1, val2, step) AS (
    VALUES(1, 1, 1)
      UNION ALL
    SELECT val2, val1 + val2, step + 1 FROM fibonacci
    WHERE step < 30
)

SELECT val1, step FROM fibonacci;

В общем виде рекурсивный запрос WITH всегда записывается как не рекурсивная часть, потом UNION (или UNION ALL), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса.

Такой запрос выполняется следующим образом:

Вычисление рекурсивного запроса

1. Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.

2. Пока рабочая таблица не пуста, повторяются следующие действия:

   a. Вычисляется рекурсивная часть так, что рекурсивная ссылка на сам запрос обращается к текущему содержимому рабочей таблицы. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.

   b. Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица очищается.

Примечание! Строго говоря, этот процесс является итерационным, а не рекурсивным, но комитетом по стандартам SQL был выбран термин RECURSIVE .

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

WITH RECURSIVE 
    included_parts(sub_part, part, quantity) AS (
        SELECT 
            sub_part, 
            part, 
            quantity
        FROM 
            parts
        WHERE 
            part = 'our_product'
        
        UNION ALL
        
        SELECT 
            p.sub_part, 
            p.part, 
            p.quantity
        FROM 
            included_parts pr,
            parts p
        WHERE 
            p.part = pr.sub_part
    )
SELECT 
    sub_part, 
    SUM(quantity) AS total_quantity
FROM 
    included_parts
GROUP BY 
    sub_part;

Изменение данных в WITH:

WITH moved_rows AS (
    DELETE FROM 
        products
    WHERE
        date >= '2010-10-01' AND
        date < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log SELECT * FROM moved_rows;

Этот запрос фактически перемещает строки из products в products_log. Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое в предложении RETURNING; а затем главный запрос читает это содержимое и вставляет в таблицу products_log.

Следует заметить, что предложение WITH в данном случае присоединяется к оператору INSERT, а не к SELECT, вложенному в INSERT. Это необходимо, так как WITH может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH, так что к результату WITH можно обратиться и из вложенного оператора SELECT.

Операторы, изменяющие данные, в WITH обычно дополняются предложением RETURNING[^2], как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING, а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH не дополнен предложением RETURNING, временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:

WITH 
    t AS (
        DELETE FROM foo
    )
DELETE FROM bar;

Он удалит все строки из таблиц foo и bar. При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar.

3. Практическое задание

1. CTE

1. Создать таблицу согласно скрипту
2. Вывести самый дорогой товар в каждой из категорий.
3. Используя CTE вывести категории, имеющие более 3 товаров.
4. Используя CTE, найти категории, в которых суммарная стоимость всех товаров превышает 5000.
5. Используя CTE, вывести среднюю цену товаров в каждой категории, округлив её до целого числа.

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

1. Написать запрос для вывода первых 10 членов геометрической прогресси с первым членом = 1 и множителем = 2.

2. Написать запрос на получение суммы арифметической прогрессии с шагом в 5, с первым членом 3 и последним 48;

3. Написать запрос для вывода первых 50 чисел Фибоначи.

4. Написать запрос, который выведет по порядку все дни, начиная с даты первого семинара по Базам Данных («2019-02-04») до сегодня;

5. Написать запрос выводящий все даты, в которые были и будут семинары по Базам Данных, учитывая, что занятия начались «2019-02- 04», а закончатся до «2019-06-01» и проводятся в один и тот же день недели;

6. Создать таблицу согласно скрипту.

7. Вывести для менеджера с employee_id = 2 всех подчиненных с помощью рекурсии.

3. Рекурсия и иерархия

Дана таблица, в которой содержится иерархия отделов некоторого банка:

1. Для каждого dep_id вывести строку вида «Группа, Отдел, Управление, …» к которым он относится.

2. Вывести dep_id структурных подразделений 5-го уровня вложенности, например, «Группа системного анализа, Отдел трансформации и загрузки данных, Управление хранилищ данных и отчетности, Департамент ИТ, Банк».

4. Полезные ссылки

• Запросы WITH (Общие табличные выражения)