Обобщенные табличные выражения (CTE)¶
Табличные выражение являются очень удобной структурой в SQL-запросе и помогают существенно упростить как сам запрос, так и его дальнейшую поддержку. Табличные выражения можно назвать анаголом временных таблиц, актуальных только в рамках одного запроса.
В табличных выражениях может быть как SELECT, так и модификация данных — INSERT, UPDATE или DELETE.
Как и в случае создания временных таблиц, табличные выражения помимо упрощения структуры самого запроса, помогают кешировать результат какого-то вычисления, которое в ином случае бы пришлось вычислять больше одного раза.
Каждое табличное выражение имеет имя и может определять имена возвращаемых полей. После определения табличных выражений должен следовать основной запрос, которые может использовать результат определенных ранее табличных выражение, но не обязан это делать. Все табличные выражение в запросе выполняются параллельно (если они не зависят друг от друга), но в рамках одного снапшота. Поэтому выполнение изменения таблицы в одном выражении и чтение из этой же таблицы в другом выражении может привести к неожиданным результат. Так же как и параллельное изменение одной и той же таблицы в двух выражениях.
Using RECURSIVE, a WITH query can refer to its own output.
Note: Strictly speaking, this process is iteration not recursion, but RECURSIVE is the terminology chosen by the SQL standards committee.
it is important to be sure that the recursive part of the query will eventually return no tuples
ou can use data-modifying statements (INSERT, UPDATE, or DELETE)
This is necessary because data-modifying statements are only allowed in WITH clauses that are attached to the top-level statement.
Recursive self-references in data-modifying statements are not allowed.
The sub-statements in WITH are executed concurrently with each other and with the main query.
Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable.
Обобщенное табличное выражение (CTE)
Определяет временный результирующий набор, на который можно ссылаться, возможно, несколько раз в область инструкции SQL. CTE используется в основном в инструкции SELECT .
Синтаксис
WITH common_table_expression [, . ] common_table_expression view_identifier [ ( column_identifier [, . ] ) ] [ AS ] ( query ) Параметры
- view_identifier Идентификатор, по которому можно ссылаться common_table_expression на
- column_identifier Необязательный идентификатор, по которому можно ссылаться на столбец common_table_expression объекта . Если column_identifier указано значение s, их число должно соответствовать количеству столбцов, возвращаемых query объектом . Если имена не указаны, имена столбцов являются производными от query .
- Запроса Запрос, создающий результирующий набор.
Примеры
-- CTE with multiple column aliases > WITH t(x, y) AS (SELECT 1, 2) SELECT * FROM t WHERE x = 1 AND y = 2; 1 2 -- CTE in CTE definition > WITH t AS ( WITH t2 AS (SELECT 1) SELECT * FROM t2) SELECT * FROM t; 1 -- CTE in subquery > SELECT max(c) FROM ( WITH t(c) AS (SELECT 1) SELECT * FROM t); 1 -- CTE in subquery expression > SELECT (WITH t AS (SELECT 1) SELECT * FROM t); 1 -- CTE in CREATE VIEW statement > CREATE VIEW v AS WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4) SELECT * FROM t; > SELECT * FROM v; 1 2 3 4 -- CTE names are scoped > WITH t AS (SELECT 1), t2 AS ( WITH t AS (SELECT 2) SELECT * FROM t) SELECT * FROM t2; 2 Табличные выражения SQL
Прием № 1, чтобы писать хорошие читаемые SQL-запросы — это табличные выражения (CTE). Люди их боятся, а зря. Давайте разберемся за три минуты, читать увесистую книгу по SQL или проходить курсы не придется.
Проблема
Допустим, у нас есть таблица продаж по месяцам за два года:
┌──────┬───────┬───────┬──────────┬─────────┐ │ year │ month │ price │ quantity │ revenue │ ├──────┼───────┼───────┼──────────┼─────────┤ │ 2019 │ 1 │ 60 │ 200 │ 12000 │ │ 2019 │ 2 │ 60 │ 660 │ 39600 │ │ 2019 │ 3 │ 60 │ 400 │ 24000 │ │ 2019 │ 4 │ 60 │ 300 │ 18000 │ │ 2019 │ 5 │ 60 │ 440 │ 26400 │ │ 2019 │ 6 │ 60 │ 540 │ 32400 │ │ 2019 │ 7 │ 60 │ 440 │ 26400 │ │ 2019 │ 8 │ 60 │ 440 │ 26400 │ │ 2019 │ 9 │ 60 │ 250 │ 15000 │ │ 2019 │ 10 │ 60 │ 420 │ 25200 │ │ . │ . │ . │ . │ . │ └──────┴───────┴───────┴──────────┴─────────┘ Мы хотим выбрать только те месяцы, выручка за которые превысила среднемесячную за год.
Для начала посчитаем среднемесячную выручку по годам:
select year, avg(revenue) as avg_rev from sales group by year; ┌──────┬─────────┐ │ year │ avg_rev │ ├──────┼─────────┤ │ 2019 │ 25125.0 │ │ 2020 │ 48625.0 │ └──────┴─────────┘ Теперь можно выбрать только те записи, revenue в которых не уступает avg_rev :
select sales.year, sales.month, sales.revenue, round(totals.avg_rev) as avg_rev from sales join ( select year, avg(revenue) as avg_rev from sales group by year ) as totals on sales.year = totals.year where sales.revenue >= totals.avg_rev; ┌──────┬───────┬─────────┬─────────┐ │ year │ month │ revenue │ avg_rev │ ├──────┼───────┼─────────┼─────────┤ │ 2019 │ 2 │ 39600 │ 25125.0 │ │ 2019 │ 5 │ 26400 │ 25125.0 │ │ 2019 │ 6 │ 32400 │ 25125.0 │ │ 2019 │ 7 │ 26400 │ 25125.0 │ │ . │ . │ . │ . │ └──────┴───────┴─────────┴─────────┘ Решили с помощью подзапроса:
- внутренний запрос считает среднемесячную выручку;
- внешний соединяется с ним и фильтрует результаты.
Запрос в целом получился сложноват. Если вернетесь к нему спустя месяц — наверняка потратите какое-то время на «распутывание». Проблема в том, что такие вложенные запросы приходится читать наоборот:
- найти самый внутренний запрос, осознать;
- мысленно присоединить к более внешнему;
- присоединить к следующему внешнему, и так далее.
Хорошо, когда вложенных уровня два, как в нашем примере. На практике же я часто встречаю трех- и четырехуровневые подзапросы. Форменное издевательство над читателем.
Решение
Вместо подзапроса можно использовать табличное выражение (common table expression, CTE). Любой подзапрос X :
select a, b, c from (X) where e = f Механически превращается в CTE:
with cte as (X) select a, b, c from cte where e = f В нашем примере:
with totals as ( select year, avg(revenue) as avg_rev from sales group by year ) select sales.year, sales.month, sales.revenue, round(totals.avg_rev) as avg_rev from sales join totals on totals.year = sales.year where sales.revenue >= totals.avg_rev; С табличным выражением запрос становится одноуровневым — так воспринимать его намного проще. Кроме того, табличное выражение можно переиспользовать в пределах запроса, как будто это обычная таблица:
with totals as (. ) select . from sales_ru join totals . union all select . from sales_us join totals . Табличные выражения SQL чем-то похожи на функции в обычном языке программирования — они уменьшают общую сложность:
- Можно написать нечитаемую простыню кода, а можно разбить код на понятные отдельные функции и составить программу из них.
- Можно возвести башню из пяти этажей подзапросов, а можно вынести подзапросы в CTE и составить общий запрос из них.
CTE против подзапроса
Существует миф, что «CTE медленные». Он пришел из старых версий PostgreSQL (11 и раньше), которые всегда материализовали CTE — вычисляли полный результат табличного выражения и запоминали до конца запроса.
Обычно это хорошо: один раз вычислил результат, и дальше используешь его несколько раз по ходу основного запроса. Но иногда материализация мешала движку оптимизировать запрос:
with cte as (select * from foo) select * from cte where id = 500000; Здесь выбирается ровно одна запись по идентификатору, но материализация создает в памяти копию всей таблицы — из-за этого запрос отработает очень медленно.
PostgreSQL 12+ и другие современные СУБД поумнели и больше так не делают. Материализация применяется, когда от нее больше пользы, чем вреда. Плюс, многие СУБД позволяют явно управлять этим поведением через инструкции MATERIALIZED / NOT MATERIALIZED .
Так что CTE не медленнее подзапросов. А если сомневаетесь, всегда можно сделать два варианта — подзапрос и табличное выражение — и сравнить план и время выполнения.
Как понять, когда использовать подзапрос, а когда CTE? Я вывел для себя простое правило, которое пока ни разу не подвело:
Чего и вам желаю.
P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции
Подписывайтесь на канал, чтобы не пропустить новые заметки ��
Общие табличные выражения (CTE) стр. 1
Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.
Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных «Вторсырье», а также тип операции, дату и пункт приема, когда и где она была зафиксирована.
Задачу можно решить, например, следующим способом.
Консоль
Выполнить
Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.
Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:
Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:
Консоль
Выполнить
Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),
Консоль
Выполнить
Общие табличные выражения позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же производным таблицам.
| Страницы: | 1 | 2 | 3 |