Когда однозначно стоит использовать cte
Перейти к содержимому

Когда однозначно стоит использовать cte

  • автор:

SQL. Обобщенное табличное выражение и способы его использования

Обобщенное табличное выражение является общим инструментов для многих баз данных. Рассмотрим конкретнее, что же это такое и как с ним работать на примере средств MS SQL.

Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как select, update, insert и delete.

Выведем количество сотрудников, устроившихся на работу, в разбивке по годам:

WITH TABLE_CTE (FIO, ID_DEPART, BEGIN_YEAR) AS ( SELECT FIO, ID_DEPART, YEAR(DATE_BEGIN) as BEGIN_YEAR FROM EMPL ) SELECT count(FIO) as COUNT_SOTR, BEGIN_YEAR FROM TABLE_CTE group by BEGIN_YEAR

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

WITH TABLE_CTE1(FIO, YEAR_EMPL) AS ( SELECT FIO, YEAR(DATE_BEGIN) as YEAR_EMPL FROM EMPL ), TABLE_CTE2 (COUNT_FIO, YEAR_EMPL) AS ( SELECT count(FIO) as COUNT_FIO, YEAR_EMPL FROM TABLE_CTE1 group by YEAR_EMPL ) SELECT * FROM TABLE_CTE2

Основные способы использования:

  • для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода);
  • в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц;
  • для создания представлений (VIEW) в части select;
  • для написания рекурсивных запросов.

Отличия от вложенного запроса:

  • вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса.

Отличия от временной таблицы:

  • заполнение временной таблицы при больших объемах создает нагрузку на диск;
  • исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb).

Мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.

Табличные выражения 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

Обобщённое табличное выражение или 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 . Оно действительно только в том запросе, которому он принадлежит, что позволяет улучшить структуру запроса, не загрязняя глобальное пространство имён.

MySQL
WITH название_cte [(столбец_1 [, столбец_2 ])] AS (подзапрос) [, название_cte [(столбец_1 [, столбец_2 ])] AS (подзапрос)] 

Порядок использования оператора WITH :

  1. Ввести оператор WITH
  2. Указать название обобщённого табличного выражения
  3. Опционально: определить названия для столбцов получившегося табличного выражения, разделённых знаком запятой
  4. Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе
  5. Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4
  1. Создаём табличное выражение Aeroflot_trips , содержащие все полёты, совершенные авиакомпанией «Aeroflot»
MySQL
WITH 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
  1. Аналогично, создаём табличное выражение Aeroflot_trips , но с переименованными колонками
MySQL
WITH 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
  1. С помощью оператора WITH определяем несколько табличных выражений
MySQL
WITH 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

Общие табличные выражения (CTE) стр. 1

Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.

Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована.

Задачу можно решить, например, следующим способом.

Консоль

Выполнить

Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.

Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:

Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:

Консоль

Выполнить

Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),

Консоль

Выполнить

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

Страницы: 1 2 3

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *