Что такое cte в sql
Общие табличные выражения (CTE) стр. 1
Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.
Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных «Вторсырье», а также тип операции, дату и пункт приема, когда и где она была зафиксирована.
Задачу можно решить, например, следующим способом.
Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.
Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:
Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:
Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),
Общие табличные выражения позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же производным таблицам.
Табличные выражения
называются подзапросы, которые используются там, где ожидается наличие таблицы. Существует два типа табличных выражений:
обобщенные табличные выражения.
Эти две формы табличных выражений рассматриваются в следующих подразделах.
Производные таблицы
— это табличное выражение, входящее в предложение FROM запроса. Производные таблицы можно применять в тех случаях, когда использование псевдонимов столбцов не представляется возможным, поскольку транслятор SQL обрабатывает другое предложение до того, как псевдоним станет известным. В примере ниже показана попытка использовать псевдоним столбца в ситуации, когда другое предложение обрабатывается до того, как станет известным псевдоним:
Попытка выполнить этот запрос выдаст следующее сообщение об ошибке:
Причиной ошибки является то обстоятельство, что предложение GROUP BY обрабатывается до обработки соответствующего списка инструкции SELECT, и при обработке этой группы псевдоним столбца enter_month неизвестен.
Эту проблему можно решить, используя производную таблицу, содержащую предшествующий запрос (без предложения GROUP BY), поскольку предложение FROM исполняется перед предложением GROUP BY:
Результат выполнения этого запроса будет таким:
Обычно табличное выражение можно разместить в любом месте инструкции SELECT, где может появиться имя таблицы. (Результатом табличного выражения всегда является таблица или, в особых случаях, выражение.) В примере ниже показывается использование табличного выражения в списке выбора инструкции SELECT:
Результат выполнения этого запроса:
Обобщенные табличные выражения
называется именованное табличное выражение, поддерживаемое языком Transact-SQL. Обобщенные табличные выражения используются в следующих двух типах запросов:
Эти два типа запросов рассматриваются в следующих далее разделах.
OTB и нерекурсивные запросы
Нерекурсивную форму OTB можно использовать в качестве альтернативы производным таблицам и представлениям. Обычно OTB определяется посредством предложения WITH и дополнительного запроса, который ссылается на имя, используемое в предложении WITH. В языке Transact-SQL значение ключевого слова WITH неоднозначно. Чтобы избежать неопределенности, инструкцию, предшествующую оператору WITH, следует завершать точкой с запятой.
далее показано использование OTB в нерекурсивных запросах. В примере ниже используется стандартное решение (здесь используется тестовая база данных AdventureWorks2012 из исходников):
Запрос в этом примере выбирает заказы, чьи общие суммы налогов (TotalDue) большие, чем среднее значение по всем налогам, и плата за перевозку (Freight) которых больше чем 40% среднего значения налогов. Основным свойством этого запроса является его объемистость, поскольку вложенный запрос требуется писать дважды. Одним из возможных способов уменьшить объем конструкции запроса будет создать представление, содержащее вложенный запрос. Но это решение несколько сложно, поскольку требует создания представления, а потом его удаления после окончания выполнения запроса. Лучшим подходом будет создать OTB. В примере ниже показывается использование нерекурсивного OTB, которое сокращает определение запроса, приведенного выше:
Синтаксис предложения WITH в нерекурсивных запросах имеет следующий вид:
OTB и рекурсивные запросы
В этом разделе представляется материал повышенной сложности. Поэтому при первом его чтении рекомендуется его пропустить и вернуться к нему позже. Посредством OTB можно реализовывать рекурсии, поскольку OTB могут содержать ссылки на самих себя. Основной синтаксис OTB для рекурсивного запроса выглядит таким образом:
Параметры cte_name и column_list имеют такое же значение, как и в OTB для нерекурсивных запросов. Тело предложения WITH состоит из двух запросов, объединенных оператором UNION ALL. Первый запрос вызывается только один раз, и он начинает накапливать результат рекурсии. Первый операнд оператора UNION ALL не ссылается на OTB. Этот запрос называется опорным запросом или источником.
Второй запрос содержит ссылку на OTB и представляет ее рекурсивную часть. Вследствие этого он называется рекурсивным членом. В первом вызове рекурсивной части ссылка на OTB представляет результат опорного запроса. Рекурсивный член использует результат первого вызова запроса. После этого система снова вызывает рекурсивную часть. Вызов рекурсивного члена прекращается, когда предыдущий его вызов возвращает пустой результирующий набор.
Оператор UNION ALL соединяет накопившиеся на данный момент строки, а также дополнительные строки, добавленные текущим вызовом рекурсивного члена. (Наличие оператора UNION ALL означает, что повторяющиеся строки не будут удалены из результата.)
Наконец, параметр outer_query определяет внешний запрос, который использует OTB для получения всех вызовов объединения обеих членов.
Для демонстрации рекурсивной формы OTB мы используем таблицу Airplane, определенную и заполненную кодом, показанным в примере ниже:
Таблица Airplane состоит из следующих 11 строк:
В примере ниже показано применение предложения WITH для определения запроса, который вычисляет общую стоимость каждой сборки:
Предложение WITH определяет список OTB с именем list_of_parts, состоящий из трех столбцов: assembly1, quantity и cost. Первая инструкция SELECT в примере вызывается только один раз, чтобы сохранить результаты первого шага процесса рекурсии. Инструкция SELECT в последней строке примера отображает следующий результат:
Запрос в примере ниже вычисляет стоимость каждой сборки со всеми ее составляющими:
Результат выполнения этого запроса дает следующий результат:
В рекурсивных запросах на OTB налагается несколько следующих ограничений:
определение OTB должно содержать, по крайней мере, две инструкции SELECT (опорный член и рекурсивный член), объединенные оператором UNION ALL;
опорный член и рекурсивный член должны иметь одинаковое количество столбцов (это является прямым следствием использования оператора UNION ALL);
столбцы в рекурсивном члене должны иметь такой же тип данных, как и соответствующие столбцы в опорном члене;
предложение FROM рекурсивного члена должно ссылаться на имя OTB только один раз;
определение рекурсивного члена не может содержать следующие параметры: SELECT DISTINCT, GROUP BY, HAVING, агрегатные функции, TOP и подзапросы. Кроме этого, единственным типом операции соединения, разрешенной в определении запроса, является внутреннее соединение.
Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ)
Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.
Для начала, конечно же, давайте поговорим о том, что вообще из себя представляют обобщенные табличные выражения, какие они бывают, рассмотрим синтаксис, для чего их можно использовать и в заключение разберем несколько примеров.
Что такое обобщенное табличное выражение?
Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.
Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:
Обобщенное табличное выражение определяется с помощью конструкции WITH, и определить его можно как в обычных запросах, так и в функциях, хранимых процедурах, триггерах и представлениях.
Синтаксис:
После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос SELECT, INSERT, UPDATE, MERGE или DELETE.
Какие бывают обобщенные табличные выражения?
Они бывают простые и рекурсивные.
Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.
Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).
Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.
В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.
Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.
Пример простого обобщенного табличного выражения
Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения
Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.
В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:
Пример рекурсивного обобщенного табличного выражения
Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:
В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1
При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.
Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Применение оконных функций и CTE в MySQL 8.0 для реализации накопительного итога без хаков
Прим. перев.: в этой статье тимлид британской компании Ticketsolve делится решением своей весьма специфичной проблемы, демонстрируя при этом общие подходы к созданию так называемых accumulating (накопительных) функций с помощью современных возможностей MySQL 8.0. Его листинги наглядны и снабжены подробными объяснениями, что помогает вникнуть в суть проблематики даже тем, кто не погружался в неё столь глубоко.
Этот паттерн плохо работает с оптимизатором (приводя к недетерминированному поведению), поэтому от него решили отказаться. В результате возникла некая пустота, поскольку (относительно) комплексную логику теперь сложнее реализовать (по крайней мере, с той же простотой).
В статье пойдет речь о двух способах ее реализации: с использованием оконных функций (канонический подход) и с помощью рекурсивных СТЕ (общих табличных выражений).
Требования и предыстория
Хотя СТЕ достаточно интуитивно понятны, тем, кто не очень хорошо знаком с ними, я рекомендую обратиться к моей предыдущей публикации на эту тему.
То же самое справедливо и для оконных функций: я буду подробно комментировать запросы/концепции, но общее представление все же не помешает. Оконным функциям посвящено огромное количество книг и публикаций (именно поэтому я до сих пор не писал о них); при этом в большинстве примеров вычисления проводятся либо на финансовых результатах, либо на демографических показателях. Однако в данной статье я буду использовать реальный случай.
В мире ПО существует известная архитектурная дилемма: реализовывать логику на уровне приложения или на уровне базы данных? Хотя это вполне уместный вопрос, в нашем случае я исхожу из предположения, что логика должна остаться на уровне базы; причиной для этого могут быть, например, требования к скорости (как и было в нашем случае).
Задача
В этой задаче мы распределяем места в некоем зале (театральном).
Для целей бизнеса требуется каждому месту присваивать так называемую «группировку» (grouping) — дополнительный номер, представляющий его.
Вот алгоритм определения значения группировки:
В реальной жизни нам нужно, чтобы конфигурация слева давала значения, приведенные справа:
Подготовка
Пусть базовая таблица имеет следующее минималистское строение:
Основываясь на диаграмме выше, координаты каждого места имеют вид (y, x):
Следует загрузить достаточно большое количество записей, чтобы помешать оптимизатору «найти» неожиданные короткие пути. Конечно, мы используем рекурсивные СТЕ:
Старый подход
Старый добрый подход весьма прямолинеен и незамысловат:
Что ж, это было легко (но не забывайте о предупреждениях)!
Небольшое отступление: в данном случае я пользуюсь свойствами булевой арифметики. Следующие выражения эквивалентны:
Некоторые находят это интуитивно понятным, другие — нет; тут дело вкуса. Дальше я буду использовать более компактное выражение.
Давайте посмотрим на результат:
Увы, у него есть «незначительный» недостаток: он прекрасно работает за исключением тех случаев, когда не работает…
Все дело в том, что оптимизатор запросов вовсе не обязательно проводит вычисления слева направо, поэтому операции присваивания (:=) могут выполняться в неверном порядке, приводя к неправильному результату. С подобной проблемой люди часто сталкиваются после обновления MySQL.
В MySQL 8.0 этот функционал действительно признан устаревшим:
Что ж, давайте исправим ситуацию!
Современный подход №1: оконные функции
Появление оконных функций было весьма долгожданным событием в мире MySQL.
Вообще говоря, «скользящая» природа оконных функций отлично сочетается с накопительными функциями. Однако некоторые сложные накопительные функции требуют наличия результатов последнего выражения — функциональность, которую оконные функции не поддерживают, поскольку работают на столбцах.
Это не означает, что проблему нельзя решить: просто ее необходимо переосмыслить.
В нашем случае можно разделить задачу на две части. Группировку для каждого места можно считать как сумму двух значений:
Порядковый номер каждого места — это встроенная функция:
А вот с совокупным значением все гораздо интереснее… Чтобы его вычислить, мы выполняем два действия:
(Обратите внимание, что с настоящего момента я опускаю UPDATE ради простоты).
Давайте проанализируем запрос.
Высокоуровневая логика
Следующее CTE (отредактировано):
… вычисляет приращения для каждого места по сравнению с предыдущим (подробнее о LAG() — позже). Он работает на каждой записи и той, которая ей предшествует, и не является кумулятивным.
Теперь, чтобы подсчитать кумулятивные приращения, мы просто воспользуемся оконной функцией для вычисления суммы до каждого места и включая его:
Оконная функция LAG()
Функция LAG в своей простейшей форме ( LAG(x) ) возвращает предыдущее значение заданного столбца. Классическое неудобство с такими функциями — обработка первой(-ых) записи(-ей) в окне. Поскольку предыдущей записи нет, они возвращают NULL. В случае LAG можно указать нужное значение как третий параметр:
Указывая значения по умолчанию, мы гарантируем, что к самому первому месту в границах окна будет применяться та же логика, что и для места, следующего за другим (х-1) и без смены ряда (у).
Второй параметр в LAG() — это число позиций, на которые надо сдвигаться назад в рамках окна; 1 — это предыдущее значение (оно также установлено по умолчанию).
Технические аспекты
Именованные окна
В нашем запросе много раз используется одно и то же окно. Следующие два запроса формально эквивалентны:
Однако второй может повлечь неоптимальное поведение (с чем я сталкивался — по крайней мере, в прошлом): оптимизатор может посчитать окна независимыми и отдельно высчитывать каждое. По этой причине я советую всегда использовать именованные окна (по крайней мере, когда они повторяются).
Оператор PARTITION BY
Обычно оконные функции выполняются на партиции. В нашем случае это будет выглядеть следующим образом:
Поскольку окно соответствует полному набору записей (который фильтруется условием WHERE ), нам не нужно указывать ее (партицию).
Сортировка
В запросе ORDER BY задается на уровне окна:
При этом оконная сортировка идет отдельно от SELECT. Это очень важно! Поведение этого запроса:
… не определено. Давайте обратимся к руководству:
Строки результата запроса определяются из выражения FROM после выполнения операторов WHERE, GROUP BY и HAVING, а выполнение в рамках окна происходит до ORDER BY, LIMIT и SELECT DISTINCT.
Некоторые соображения
Если говорить в общих чертах, для решения задач подобного типа имеет смысл рассчитывать изменение состояния для каждой записи, а затем их суммировать — вместо того, чтобы представлять каждую запись как функцию предыдущей.
Это решение более сложное, чем функционал, который оно заменяет, но в то же время надежное. Увы, этот подход не всегда возможен или легко реализуем. Именно здесь в игру вступают рекурсивные СТЕ.
Современный подход №2: рекурсивные CTE
Этот подход требует небольших хитростей из-за ограниченных возможностей СТЕ в MySQL. С другой стороны, это универсальное, прямое решение, поэтому оно не требует какого-либо переосмысления глобального подхода.
Давайте начнем с упрощенной версии конечного запроса:
Бинго! Этот запрос (относительно) прост, но, что более важно, он выражает накопительную функцию группировки самым простым возможным образом:
Логика понятна даже для тех, кто не слишком знаком с СТЕ. Первый ряд — это первое место в зале, по порядку:
В рекурсивной части мы проводим итерацию:
Часть s.venue_id в выражении сортировки очень важна! Она позволяет нам использовать индекс.
JOIN формально является перекрестным, однако из-за оператора LIMIT возвращается только одна запись.
Рабочая версия
К сожалению, приведенный выше запрос не работает, поскольку ORDER BY в настоящее время не поддерживается в рекурсивных подзапросах. Кроме того, семантика LIMIT в том виде, в котором он используется здесь, отличается от типичной, которая применяется к внешнему запросу:
LIMIT теперь поддерживается [..] Воздействие на итоговый набор данных такое же, как при использовании LIMIT с внешним SELECT
Впрочем, это не такая уж серьезная проблема. Давайте взглянем на работающую версию:
Немного некомфортно использовать подзапрос, но данный подход работает и boilerplate здесь минимален, так как в любом случае требуется несколько выражений.
Размышления о производительности
Давайте изучим план выполнения запроса с помощью EXPLAIN ANALYZE:
План вполне соответствует ожиданиям. В данном случае основа оптимального плана кроется в индексных поисках:
… имеющих первостепенное значение. Скорость работы значительно упадет, если производить сканирование индексов (то есть линейно сканировать записи индекса вместо того, чтобы искать сразу нужные).
Таким образом, чтобы эта стратегия работала, необходимо, чтобы связанные индексы были на месте и максимально эффективно использовались оптимизатором.
Если в будущем ограничения будут сняты, то отпадет и необходимость использовать подзапрос, что значительно упростит задачу для оптимизатора.
Альтернатива для неоптимальных планов
В случае, если оптимальный план невозможно определить, используйте временную таблицу:
Даже если в этом запросе проходят индексные сканы, они обходятся «малой кровью», поскольку таблица selected_seats очень мала.
Заключение
Я очень доволен тем, что эффективный, но в то же время имеющий определенные недостатки рабочий процесс теперь можно заменить достаточно простым функционалом, появившимися в MySQL 8.0.
Тем временем, развитие новых фич для 8.0 продолжается, что делает и без того удачный релиз ещё лучше.
CTE в SQL
Общие табличные выражения (CTE) были введены в стандартный SQL для упрощения различных классов SQL-запросов, для которых производная таблица была просто непригодна. CTE был представлен в SQL Server 2005, общее табличное выражение (CTE) — это временный именованный набор результатов, на который можно ссылаться в операторе SELECT, INSERT, UPDATE или DELETE. Вы также можете использовать CTE в CREATE представления, как часть запроса SELECT представления. Кроме того, начиная с SQL Server 2008, вы можете добавить CTE к новой инструкции MERGE.
Использование CTE —
Мы можем определить CTE, добавив предложение WITH непосредственно перед оператором SELECT, INSERT, UPDATE, DELETE или MERGE. Предложение WITH может включать один или несколько CTE, разделенных запятыми. Можно использовать следующий синтаксис:
После того, как вы определили свое предложение WITH с помощью CTE, вы можете ссылаться на CTE так же, как и на любую другую таблицу. Однако вы можете ссылаться на CTE только в пределах области выполнения оператора, который следует сразу за предложением WITH. После того, как вы запустите оператор, набор результатов CTE будет недоступен для других операторов.
Создание рекурсивного общего табличного выражения —
Рекурсивный CTE — это тот, который ссылается на себя в этом CTE. Рекурсивный CTE полезен при работе с иерархическими данными, поскольку CTE продолжает выполняться до тех пор, пока запрос не вернет всю иерархию.
Типичным примером иерархических данных является таблица, которая включает список сотрудников. Для каждого сотрудника в таблице содержится ссылка на менеджера этого человека. Эта ссылка сама является идентификатором сотрудника в той же таблице. Вы можете использовать рекурсивный CTE для отображения иерархии данных о сотрудниках.
Если CTE создан неправильно, он может войти в бесконечный цикл. Чтобы предотвратить это, подсказка MAXRECURSION может быть добавлена в предложение OPTION основного оператора SELECT, INSERT, UPDATE, DELETE или MERGE.
После создания таблицы Employees создается следующая инструкция SELECT, которой предшествует предложение WITH, включающее CTE с именем cteReports:
Таким образом, CTE могут быть полезным инструментом, когда вам нужно сгенерировать временные наборы результатов, к которым можно обращаться в операторе SELECT, INSERT, UPDATE, DELETE или MERGE.