Что такое cte
Обобщённое табличное выражение или CTE (Common Table Expressions) — это временный результирующий набор данных, к которому можно обращаться в последующих запросах. Для написания обобщённого табличного выражения используется оператор WITH .
MySQL-- Пример использования конструкции WITH WITH Aeroflot_trips AS (SELECT TRIP.* FROM Company INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot") SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;Выражение с WITH считается «временным», потому что результат не сохраняется где-либо на постоянной основе в схеме базы данных, а действует как временное представление, которое существует только на время выполнения запроса, то есть оно доступно только во время выполнения операторов SELECT , INSERT , UPDATE , DELETE или MERGE . Оно действительно только в том запросе, которому он принадлежит, что позволяет улучшить структуру запроса, не загрязняя глобальное пространство имён.
MySQLWITH название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос) [, название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)] …Порядок использования оператора WITH :
- Ввести оператор WITH
- Указать название обобщённого табличного выражения
- Опционально: определить названия для столбцов получившегося табличного выражения, разделённых знаком запятой
- Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе
- Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4
- Создаём табличное выражение Aeroflot_trips , содержащие все полёты, совершенные авиакомпанией «Aeroflot»
MySQLWITH Aeroflot_trips AS (SELECT plane, town_from, town_to FROM Company INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot") SELECT * FROM Aeroflot_trips;
plane town_from town_to IL-86 Moscow Rostov IL-86 Rostov Moscow
- Аналогично, создаём табличное выражение Aeroflot_trips , но с переименованными колонками
MySQLWITH Aeroflot_trips (aeroflot_plane, town_from, town_to) AS (SELECT plane, town_from, town_to FROM Company INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot") SELECT * FROM Aeroflot_trips;
aeroflot_plane town_from town_to IL-86 Moscow Rostov IL-86 Rostov Moscow
- С помощью оператора WITH определяем несколько табличных выражений
MySQLWITH Aeroflot_trips AS (SELECT TRIP.* FROM Company INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot"), Don_avia_trips AS (SELECT TRIP.* FROM Company INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Don_avia") SELECT * FROM Don_avia_trips UNION SELECT * FROM Aeroflot_trips;
id company plane town_from town_to time_out time_in 1181 1 TU-134 Rostov Moscow 1900-01-01T06:12:00.000Z 1900-01-01T08:01:00.000Z 1182 1 TU-134 Moscow Rostov 1900-01-01T12:35:00.000Z 1900-01-01T14:30:00.000Z 1187 1 TU-134 Rostov Moscow 1900-01-01T15:42:00.000Z 1900-01-01T17:39:00.000Z 1188 1 TU-134 Moscow Rostov 1900-01-01T22:50:00.000Z 1900-01-02T00:48:00.000Z 1195 1 TU-154 Rostov Moscow 1900-01-01T23:30:00.000Z 1900-01-02T01:11:00.000Z 1196 1 TU-154 Moscow Rostov 1900-01-01T04:00:00.000Z 1900-01-01T05:45:00.000Z 1145 2 IL-86 Moscow Rostov 1900-01-01T09:35:00.000Z 1900-01-01T11:23:00.000Z 1146 2 IL-86 Rostov Moscow 1900-01-01T17:55:00.000Z 1900-01-01T20:01:00.000Z Табличные выражения 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 T-SQL | CTE-выражения
CTE – Common Table Expression – виртуальное представление данных, которое можно использовать, не оформляя хранимой процедурой / функцией или представлением данных (View).
Если для возврата данных удобно строить несколько запросов, каждый из которых решает свою подзадачу, то вам наверняка приходилось строить несколько вьюх, включая результирующую, которая отбирает конечный набор данных.
Подобную задачу можно решить также с помощью CTE-выражений (с версии MS SQL 2005).
CTE позволяет внутри одного запроса строить подзапросы – временные результаты обработки данных, к которым в этом же выражении можно обращаться:
WITH CTE_name ( FieldName1 , FieldName2 ) AS
SELECT Field1 , Field2 FROM Table1
SELECT * FROM CTE_name
При больших объемах данных и связей между ними, с помощью CTE-выражений можно оптимизировать время выгрузки, например, если разбить логику отбора данных на CTE, вставить в каждый требуемые фильтры и потом CTE-выражения связать:
WITH CTE_name_1 ( FieldName1 , FieldName2 ) AS
SELECT Field1 AS ID , Field2 FROM Table1
WHERE Field2 >= 1000
CTE_name_2 ( FieldName3 , FieldName4 ) AS
SELECT Field3 AS ID , Field4 FROM Table2
WHERE Field4 = 'Москва'
SELECT * FROM CTE_name_1 INNER JOIN CTE_name_2 ON CTE_name_2.ID = CTE_name_1.ID
Преимущество CTE-выражения, что данные подзапроса при многократном использовании возвращаются быстрее (по сравнению с традиционным SELECT-подзапросом), т.к. CTE компелируется только при первом вызове. Это особенно актуально, если к одному и тому же подзапросу требуется обратиться больше одного раза .
Результат CTE-выражения можно использовать со всеми стандартными операторами DML (SELECT, INSERT, DELETE, UPDATE), также его удобно использовать для рекурсивных вычислений (например, таких как представлены в примерах Рекурсивные функции).
