DML. Common table expression. WITH
Дорогу осилит идущийЕсли вы практиковались в написании запросов с использованием подзапросов, могли заметить две вещи:
- Иногда необходимо вызвать один и тот же подзапрос (или схожие, но с разными колонками в SELECT-блоке) несколько раз в рамках глобального запроса;
- Запрос с подзапросами зачастую выглядят громоздко и тяжелочитаемы. Особенно если возникает необходимость вызвать подзапрос внутри другого подзапроса.
CTE
Чтобы сделать запрос более простым для восприятия (а в ряде случаев - и увеличить его производительность) в SQL существует функциональность под названием CTE - Common table expression. С ней мы и познакомимся в рамках данной статьи.
CTE - запрос, точнее, результат запроса, который можно использовать в рамках других запросов.
В чем-то это напоминает концепцию переменной - мы записываем в нее какое-то значение (в нашем случае - таблицу-результат запроса), которую можем использовать в дальнейшем - в основном запросе, его подзапросах (если они не были вынесены в CTE) или в других “переменных”.
Строго говоря, CTE не позволяет полностью избежать подзапросов, но значительно их упрощает, что особенно ценно, когда подзапросы включают в себя другие подзапросы - общая структура становится более линейной.
WITH
Для объявления и заполнения таких “переменных” SQL предоставляет оператор WITH, который располагается до основного оператора запроса (SELECT, INSERT, DELETE или UPDATE), в рамках которого эти самые “переменные” и будут использованы.
Еще один плюс заключается в том, что данные запросы будут выполнены до выполнения основного запроса, что упрощает восприятие - вместо необходимости держать в голове несколько подзапросов в разных частях основного запроса, мы четко понимаем, что у нас доступен ряд значений (таблиц), которые мы можем использовать в рамках основного запроса.
Разберемся на примере. В из прошлых уроков у нас был следующий запрос:
select * from passenger
where id in (
select id from passenger
where male and id in (
select id from passenger where birth_date < '2000-01-01'
)
);
С помощью WITH мы можем его упростить до следующего (заодно сразу рассмотрим возможность использования нескольких WITH):
with old_passenger_id as ( select id from passenger where birth_date < '2000-01-01' ), old_male_passenger_id as ( select id from passenger where male and id in (select id from old_passenger_id) ) select * from old_male_passenger_id;
В данном случае мы объявляем old_passenger_id, используя эдакий “алиас наоборот” (сначала задаем имя, а потом - описание). Назовем это временной таблицей, хоть это и не совсем так (временная таблица - это самостоятельный термин, который мы не затрагиваем в рамках курса, но вы можете познакомиться с ним самостоятельно).
Эта временная таблица будет содержать результат описанного запроса, который указан в скобках:
select id from passenger where birth_date < '2000-01-01'
На выходе получим таблицу с id пассажиров, которые родились после 1 января 2000 года.
Через запятую мы можем указывать несколько “переменных”, чем и воспользуемся. Следующая временная таблица - old_male_passenger_id, содержащая id пассажиров-мужчин, которые старше 1 января 2000 года.
И, наконец, опишем основной запрос, который стал гораздо проще - получение всех пассажиров из old_male_passenger_id.
В данном случае целью было продемонстрировать то, что с результатами запросов из секции WITH можно работать также, как и с обычными таблицами. При желании, из них можно делать выборки, использовать в WHERE, подзапросах, JOIN и т.д.
Сам же запрос можно упростить (не говоря о том, что его можно описать без подзапросов вообще):
with old_passenger_id as ( select id from passenger where birth_date < '2000-01-01' ) select * from passenger where male and id in (select id from old_passenger_id);
Резюмируем основные моменты:
- WITH позволяет создавать предвычисленные (до выполнения основного запроса) таблицы, которые можно использовать в рамках других предвычисленных таблиц или в основном запросе;
- Такие предвычисленные таблицы живут в рамках одного глобального запроса;
- Синтаксически такие таблицы описываются как алиас AS (*запрос для формирования*);
- один запрос может иметь несколько временных таблиц;
- Использование WITH может упростить чтение запроса в дальнейшем.
Строго говоря, в CTE можно помещать не только SELECT-запросы, а, например, и INSERT-запросы (или другие в рамках DML), если нам важно возвращаемое значение такого запроса. Например, если наш неSELECT-запрос содержит предложение RETURNING, которое мы не рассматриваем в рамках курса.
Recursive WITH
Функциональность WITH, разобранная выше, полезна. Но ее, с большего, можно рассматривать как синтаксический сахар над подзапросами.
Но использование CTE может дать больше, чем просто сахар, за счет рекурсивных запросов, расположенных в WITH-блоке.
Предлагаю ознакомиться со следующей статьей. Она достаточно проста и понятна на данном уровне владения SQL: ссылка.
Также рекомендую обратить внимание на этот комментарий.
Выделим ключевые моменты:
- WITH позволяет создавать временные таблицы, наполняемые рекурсивно;
- Для простоты восприятия процесс наполнения таких таблиц можно представить итеративно (как цикл while, например);
- Данные таблицы формируются посредством ссылки временной таблицы на саму себя;
- Каждый запрос в рамках WITH RECURSIVE состоит из нерекурсивной части, выполняемой единожды, и присоединяемой через UNION рекурсивной части, выполняемой до тех пор, пока следующий вызов возвращает что-либо. Ограничение числа вызовов рекурсивной части происходит посредством ограничения в WHERE-блоке;
- Ценность такого подхода заключается в возможности линейно представить иерархические структуры (линейные, древовидные). Интуитивно понятный пример подобной иерархической (древовидной) структуры - таблица членов семьи в рамках генеалогического древа;
- Рекурсивный WITH имеет ряд ограничений. Отчасти, их можно обойти (см. ссылку на комментарий выше);
- Использование такого подхода может как улучшить, так и ощутимо ухудшить производительность запроса в целом - в каждом случае стоит рассматривать наиболее выгодный подход к формированию конечной выборки. Некоторые варианты можно почерпнуть из других комментариев к той же статье (осторожно, могут встретиться незнакомые слова).
В качестве заключения
CTE предоставляет достаточно удобный механизм, который позволяет упростить написание и чтение сложных запросов с большой вложенностью подзапросов. От себя рекомендую использовать WITH везде, где это возможно. К сожалению, не любой запрос можно представить в подобном виде без потери производительности (например, подзапросы, содержащие в WHERE-блоке условия, связанные с внешним запросом), поэтому совсем механически к этому вопросу подойти не получится.
Кроме того, различные СУБД (в т.ч. PostgreSQL) имеют разного рода дополнительные возможности и особенности использования в рамках WITH. В рамках PostgreSQL (и уже разобранных возможностей) к таким можно отнести предложение (модификатор) RECURSIVE для WITH (в целом, рекурсия в запросах может быть реализована по-разному в разных СУБД, в т.ч. в контексте синтаксиса) и использование неSELECT-запросов в WITH.
С теорией на сегодня все!
Переходим к практике:
Задача 1
Найдите наиболее популярный аэропорт прибытия для каждой из фамилий пассажиров. Необходимые атрибуты пассажиров предварительно вынесите в CTE.
Задача 2
Посчитайте число билетов на рейсы до августа 2023 года, сгруппировав их по аэропорту прибытия. Отобразите для каждого совершеннолетнего пассажира число таких билетов для рейсов, направляющихся в первый и второй их любимые аэропорты (для каждого аэропорта, а также суммарное значение этих показателей).
Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)
Канал: https://t.me/ViamSupervadetVadens
Мой тг: https://t.me/ironicMotherfucker
Дорогу осилит идущий!