сте sql что это
Общие табличные выражения (CTE) стр. 1
Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.
Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных «Вторсырье», а также тип операции, дату и пункт приема, когда и где она была зафиксирована.
Задачу можно решить, например, следующим способом.
Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.
Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:
Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:
Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),
Общие табличные выражения позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же производным таблицам.
Конструкция 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.
WITH common_table_expression (Transact-SQL)
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.
Transact-SQL Syntax Conventions
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
expression_name
Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.
column_name
Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
CTE_query_definition
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL).
If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.
Remarks
Guidelines for Creating and Using Common Table Expressions
The following guidelines apply to nonrecursive common table expressions. For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.
A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
The following clauses cannot be used in the CTE_query_definition:
ORDER BY (except when a TOP clause is specified)
OPTION clause with query hints
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
A query referencing a CTE can be used to define a cursor.
Tables on remote servers can be referenced in the CTE.
When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.
Guidelines for Defining and Using Recursive Common Table Expressions
The following guidelines apply to defining a recursive common table expression:
The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
The number of columns in the anchor and recursive members must be the same.
The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
The FROM clause of a recursive member must refer only one time to the CTE expression_name.
The following items are not allowed in the CTE_query_definition of a recursive member:
PIVOT (When the database compatibility level is 110 or higher. See Breaking Changes to Database Engine Features in SQL Server 2016.)
A hint applied to a recursive reference to a CTE inside a CTE_query_definition.
The following guidelines apply to using a recursive common table expression:
All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.
A view that contains a recursive common table expression cannot be used to update data.
Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.
Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. This is one way to confirm proper recursion.
Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. For more information, see example K. Using analytical functions in a recursive CTE that follows.
Features and Limitations of Common Table Expressions in Azure Synapse Analytics and Analytics Platform System (PDW)
The current implementation of CTEs in Azure Synapse Analytics and Analytics Platform System (PDW) have the following features and limitations:
A CTE can be specified in a SELECT statement.
A CTE can be specified in a CREATE VIEW statement.
A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.
A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.
A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.
A remote table can be referenced from a CTE.
An external table can be referenced from a CTE.
Multiple CTE query definitions can be defined in a CTE.
A common table expression that includes references to itself (a recursive common table expression) is not supported.
Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
Examples
A. Creating a simple common table expression
The following example shows the total number of sales orders per year for each sales representative at Adventure Works Cycles.
B. Using a common table expression to limit counts and report averages
The following example shows the average number of sales orders for all years for the sales representatives.
C. Using multiple CTE definitions in a single query
The following example shows how to define more than one CTE in a single query. Notice that a comma is used to separate the CTE query definitions. The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.
Here is a partial result set.
D. Using a recursive common table expression to display multiple levels of recursion
The following example shows the hierarchical list of managers and the employees who report to them. The example begins by creating and populating the dbo.MyEmployees table.
Using a recursive common table expression to display two levels of recursion
The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.
Using a recursive common table expression to display a hierarchical list
The following example adds the names of the manager and employees, and their respective titles. The hierarchy of managers and employees is additionally emphasized by indenting each level.
Using MAXRECURSION to cancel a statement
MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.
After the coding error is corrected, MAXRECURSION is no longer required. The following example shows the corrected code.
E. Using a common table expression to selectively step through a recursive relationship in a SELECT statement
F. Using a recursive CTE in an UPDATE statement
The following example updates the PerAssemblyQty value for all parts that are used to build the product ‘Road-550-W Yellow, 44’ (ProductAssemblyID«800 ). The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Only the rows returned by the common table expression are modified.
H. Using multiple anchor and recursive members
The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. A table is created and values inserted to establish the family genealogy returned by the recursive CTE.
I. Using analytical functions in a recursive CTE
The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.
The following results are the expected results for the query.
The following results are the actual results for the query.
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
J. Using a common table expression within a CTAS statement
The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works Cycles.
K. Using a common table expression within a CETAS statement
The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works Cycles.
L. Using multiple comma separated CTEs in a statement
The following example demonstrates including two CTEs in a single statement. The CTEs cannot be nested (no recursion).