Общие табличные выражения (CTE)
Общее табличное выражение (CTE) — это временный результат выполнения SQL-выражения, который можно использовать в другом SQL-выражении. CTE позволяет упрощать сложные SQL-запросы, разбивая их на составные части.
CTE имеет следующий синтаксис:
WITH <CTE_name> (<column_list>) AS (
<CTE_query_definition>
)
<statement>;В приведенном выше выражении, WITH создает CTE, используя следующие параметры:
-
<CTE_name>— идентификатор CTE; -
<column_list>— необязательный параметр, содержащий список столбцов CTE; -
<CTE_query_definition>— SQL-выражение, создающее CTE; -
<statement>— SQL-выражение, использующее CTE.
Вы также можете создать несколько CTE с помощью одного WITH.
Оператор SELECT в CTE
Приведенный ниже пример показывает общее количество продаж по книге в магазине с самыми высокими продажами. Оператор WITH определяет два вспомогательных подзапроса с именами store_sales и top_stores. Выходные данные store_sales используются в top_stores, а выходные данные top_stores — в главном запросе SELECT.
WITH store_sales AS (
SELECT store, SUM(amount) AS total_sales
FROM orders
GROUP BY store
), top_stores AS (
SELECT store
FROM store_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM store_sales)
)
SELECT store,
book,
SUM(quantity) AS book_quantity,
SUM(amount) AS book_sales
FROM orders
WHERE store IN (SELECT store FROM top_stores)
GROUP BY store, book;Если переписать этот запрос без WITH, получится сложный запрос с двумя уровнями вложенных подзапросов SELECT.
Рекурсивные запросы
Рекурсивные запросы используются для обработки иерархических данных. Чтобы создать рекурсивный запрос, добавьте модификатор RECURSIVE в выражение WITH. Это позволит сослаться на собственный вывод запроса.
Рекурсивное CTE имеет следующий синтаксис:
WITH RECURSIVE <CTE_name> AS(
<CTE_query_definition> -- нерекурсивная часть
UNION [ALL]
<CTE_query definion> -- рекурсивная частьРекурсивный запрос содержит нерекурсивную часть, оператор UNION или UNION ALL и рекурсивную часть. Только рекурсивная часть может содержать ссылку на собственный вывод запроса.
PostgreSQL выполняет рекурсивное CTE в следующей последовательности:
-
Выполняет нерекурсивную часть, чтобы создать базовый набор результатов (R0).
-
Выполняет рекурсивную часть с Ri в качестве входных данных, чтобы вернуть результирующий набор Ri+1 в качестве выходных данных.
-
Повторяет шаг 2, пока не будет возвращен пустой результат.
-
Возвращает окончательный результат, который является итогом выполнения
UNIONилиUNION ALLнад наборами результатов R0, R1,…Rn.
Создадим новую таблицу, чтобы продемонстрировать рекурсивный запрос PostgreSQL:
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);Добавим данные в таблицу:
INSERT INTO employees (employee_id, full_name, manager_id)
VALUES
(1, 'James Wilson', NULL),
(2, 'Mary Burton', 1),
(3, 'Patricia Robinson', 1),
(4, 'Robert Gray', 1),
(5, 'Elizabeth Tucker', 2),
(6, 'Joseph Lewis', 2),
(7, 'William Ferguson', 2),
(8, 'Linda Black', 3),
(9, 'David Green', 3),
(10, 'Daniel Gray', 5),
(11, 'Mark Armstrong', 4),
(12, 'Donald Carter', 7),
(13, 'Elizabeth Collins', 7),
(14, 'Paul Brown', 8),
(15, 'Andrew Clarke', 8);Следующий рекурсивный запрос возвращает сотрудника со значением employee_id равным 2 и всех его подчиненных:
WITH RECURSIVE subordinates (employee_id, full_name, manager_id) AS (
SELECT employee_id, manager_id, full_name
FROM employees WHERE employee_id = 2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;Нерекурсивная часть запроса возвращает базовый набор результатов (R0):
employee_id | manager_id | full_name
-------------+------------+-------------
2 | 1 | Mary Burton
Рекурсивная часть возвращает подчиненных сотрудника из первого результата. Это итог выполнения JOIN для таблицы employees и CTE subordinates. Первая итерация рекурсивного подзапроса вернет следующие данные:
employee_id | manager_id | full_name
-------------+------------+------------------
5 | 2 | Elizabeth Tucker
6 | 2 | Joseph Lewis
7 | 2 | William Ferguson
Вторая итерация использует результаты первой итерации в качестве входных значений и вернет следующий результат:
employee_id | manager_id | full_name
-------------+------------+-------------------
12 | 7 | Donald Carter
13 | 7 | Elizabeth Collins
10 | 5 | Daniel Gray
Третья итерация вернет пустой результат, потому что у сотрудников с employee_id 10, 12 и 13 нет подчиненных.
Конечный результат представляет собой объединение всех результатов первой и второй итерации, сгенерированных нерекурсивными и рекурсивными частями.
employee_id | manager_id | full_name
-------------+------------+-------------------
2 | 1 | Mary Burton
5 | 2 | Elizabeth Tucker
6 | 2 | Joseph Lewis
7 | 2 | William Ferguson
12 | 7 | Donald Carter
13 | 7 | Elizabeth Collins
10 | 5 | Daniel Gray
Порядок поиска
Если нужно применить к результатам запроса сортировку "в глубину" (depth-first order) или "в ширину" (breadth-first order), используйте оператор SEARCH.
Добавьте фразу SEARCH DEPTH FIRST BY, чтобы показать результаты с сортировкой "в глубину". При этом в вывод запроса будет добавлен столбец, содержащий путь до записи.
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT employee_id, manager_id, full_name
FROM employees WHERE employee_id = 2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SEARCH DEPTH FIRST BY employee_id SET ordercol
SELECT * FROM subordinates ORDER BY ordercol;Результат:
employee_id | manager_id | full_name | ordercol
-------------+------------+-------------------+----------------
2 | 1 | Mary Burton | {(2)}
5 | 2 | Elizabeth Tucker | {(2),(5)}
10 | 5 | Daniel Gray | {(2),(5),(10)}
6 | 2 | Joseph Lewis | {(2),(6)}
7 | 2 | William Ferguson | {(2),(7)}
12 | 7 | Donald Carter | {(2),(7),(12)}
13 | 7 | Elizabeth Collins | {(2),(7),(13)}
Используйте SEARCH BREADTH FIRST BY, чтобы показать результаты с сортировкой "в ширину". При этом в вывод запроса будет добавлен столбец, с уровнем вложенности рекурсивного запроса.
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT employee_id, manager_id, full_name
FROM employees WHERE employee_id = 2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SEARCH BREADTH FIRST BY employee_id SET ordercol
SELECT * FROM subordinates ORDER BY ordercol;Результат:
employee_id | manager_id | full_name | ordercol
-------------+------------+-------------------+----------
2 | 1 | Mary Burton | (0,2)
5 | 2 | Elizabeth Tucker | (1,5)
6 | 2 | Joseph Lewis | (1,6)
7 | 2 | William Ferguson | (1,7)
10 | 5 | Daniel Gray | (2,10)
12 | 7 | Donald Carter | (2,12)
13 | 7 | Elizabeth Collins | (2,13)
Выявление бесконечных циклов
При работе с рекурсивными запросами важно убедиться, что рекурсивная часть запроса в конечном итоге не возвращает ни одного элемента, иначе запрос уйдет в бесконечный цикл. Вы можете использовать фразу CYCLE для обнаружения бесконечных циклов.
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT employee_id, manager_id, full_name
FROM employees WHERE employee_id=2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
) CYCLE employee_id SET is_cycle USING path
SELECT * FROM subordinates;Выражение CYCLE содержит список столбцов, которые нужно отслеживать для обнаружения бесконечных циклов (employee_id — в приведенном выше примере), имя столбца, показывающего обнаружен ли цикл (is_cycle), а также имя столбца, который отслеживает путь (path). Столбцы is_cycle и path добавляются к выходным данным запроса.
Результат:
employee_id | manager_id | full_name | is_cycle | path
-------------+------------+-------------------+----------+----------------
2 | 1 | Mary Burton | f | {(2)}
5 | 2 | Elizabeth Tucker | f | {(2),(5)}
6 | 2 | Joseph Lewis | f | {(2),(6)}
7 | 2 | William Ferguson | f | {(2),(7)}
12 | 7 | Donald Carter | f | {(2),(7),(12)}
13 | 7 | Elizabeth Collins | f | {(2),(7),(13)}
10 | 5 | Daniel Gray | f | {(2),(5),(10)}
|
РЕКОМЕНДАЦИЯ
Запрос может включать как выражение SEARCH, так и CYCLE. Столбец пути цикла вычисляется так же, как столбец сортировки "в глубину". Выражения SEARCH DEPTH FIRST BY и CYCLE, используемые вместе, создают избыточные вычисления. Чтобы избежать этого, используйте только CYCLE и отсортируйте результаты по столбцу path. Если вам нужно применить сортировку "в ширину", используйте оба выражения, SEARCH BREADTH FIRST BY и CYCLE.
|
Материализация CTE
Вы можете указать, будет ли материализоваться результат подзапроса CTE (начинающегося с AS). Это означает, что подзапрос выполнится отдельно от родительского запроса и только один раз во время его выполнения, а его результат будет записан во временную таблицу, хранящуюся в памяти. Такое поведение может быть полезно, если подзапрос содержит ресурсозатратные вычисления.
Используйте модификатор MATERIALIZED, чтобы принудительно вычислить подзапрос отдельно от родительского запроса:
WITH employees_data AS MATERIALIZED (
SELECT * FROM employees
)
SELECT full_name FROM employees_data WHERE employee_id = 5;Следующие подзапросы материализуются по умолчанию:
-
подзапросы из рекурсивных запросов CTE;
-
подзапросы, на которые ссылаются более одного раза.
Поскольку материализованный подзапрос выполняется и оптимизируется отдельно от родительского запроса, это может вызвать проблемы с производительностью. Поэтому поведение по умолчанию было изменено в версии PostgreSQL 12. В последних версиях PostgreSQL подзапросы CTE, на которые ссылаются один раз, не материализуются. Оптимизатор объединяет их с родительскими запросами и обрабатывает оба запроса одновременно. Например, оптимизатор может объединить условия из двух запросов в один фильтр для поиска по индексу.
Вы также можете использовать модификатор NOT MATERIALIZED для принудительной совместной оптимизации подзапроса и родительского запроса:
WITH employees_data AS NOT MATERIALIZED (
SELECT * FROM employees
)
SELECT (
SELECT full_name FROM employees_data WHERE employee_id = 5),
(SELECT full_name FROM employees_data WHERE employee_id = 6);Обратите внимание, что добавление модификатора NOT MATERIALIZED не влияет на рекурсивные CTE-запросы, они всегда материализуются.
Изменение данных
В выражениях WITH можно также использовать операторы INSERT, UPDATE и DELETE. Это позволяет выполнять несколько различных операций в одном запросе. В приведенном ниже примере две строки перемещаются из таблицы employees в таблицу retired_employees:
WITH moved_rows AS (
DELETE FROM employees
WHERE
employee_id = 4 OR
employee_id = 5
RETURNING *
)
INSERT INTO retired_employee
SELECT * FROM moved_rows;DELETE удаляет указанные строки из employees и возвращает их содержимое с помощью RETURNING. Родительский запрос считывает выходные данные подзапроса и добавляет их в retired_employee.
Операторы INSERT, UPDATE и DELETE, используемые в WITH, должны содержать RETURNING, чтобы можно было сослаться на их результат в родительском запросе.
Обратите внимание, что порядок выполнения операторов, используемых в WITH, не определен. Приведенный ниже запрос возвращает неизмененные данные из таблицы employees, поскольку оператор UPDATE выполняется позже, чем SELECT.
WITH employees_data AS (
UPDATE employees SET manager_id = 1 WHERE employee_id =10
RETURNING *
)
SELECT * FROM employees;Чтобы получить измененные данные, обратитесь к результату подзапроса:
WITH employees_data AS (
UPDATE employees SET manager_id = 1 WHERE employee_id =10
RETURNING *
)
SELECT * FROM employees_data;Рекурсивные ссылки в операторах, изменяющих данные, не допускаются, но вы можете включить INSERT, UPDATE или DELETE в родительский запрос. Следующий рекурсивный запрос удаляет сотрудника с employee_id 2 и всех его подчиненных из employees:
WITH RECURSIVE subordinates (employee_id, full_name, manager_id) AS (
SELECT employee_id, manager_id, full_name
FROM employees WHERE employee_id = 2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM subordinates);