Введение в общие табличные выражения MySQL
Пересказ статьи Robert Sheldon. Introducing the MySQL common table expression
Как и во многих реляционных системах управления базами данных, MySQL предлагает разнообразные методы комбинирования данных в операторах языка манипуляции данными (DML). Вы можете соединять несколько таблиц в одном запросе или добавлять подзапросы, которые извлекают данные из других таблиц. Вы можете также обращаться к представлениям и временным таблицам из оператора наряду с постоянными таблицами.
MySQL предлагает также другой ценный инструмент для работы с данными - общие табличные выражения (CTE). CTE - это именованный результирующий набор, который вы определяете в предложении WITH. Предложение WITH связано с единственным оператором DML, но создается вне этого оператора. Однако только этот оператор может иметь доступ к результирующему набору.
В некоторых случаях вы можете включить CTE в оператор SELECT, который встроен в другой оператор, как в случае подзапроса или оператора DELETE…SELECT. Но даже тогда предложение WITH определяется вне этого оператора SELECT, и только этот оператор SELECT может иметь доступ к результирующему набору.
Можно думать о CTE как о неком представлении с очень ограниченной (одним оператором) областью видимости. Можно также представлять себе CTE как именованный подзапрос, который определен в предложении, отдельном от главного запроса. Однако CTE не является ни тем, ни другим, и в этой статье я объясняю, как работает CTE, и представлю ряд примеров, которые демонстрируют различные способы, которые вы можете использовать для получения данных.
Замечание: примеры используют таблицы и данные, которые создаются в последнем разделе статьи "Приложение: подготовка демонстрационных объектов и данных".
Общее табличное выражение определяется внутри предложения WITH. Это предложение предшествует основному оператору DML, который иногда называют оператором верхнего уровня. Кроме того, предложение может содержать одно или более определений CTE, как показано в следующем синтаксисе:
Если предложение WITH содержит более одного CTE, вы должны разделять их запятыми и присвоить уникальное имя каждому CTE, хотя это применимо только в пределах предложения WITH. Например, два оператора SELECT могут включать CTE с одним и тем же именем, поскольку область видимости CTE ограничена связанным с ним оператором верхнего уровня.
За именем CTE следует одно или более необязательных имен столбцов, затем ключевое слово AS и, наконец, запрос SELECT в круглых скобках. Если вы указываете имена столбцов, их число должно соответствовать числу столбцов, возвращаемых запросом SELECT. Если имена столбцов не указываются, будут использоваться имена столбцов, возвращаемых запросом SELECT.
Общие табличные выражения обычно используются с операторами SELECT. Однако вы можете также использовать их с операторами UPDATE и DELETE, следуя вышеприведенному синтаксису. Кроме того, вы можете включать CTE с подзапросами при передаче их во внешние операторы. Вы можете также использовать CTE в операторах, которые поддерживают использование SELECT как части определения оператора. Например, вы можете добавить предложение WITH в оператор SELECT в операторе INSERT…SELECT или в операторе CREATE TABLE…SELECT.
В этой статье я сосредоточусь, главным образом, на создании CTE, которые используют оператор SELECT на верхнем уровне, поскольку это наиболее частое использование CTE. Такой подход является также хорошим способом начать изучение CTE, не рискуя данными. Затем вы можете применить изученные фундаментальные принципы к другим типам операторов, когда освоитесь с CTE.
Имея это в виду, давайте начнем с простого примера. Следующий оператор SELECT включает предложение WITH, которое определяет CTE с именем planes:
Запрос SELECT в CTE извлекает все самолеты из таблицы airplanes, для которых типом мотора (engine_type) является jet. Результирующий набор CTE составляют данные, возвращаемые запросом SELECT, они доступны для оператора SELECT верхнего уровня.
Оператор верхнего уровня извлекает данные непосредственно из CTE подобно тому, как оператор мог бы извлекать данные из представления. Основное различие состоит в том, что определение представления постоянно находится в базе данных и может использоваться всеми, кто имеет соответствующие привилегии. С другой стороны, CTE имеет очень ограниченную область видимости и доступно только в пределах оператора верхнего уровня.
В этом случае оператор SELECT верхнего уровня извлекает из CTE только столбцы plane и max_weight и упорядочивает результаты по столбцу max_weight в порядке убывания. На следующем рисунке показаны результаты, возвращаемые оператором.
Конечно, вы можете легко достичь тех же результатов без использования CTE, непосредственно выполнив запрос к таблице airplanes:
Однако я хотел продемонстрировать основные компоненты работы с CTE и то, как вы можете получить доступ к CTE из оператора SELECT верхнего уровня. Как CTE, так и оператор верхнего уровня, несомненно могут быть значительно более сложными - обычно так и есть - но принцип остается тем же.
Как упомянуто ранее, CTE — это, по сути, именованный результирующий набор. Когда вы обращаетесь к CTE из оператора верхнего уровня, данные возвращаются в табличном формате, подобно обращению к представлению, постоянной таблице, временной таблице или производной таблице (например, такой, которая производится подзапросом в предложении FROM оператора SELECT). Это означает, что вы можете работать с CTE в той же манере, как и с упомянутыми объектами других типов. Например, одним из обычных подходов к обращению с CTE в запросе верхнего уровня является соединение его с другой таблицей, как показано в следующем примере:
Предложение WITH определяет единственное CTE с именем mfcs. Запрос SELECT в CTE возвращает значения manufacturer_id и manufacturer из таблицы manufacturers. Оператор SELECT верхнего уровня соединяет затем таблицу airplanes с CTE mfcs на основе столбца manufacturer_id. На следующем рисунке показаны результаты выполнения оператора:
Как показывает этот пример, вы можете считать CTE подобным любой другой табличной структуре в вашем запросе верхнего уровня. Однако, как вы уже видели ранее, вы можете также переписать этот оператор без CTE, соединив таблицу airplanes непосредственно с таблицей manufacturers:
Поскольку здесь совсем немного данных, разница в производительности этих двух операторов незначительна, но это может быть не так для больших наборов данных. Однако, как это часто бывает с MySQL, трудно сказать, какой из подходов является лучшим, пока не выполнишь оба оператора на реальных данных. Даже в этом случае может не быть существенной разницы в производительности, тогда все может зависеть от предпочтений разработчика.
Как отмечалось ранее, MySQL часто поддерживает множество способов получения одних и тех же результатов, что демонстрировали предыдущие примеры. Общие табличные выражения иногда могут помочь упростить код и сделать его более читабельным, что является важным аргументом в их пользу, но производительность, как правило, должна быть главным соображением.
Сравнение различных подходов обычно требует тестирования на соответствующем наборе данных, в частности, потому что может быть сложно найти конкретные рекомендации при сравнении подходов. Например, мало кто из разработчиков смог бы утверждать, что вы всегда должны использовать CTE, а не внутренние соединения, при любых обстоятельствах, или наоборот.
Тем не менее, вы можете встретить менее жесткие рекомендации, которые, возможно, стоит рассмотреть, скажем, при сравнении CTE с подзапросами. Например, CTE часто считается лучшим выбором, если оператор SELECT включает несколько подзапросов, требующих одни и те же данные, как в следующем примере:
Если этот оператор кажется вам знакомым, это потому, что я взял его из моей предыдущей статьи, посвященной подзапросам. Как видно, оператор включает три одинаковых подзапроса, что может привести к избыточной обработке, в зависимости от того, как ядро базы данных предпочтет обрабатывать запрос. На следующем рисунке показаны результаты, возвращаемые оператором.
Вместо использования подзапросов вы можете достичь тех же результатов, определив CTE, которое получает среднее значение max_weight для каждого производителя. Затем в запросе верхнего уровня вы можете соединить таблицу airplanes с CTE по ИД производителя, как показано в следующем примере:
В этом случае CTE задает имена столбцов для их использования в результирующем наборе, поэтому значения manufacturer_id возвращаются как столбец id. Кроме того, CTE группирует данные в таблице manufacturers по значениям manufacturer_id и подсчитывает для каждого из них среднее значение avg_weight.
Затем запрос верхнего уровня соединяет таблицу airplanes с CTE и ограничивает результаты только теми самолетами, у которых значение max_weight больше среднего веса, возвращаемого CTE. Обратите внимание, что вместо подзапросов оператор теперь использует столбец avg_weight из CTE.
Повторю, что разница в производительности этих двух подходов незначительна, поскольку мы работаем с небольшим набором данных. Только выполнение этих операторов на более реалистичных данных может дать вам более правдивую картину разницы в производительности. Однако на мой вкус CTE делает код более читабельным, т.е. легче отслеживать логику оператора.
До сих пор в примерах этой статьи было только по одному CTE на предложение WITH, но вы можете определить несколько CTE и ссылаться на любое из них в операторе верхнего уровня. Просто следите, чтобы имена CTE различались и разделялись запятыми. Например, следующее предложение WITH определяет три CTE, на каждое из которых ссылается оператор SELECT верхнего уровня:
Три CTE похожи, они отличаются лишь тем, что выбирают данные на основе разных значений engine_type. Запрос SELECT верхнего уровня использует затем оператор UNION для их объединения. (Оператор UNION ALL собирает результаты нескольких операторов SELECT в единый результирующий набор.) Следующий рисунок показывает фрагмент результатов, возвращаемых этим оператором.
Это довольно простой пример, но он демонстрирует принцип определения нескольких CTE и ссылки на них в запросе верхнего уровня. В этом случае три CTE работают независимо друг от друга, но вы не всегда должны принимать этот подход. Например, предложение WITH в следующем примере также включает три CTE, но теперь второе CTE (mfc_avg) ссылается на первое CTE (mfcs), в то время как третье CTE (pl_avg) остается автономным:
Как показывает данный пример, CTE может ссылаться на CTE, который находится до него. Однако это работает только в одном направлении; CTE не может ссылаться на CTE, который идет после него. В данном случае CTE mfc_avg соединяет таблицу airplanes с CTE mfcs и группирует данные по значению manufacturer_id. Затем запрос верхнего уровня извлекает данные из этого CTE, но возвращает только те строки, для которых значение avg_parking больше среднего, возвращаемого CTE pl_avg. На следующей картинке показаны результаты, возвращаемые этим оператором.
Стоит обратить внимание на то, что предложение WHERE запроса верхнего уровня включает подзапрос, который извлекает данные из CTE pl_avg. Это говорит не только о гибкости, присущей CTE, но и том, что CTE и подапросы не исключают друг друга.
Одним из наиболее полезных аспектов CTE является возможность выполнять рекурсивные запросы. Этот тип CTE - известный как рекурсивный CTE - ссылается на самого себя в пределах запроса CTE. Предложение WITH в рекурсивном CTE должно включать ключевое слово RECURSIVE, а запрос CTE должен включать две части, которые разделяются оператором UNION. Первая (нерекурсивная) часть заполняет начальную строку данными, а вторая (рекурсивная) часть фактически выполняет рекурсию на основе первой строки. Только рекурсивная часть может ссылаться на само CTE.
Чтобы понять, как это работает, рассмотрим следующий пример, который генерирует список четных чисел до 20 включтельно:
CTE называется counter, и оно возвращает только один столбец val. Нерекурсвная часть запроса CTE устанавливает для первой строки значение 2, которое присваивается столбцу val. Рекурсивная часть запроса извлекает данные из CTE, но увеличивает значение столбца val на 2 на каждой итерации. Запрос продолжает инкрементровать столбец на 2, пока значение val меньше 20. Затем оператор SELECT верхнего уровня извлекает данные из CTE, возвращая результаты, показанные на следующем рисунке.
Замечание. Поскольку рекурсивный запрос говорит < 20, вы могли бы решить, что значение 20 не попадет в вывод. Однако итерация, которая возврашает 20, выполняется и останавливает итерационный процесс, поскольку значение перестает быть меньше 20.
При построении рекурсивных CTE имейте в виду, что MySQL накладывает несколько ограничений на рекурсивную часть. Второй оператор SELECT не может содержать агрегатные фукнции, оконные функции, ключевое слово DISTINCT или предложения GROUP BY, или ORDER BY.
Выполнение рекурсивного запроса может быть полезным при работе с иерархическими данными. Для демонстрации их работы я использовал следующий код для создания и заполнения таблицы с именем airline_emps, которая хранит ID и наименования должностей для группы фиктивных сотрудников наряду с ID лица, которому каждый их них подчиняется:
Каждый, за исключением emp_id 1 (главный босс), подчиняется кому-то другому. Например, надзиратель отчитывается перед высшим руководством, которое в свою очередь подчиняется главному боссу. Я добавил в код оператор SELECT, чтобы вы могли проверить данные, после добавления их в таблицу.
Имея эти данные, вы можете теперь создать следующее рекурсивное CTE, которое находит должность человека в компании, и как эта должность вписывается в иерархию подчиненности:
Поскольку это рекурсивное CTE, оно разделено на две части, которые соединяются оператором UNION ALL. Нерекурсивная часть заполняет первую строку на основе NULL-значения в столбце reports_to. Это строка для главного босса, кто находится наверху иерархии. Нерекурсивная часть также присваивает значение 1 столбцу emp_tier и значение emp_id - столбцу emp_path, преобразуя значение к тпу данных CHAR. Первая строка, возвращаемая CTE выглядит подобно показанной на следующем рисунке.
Рекурсивная часть CTE использует внутрее соединение по совпадению значений таблицы airline_emps и CTE emps. Соединение основано на столбце reports_to в таблице airline_emps и столбце emp_id в CTE emps. Условие соединения делает возможным рекурсивный обход каждого уровня иерархии подчиненности на основе значения reports_to. Затем рекурсивная часть инкрементирует столбец emp_tier на 1 на каждом уровне иерархии.
Например, emp_id 2 (начальник отделения) и emp_id 6 (исполнительный директор) оба подчиняются emp_id 1 (главному боссу), поэтому столбец emp_tier для этих двух строк инкрементируется на 1, что дает значение 2 для каждой строки. Это означает, что он оба находятся на втором уровне иерархии сотрудников. Следующий уровень в иерархии включает тех людей, кто подчиняется начальнику отделения или исполнительному директору, поэтому столбец emp_tier для этих строк устанавливается в 3. Этот процесс подолжается до тех пор, пока остаются уровни.
Во время этого процесса также обновляется столбец emp_path для каждой строки, конкатенируя значения emp_id для обеспечения представления иерархии подчиненности. Например, столбец reports_to для emp_id 9 покажет, что организатор подчняется emp_id 8 (руководителю группы), который подчиняется emp_id 6 (исполнительному директору), который подчиняется emp_id 1 (главному боссу). При этом каждый слой отделяется прямым слэшем. Следующий рисунок показывает данные, возвращаемые запросом.
Оператор SELECT верхнего уровня возвращает данные только из CTE без соединения с какой-либо другой таблицей. Оператор также включает предложение ORDER BY, которое сортирует результаты сначала по столбцу emp_tier, а затем по столбцу emp_id.
Ранее в статье я упомянул, что вы можете использовать CTE с другими операторами помимо SELECT. Я также говорил, что сосредоточусь в первую очередь на применении CTE в операторе SELECT. Однако я хочу показать вам, по крайней мере, один из альтернативных вариантов, чтобы вы получили представление, как это может выглядеть (и немного подогреть ваш интерес).
Следующий пример показывает CTE, используемое с оператором UPDATE для модификации данных в таблице airline_emps, созданной в предыдущем разделе:
Предложение WITH и CTE работают так же, как показано в других примерах. Предложение включает единственное CTE с именем rpts, которое извлекает значения emp_id для тех сотрудников, которые подчняются emp_id 8. Запрос возвращает значения 9 и 10.
Оператор UPDATE вехнего уровня использует данные, возвращаемые CTE для обновления столбца reports_to значением 7 для этих двух сотрудников. Предложение WHERE оператора UPDATE включает подзапрос, который извлекает данные из CTE, поэтому оператор будет обновлять только эти две строки.
После выполнения этого оператора обновления вы можете снова выполнить оператор SELECT из предыдущего раздела, чтобы проверить изменения. Я включил сюда этот оператор для удобства:
Оператор возвращает результаты, показанные на рисунке ниже.
Обратите внимание, что сотрудники 9 и 10 теперь имеют значение reports_to = 7. Кроме того, значение emp_path для каждой из двух строк была обновлена, чтобы отразить новую иерархию подчинения.
CTE может быть мощным инструментом при запросах на выборку и модификацию данных в базах данных MySQL. Рекурсивные CTE, в частности, могут оказаться полезными при работе с данными, которые ссылаются сами на себя, как это было продемонстрировано в примерах ранее. Но CTE не всегда интуитивно понятны, и вы должны хорошо понимать как они работают, прежде чем начать добавлять их в код текущей базы данных, особенно если вы хотите использовать их для модификации данных. По этой причине я рекомендую вам также ознакомиться с документацией MySQL на CTE и другими доступными рекурсами. Чем больше времени вы потратите на изучение CTE, нет более эффективно вы будете использовать их в своих запросах.
Для примеров в этой статье я создал базу данных travel и добавил в нее таблицы manufacturers и airplanes. Если вы хотите проверить эти примеры у себя, начните с выполнения следующего скрипта на своем экземпляре MySQL.
После создания базы данных вы можете добавить некоторые данные в таблицу manufacturers, выполнив следующий оператор INSERT:
Я включил после оператора INSERT оператор SELECT, чтобы вы могли проверть, что в таблицу были вставлены 7 строк. Первая строка в таблце имеет значение 101 в столбце manufacturer_id, а последующие строк инкрементируют это значение на единицу. После заполнения таблицы manufacturers вы можете выполнить следующий оператор INSERT для добавления данных в таблицу airplanes:
Оператор INSERT использует значения manufacturer_id из таблицы manufacturers. Эти значения служат значениями внешнего ключа, требуемые для столбца manufacturer_id в таблице airplanes. Кроме того, первой строке присваивается значение 1001 в столбце plane_id, а значения plane_id для других строк инкрементируются соответствующим образом. Как и в предыдущем операторе INSERT, я включил оператор SELECT для контроля вставки данных.
Ссылки по теме
Можно думать о CTE как о неком представлении с очень ограниченной (одним оператором) областью видимости. Можно также представлять себе CTE как именованный подзапрос, который определен в предложении, отдельном от главного запроса. Однако CTE не является ни тем, ни другим, и в этой статье я объясняю, как работает CTE, и представлю ряд примеров, которые демонстрируют различные способы, которые вы можете использовать для получения данных.
Замечание: примеры используют таблицы и данные, которые создаются в последнем разделе статьи "Приложение: подготовка демонстрационных объектов и данных".
Начало работы с общими табличными выражениями
Общее табличное выражение определяется внутри предложения WITH. Это предложение предшествует основному оператору DML, который иногда называют оператором верхнего уровня. Кроме того, предложение может содержать одно или более определений CTE, как показано в следующем синтаксисе:
WITH [RECURSIVE]
имя_cte [(имя_столбца [, имя_столбца] ...)] AS (оператор_select)
[, имя_cte [(имя_столбца [, имя_столбца] ...)] AS (оператор_select)] ...
оператор_верхнего_уровня;Если предложение WITH содержит более одного CTE, вы должны разделять их запятыми и присвоить уникальное имя каждому CTE, хотя это применимо только в пределах предложения WITH. Например, два оператора SELECT могут включать CTE с одним и тем же именем, поскольку область видимости CTE ограничена связанным с ним оператором верхнего уровня.
За именем CTE следует одно или более необязательных имен столбцов, затем ключевое слово AS и, наконец, запрос SELECT в круглых скобках. Если вы указываете имена столбцов, их число должно соответствовать числу столбцов, возвращаемых запросом SELECT. Если имена столбцов не указываются, будут использоваться имена столбцов, возвращаемых запросом SELECT.
Общие табличные выражения обычно используются с операторами SELECT. Однако вы можете также использовать их с операторами UPDATE и DELETE, следуя вышеприведенному синтаксису. Кроме того, вы можете включать CTE с подзапросами при передаче их во внешние операторы. Вы можете также использовать CTE в операторах, которые поддерживают использование SELECT как части определения оператора. Например, вы можете добавить предложение WITH в оператор SELECT в операторе INSERT…SELECT или в операторе CREATE TABLE…SELECT.
В этой статье я сосредоточусь, главным образом, на создании CTE, которые используют оператор SELECT на верхнем уровне, поскольку это наиболее частое использование CTE. Такой подход является также хорошим способом начать изучение CTE, не рискуя данными. Затем вы можете применить изученные фундаментальные принципы к другим типам операторов, когда освоитесь с CTE.
Имея это в виду, давайте начнем с простого примера. Следующий оператор SELECT включает предложение WITH, которое определяет CTE с именем planes:
WITH planes AS
(SELECT plane, engine_count, max_weight
FROM airplanes WHERE engine_type = 'jet')
SELECT plane, max_weight FROM planes
ORDER BY max_weight DESC;Запрос SELECT в CTE извлекает все самолеты из таблицы airplanes, для которых типом мотора (engine_type) является jet. Результирующий набор CTE составляют данные, возвращаемые запросом SELECT, они доступны для оператора SELECT верхнего уровня.
Оператор верхнего уровня извлекает данные непосредственно из CTE подобно тому, как оператор мог бы извлекать данные из представления. Основное различие состоит в том, что определение представления постоянно находится в базе данных и может использоваться всеми, кто имеет соответствующие привилегии. С другой стороны, CTE имеет очень ограниченную область видимости и доступно только в пределах оператора верхнего уровня.
В этом случае оператор SELECT верхнего уровня извлекает из CTE только столбцы plane и max_weight и упорядочивает результаты по столбцу max_weight в порядке убывания. На следующем рисунке показаны результаты, возвращаемые оператором.
Конечно, вы можете легко достичь тех же результатов без использования CTE, непосредственно выполнив запрос к таблице airplanes:
SELECT plane, max_weight
FROM airplanes
WHERE engine_type = 'jet'
ORDER BY max_weight DESC;Однако я хотел продемонстрировать основные компоненты работы с CTE и то, как вы можете получить доступ к CTE из оператора SELECT верхнего уровня. Как CTE, так и оператор верхнего уровня, несомненно могут быть значительно более сложными - обычно так и есть - но принцип остается тем же.
Работа с CTE в операторе SELECT верхнего уровня
Как упомянуто ранее, CTE — это, по сути, именованный результирующий набор. Когда вы обращаетесь к CTE из оператора верхнего уровня, данные возвращаются в табличном формате, подобно обращению к представлению, постоянной таблице, временной таблице или производной таблице (например, такой, которая производится подзапросом в предложении FROM оператора SELECT). Это означает, что вы можете работать с CTE в той же манере, как и с упомянутыми объектами других типов. Например, одним из обычных подходов к обращению с CTE в запросе верхнего уровня является соединение его с другой таблицей, как показано в следующем примере:
WITH mfcs AS
(SELECT manufacturer_id, manufacturer FROM manufacturers)
SELECT a.plane, m.manufacturer, a.max_weight
FROM airplanes a INNER JOIN mfcs m
ON a.manufacturer_id = m.manufacturer_id
WHERE a.engine_type = 'jet'
ORDER BY a.max_weight DESC;Предложение WITH определяет единственное CTE с именем mfcs. Запрос SELECT в CTE возвращает значения manufacturer_id и manufacturer из таблицы manufacturers. Оператор SELECT верхнего уровня соединяет затем таблицу airplanes с CTE mfcs на основе столбца manufacturer_id. На следующем рисунке показаны результаты выполнения оператора:
Как показывает этот пример, вы можете считать CTE подобным любой другой табличной структуре в вашем запросе верхнего уровня. Однако, как вы уже видели ранее, вы можете также переписать этот оператор без CTE, соединив таблицу airplanes непосредственно с таблицей manufacturers:
SELECT a.plane, m.manufacturer, a.max_weight
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE a.engine_type = 'jet'
ORDER BY a.max_weight DESC;Поскольку здесь совсем немного данных, разница в производительности этих двух операторов незначительна, но это может быть не так для больших наборов данных. Однако, как это часто бывает с MySQL, трудно сказать, какой из подходов является лучшим, пока не выполнишь оба оператора на реальных данных. Даже в этом случае может не быть существенной разницы в производительности, тогда все может зависеть от предпочтений разработчика.
Как отмечалось ранее, MySQL часто поддерживает множество способов получения одних и тех же результатов, что демонстрировали предыдущие примеры. Общие табличные выражения иногда могут помочь упростить код и сделать его более читабельным, что является важным аргументом в их пользу, но производительность, как правило, должна быть главным соображением.
Сравнение различных подходов обычно требует тестирования на соответствующем наборе данных, в частности, потому что может быть сложно найти конкретные рекомендации при сравнении подходов. Например, мало кто из разработчиков смог бы утверждать, что вы всегда должны использовать CTE, а не внутренние соединения, при любых обстоятельствах, или наоборот.
Тем не менее, вы можете встретить менее жесткие рекомендации, которые, возможно, стоит рассмотреть, скажем, при сравнении CTE с подзапросами. Например, CTE часто считается лучшим выбором, если оператор SELECT включает несколько подзапросов, требующих одни и те же данные, как в следующем примере:
SELECT manufacturer_id, plane_id, plane, max_weight,
(SELECT ROUND(AVG(max_weight)) FROM airplanes a2
WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_weight,
(max_weight - (SELECT ROUND(AVG(max_weight)) FROM airplanes a2
WHERE a.manufacturer_id = a2.manufacturer_id)) AS amt_over
FROM airplanes a
WHERE max_weight >
(SELECT ROUND(AVG(max_weight)) FROM airplanes a2
WHERE a.manufacturer_id = a2.manufacturer_id); Если этот оператор кажется вам знакомым, это потому, что я взял его из моей предыдущей статьи, посвященной подзапросам. Как видно, оператор включает три одинаковых подзапроса, что может привести к избыточной обработке, в зависимости от того, как ядро базы данных предпочтет обрабатывать запрос. На следующем рисунке показаны результаты, возвращаемые оператором.
Вместо использования подзапросов вы можете достичь тех же результатов, определив CTE, которое получает среднее значение max_weight для каждого производителя. Затем в запросе верхнего уровня вы можете соединить таблицу airplanes с CTE по ИД производителя, как показано в следующем примере:
WITH mfc_weights (id, avg_weight) AS
(SELECT manufacturer_id, ROUND(AVG(max_weight))
FROM airplanes GROUP BY manufacturer_id)
SELECT a.manufacturer_id, a.plane_id, a.plane,
a.max_weight, m.avg_weight,
(a.max_weight - m.avg_weight) AS amt_over
FROM airplanes a INNER JOIN mfc_weights m
ON a.manufacturer_id = m.id
WHERE max_weight > m.avg_weight; В этом случае CTE задает имена столбцов для их использования в результирующем наборе, поэтому значения manufacturer_id возвращаются как столбец id. Кроме того, CTE группирует данные в таблице manufacturers по значениям manufacturer_id и подсчитывает для каждого из них среднее значение avg_weight.
Затем запрос верхнего уровня соединяет таблицу airplanes с CTE и ограничивает результаты только теми самолетами, у которых значение max_weight больше среднего веса, возвращаемого CTE. Обратите внимание, что вместо подзапросов оператор теперь использует столбец avg_weight из CTE.
Повторю, что разница в производительности этих двух подходов незначительна, поскольку мы работаем с небольшим набором данных. Только выполнение этих операторов на более реалистичных данных может дать вам более правдивую картину разницы в производительности. Однако на мой вкус CTE делает код более читабельным, т.е. легче отслеживать логику оператора.
Определение нескольких CTE в одном предложении WITH
До сих пор в примерах этой статьи было только по одному CTE на предложение WITH, но вы можете определить несколько CTE и ссылаться на любое из них в операторе верхнего уровня. Просто следите, чтобы имена CTE различались и разделялись запятыми. Например, следующее предложение WITH определяет три CTE, на каждое из которых ссылается оператор SELECT верхнего уровня:
WITH
jets AS
(SELECT plane, engine_type, engine_count, max_weight
FROM airplanes WHERE engine_type = 'jet'),
turbos AS
(SELECT plane, engine_type, engine_count, max_weight
FROM airplanes WHERE engine_type = 'turboprop'),
pistons AS
(SELECT plane, engine_type, engine_count, max_weight
FROM airplanes WHERE engine_type = 'piston')
SELECT * FROM jets
UNION ALL
SELECT * FROM turbos
UNION ALL
SELECT * FROM pistons; Три CTE похожи, они отличаются лишь тем, что выбирают данные на основе разных значений engine_type. Запрос SELECT верхнего уровня использует затем оператор UNION для их объединения. (Оператор UNION ALL собирает результаты нескольких операторов SELECT в единый результирующий набор.) Следующий рисунок показывает фрагмент результатов, возвращаемых этим оператором.
Это довольно простой пример, но он демонстрирует принцип определения нескольких CTE и ссылки на них в запросе верхнего уровня. В этом случае три CTE работают независимо друг от друга, но вы не всегда должны принимать этот подход. Например, предложение WITH в следующем примере также включает три CTE, но теперь второе CTE (mfc_avg) ссылается на первое CTE (mfcs), в то время как третье CTE (pl_avg) остается автономным:
WITH
mfcs (id, mfc) AS
(SELECT manufacturer_id, manufacturer FROM manufacturers),
mfc_avg (id, mfc, avg_parking) AS
(SELECT m.id, m.mfc, ROUND(AVG(a.parking_area))
FROM mfcs m INNER JOIN airplanes a
ON m.id = a.manufacturer_id
GROUP BY manufacturer_id),
pl_avg (avg_all) AS
(SELECT ROUND(AVG(parking_area)) FROM airplanes)
SELECT id, mfc, avg_parking
FROM mfc_avg m
WHERE avg_parking > (SELECT avg_all FROM pl_avg);Как показывает данный пример, CTE может ссылаться на CTE, который находится до него. Однако это работает только в одном направлении; CTE не может ссылаться на CTE, который идет после него. В данном случае CTE mfc_avg соединяет таблицу airplanes с CTE mfcs и группирует данные по значению manufacturer_id. Затем запрос верхнего уровня извлекает данные из этого CTE, но возвращает только те строки, для которых значение avg_parking больше среднего, возвращаемого CTE pl_avg. На следующей картинке показаны результаты, возвращаемые этим оператором.
Стоит обратить внимание на то, что предложение WHERE запроса верхнего уровня включает подзапрос, который извлекает данные из CTE pl_avg. Это говорит не только о гибкости, присущей CTE, но и том, что CTE и подапросы не исключают друг друга.
Работа с рекурсивными CTE
Одним из наиболее полезных аспектов CTE является возможность выполнять рекурсивные запросы. Этот тип CTE - известный как рекурсивный CTE - ссылается на самого себя в пределах запроса CTE. Предложение WITH в рекурсивном CTE должно включать ключевое слово RECURSIVE, а запрос CTE должен включать две части, которые разделяются оператором UNION. Первая (нерекурсивная) часть заполняет начальную строку данными, а вторая (рекурсивная) часть фактически выполняет рекурсию на основе первой строки. Только рекурсивная часть может ссылаться на само CTE.
Чтобы понять, как это работает, рассмотрим следующий пример, который генерирует список четных чисел до 20 включтельно:
WITH RECURSIVE counter (val) AS
(SELECT 2
UNION ALL
SELECT val + 2 FROM counter WHERE val < 20)
SELECT * FROM counter;CTE называется counter, и оно возвращает только один столбец val. Нерекурсвная часть запроса CTE устанавливает для первой строки значение 2, которое присваивается столбцу val. Рекурсивная часть запроса извлекает данные из CTE, но увеличивает значение столбца val на 2 на каждой итерации. Запрос продолжает инкрементровать столбец на 2, пока значение val меньше 20. Затем оператор SELECT верхнего уровня извлекает данные из CTE, возвращая результаты, показанные на следующем рисунке.
Замечание. Поскольку рекурсивный запрос говорит < 20, вы могли бы решить, что значение 20 не попадет в вывод. Однако итерация, которая возврашает 20, выполняется и останавливает итерационный процесс, поскольку значение перестает быть меньше 20.
При построении рекурсивных CTE имейте в виду, что MySQL накладывает несколько ограничений на рекурсивную часть. Второй оператор SELECT не может содержать агрегатные фукнции, оконные функции, ключевое слово DISTINCT или предложения GROUP BY, или ORDER BY.
Выполнение рекурсивного запроса может быть полезным при работе с иерархическими данными. Для демонстрации их работы я использовал следующий код для создания и заполнения таблицы с именем airline_emps, которая хранит ID и наименования должностей для группы фиктивных сотрудников наряду с ID лица, которому каждый их них подчиняется:
CREATE TABLE airline_emps (
emp_id INT UNSIGNED NOT NULL,
job_title varchar(50) NOT NULL,
reports_to INT UNSIGNED NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (emp_id),
CONSTRAINT fk_emp_id FOREIGN KEY (reports_to)
REFERENCES airline_emps (emp_id) );
INSERT INTO airline_emps
(emp_id, job_title, reports_to)
VALUES
(1, 'big boss', NULL),
(2, 'divsional boss', 1),
(3, 'supervisor', 2),
(4, 'chief', 2),
(5, 'designer', 3),
(6, 'top exec', 1),
(7, 'overseer', 6),
(8, 'team leader', 6),
(9, 'organizer', 8),
(10, 'controller', 8),
(11, 'specialist', 10),
(12, 'analyst', 9);
SELECT * FROM airline_emps;Каждый, за исключением emp_id 1 (главный босс), подчиняется кому-то другому. Например, надзиратель отчитывается перед высшим руководством, которое в свою очередь подчиняется главному боссу. Я добавил в код оператор SELECT, чтобы вы могли проверить данные, после добавления их в таблицу.
Имея эти данные, вы можете теперь создать следующее рекурсивное CTE, которое находит должность человека в компании, и как эта должность вписывается в иерархию подчиненности:
WITH RECURSIVE emps AS
(SELECT emp_id, job_title, reports_to,
1 AS emp_tier, CAST(emp_id AS CHAR(50)) AS emp_path
FROM airline_emps
WHERE reports_to IS NULL
UNION ALL
SELECT a.emp_id, a.job_title, a.reports_to,
e.emp_tier + 1,
CONCAT(a.emp_id, ' / ', e.emp_path)
FROM airline_emps a INNER JOIN emps e
ON a.reports_to = e.emp_id)
SELECT * FROM emps
ORDER BY emp_tier, emp_id;Поскольку это рекурсивное CTE, оно разделено на две части, которые соединяются оператором UNION ALL. Нерекурсивная часть заполняет первую строку на основе NULL-значения в столбце reports_to. Это строка для главного босса, кто находится наверху иерархии. Нерекурсивная часть также присваивает значение 1 столбцу emp_tier и значение emp_id - столбцу emp_path, преобразуя значение к тпу данных CHAR. Первая строка, возвращаемая CTE выглядит подобно показанной на следующем рисунке.
Рекурсивная часть CTE использует внутрее соединение по совпадению значений таблицы airline_emps и CTE emps. Соединение основано на столбце reports_to в таблице airline_emps и столбце emp_id в CTE emps. Условие соединения делает возможным рекурсивный обход каждого уровня иерархии подчиненности на основе значения reports_to. Затем рекурсивная часть инкрементирует столбец emp_tier на 1 на каждом уровне иерархии.
Например, emp_id 2 (начальник отделения) и emp_id 6 (исполнительный директор) оба подчиняются emp_id 1 (главному боссу), поэтому столбец emp_tier для этих двух строк инкрементируется на 1, что дает значение 2 для каждой строки. Это означает, что он оба находятся на втором уровне иерархии сотрудников. Следующий уровень в иерархии включает тех людей, кто подчиняется начальнику отделения или исполнительному директору, поэтому столбец emp_tier для этих строк устанавливается в 3. Этот процесс подолжается до тех пор, пока остаются уровни.
Во время этого процесса также обновляется столбец emp_path для каждой строки, конкатенируя значения emp_id для обеспечения представления иерархии подчиненности. Например, столбец reports_to для emp_id 9 покажет, что организатор подчняется emp_id 8 (руководителю группы), который подчиняется emp_id 6 (исполнительному директору), который подчиняется emp_id 1 (главному боссу). При этом каждый слой отделяется прямым слэшем. Следующий рисунок показывает данные, возвращаемые запросом.
Оператор SELECT верхнего уровня возвращает данные только из CTE без соединения с какой-либо другой таблицей. Оператор также включает предложение ORDER BY, которое сортирует результаты сначала по столбцу emp_tier, а затем по столбцу emp_id.
Использование CTE с операторами DML
Ранее в статье я упомянул, что вы можете использовать CTE с другими операторами помимо SELECT. Я также говорил, что сосредоточусь в первую очередь на применении CTE в операторе SELECT. Однако я хочу показать вам, по крайней мере, один из альтернативных вариантов, чтобы вы получили представление, как это может выглядеть (и немного подогреть ваш интерес).
Следующий пример показывает CTE, используемое с оператором UPDATE для модификации данных в таблице airline_emps, созданной в предыдущем разделе:
WITH rpts AS
(SELECT emp_id FROM airline_emps
WHERE reports_to = 
UPDATE airline_emps
SET reports_to = 7
WHERE emp_id IN (SELECT * FROM rpts);Предложение WITH и CTE работают так же, как показано в других примерах. Предложение включает единственное CTE с именем rpts, которое извлекает значения emp_id для тех сотрудников, которые подчняются emp_id 8. Запрос возвращает значения 9 и 10.
Оператор UPDATE вехнего уровня использует данные, возвращаемые CTE для обновления столбца reports_to значением 7 для этих двух сотрудников. Предложение WHERE оператора UPDATE включает подзапрос, который извлекает данные из CTE, поэтому оператор будет обновлять только эти две строки.
После выполнения этого оператора обновления вы можете снова выполнить оператор SELECT из предыдущего раздела, чтобы проверить изменения. Я включил сюда этот оператор для удобства:
WITH RECURSIVE emps AS
(SELECT emp_id, job_title, reports_to,
1 AS emp_tier, CAST(emp_id AS CHAR(50)) AS emp_path
FROM airline_emps
WHERE reports_to IS NULL
UNION ALL
SELECT a.emp_id, a.job_title, a.reports_to,
e.emp_tier + 1,
CONCAT(a.emp_id, ' / ', e.emp_path)
FROM airline_emps a INNER JOIN emps e
ON a.reports_to = e.emp_id)
SELECT * FROM emps
ORDER BY emp_tier, emp_id;Оператор возвращает результаты, показанные на рисунке ниже.
Обратите внимание, что сотрудники 9 и 10 теперь имеют значение reports_to = 7. Кроме того, значение emp_path для каждой из двух строк была обновлена, чтобы отразить новую иерархию подчинения.
Работа с общими табличным выражениями в MySQL
CTE может быть мощным инструментом при запросах на выборку и модификацию данных в базах данных MySQL. Рекурсивные CTE, в частности, могут оказаться полезными при работе с данными, которые ссылаются сами на себя, как это было продемонстрировано в примерах ранее. Но CTE не всегда интуитивно понятны, и вы должны хорошо понимать как они работают, прежде чем начать добавлять их в код текущей базы данных, особенно если вы хотите использовать их для модификации данных. По этой причине я рекомендую вам также ознакомиться с документацией MySQL на CTE и другими доступными рекурсами. Чем больше времени вы потратите на изучение CTE, нет более эффективно вы будете использовать их в своих запросах.
Приложение: подготовка демонстрационных объектов и данных
Для примеров в этой статье я создал базу данных travel и добавил в нее таблицы manufacturers и airplanes. Если вы хотите проверить эти примеры у себя, начните с выполнения следующего скрипта на своем экземпляре MySQL.
DROP DATABASE IF EXISTS travel;
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) );
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) );После создания базы данных вы можете добавить некоторые данные в таблицу manufacturers, выполнив следующий оператор INSERT:
INSERT INTO manufacturers (manufacturer_id, manufacturer)
VALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'), (103,'Beechcraft'),
(104,'Boeing'), (105,'Bombardier'), (106,'Cessna'), (107,'Embraer');
SELECT * FROM manufacturers; Я включил после оператора INSERT оператор SELECT, чтобы вы могли проверть, что в таблицу были вставлены 7 строк. Первая строка в таблце имеет значение 101 в столбце manufacturer_id, а последующие строк инкрементируют это значение на единицу. После заполнения таблицы manufacturers вы можете выполнить следующий оператор INSERT для добавления данных в таблицу airplanes:
INSERT INTO airplanes
(plane_id, plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
(1001,'A340-600',101,'Jet',4,208.17,247.24,837756,'A346'),
(1002,'A350-800 XWB',101,'Jet',2,212.42,198.58,546700,'A358'),
(1003,'A350-900',101,'Jet',2,212.42,219.16,617295,'A359'),
(1004,'A380-800',101,'Jet',4,261.65,238.62,1267658,'A388'),
(1005,'A380-843F',101,'Jet',4,261.65,238.62,1300000,'A38F'),
(1006,'A.109 Airedale',102,'Piston',1,36.33,26.33,2750,'AIRD'),
(1007,'A.61 Terrier',102,'Piston',1,36,23.25,2400,'AUS6'),
(1008,'B.121 Pup',102,'Piston',1,31,23.17,1600,'PUP'),
(1009,'B.206',102,'Piston',2,55,33.67,7500,'BASS'),
(1010,'D.5-108 Husky',102,'Piston',1,36,23.17,2400,'D5'),
(1011,'Baron 56 TC Turbo Baron',103,'Piston',2,37.83,28,5990,'BE56'),
(1012,'Baron 58 (and current G58)',103,'Piston',2,37.83,29.83,5500,'BE58'),
(1013,'Beechjet 400 (same as MU-300-10 Diamond II)',103,'Jet',2,43.5,48.42,15780,'BE40'),
(1014,'Bonanza 33 (F33A)',103,'Piston',1,33.5,26.67,3500,'BE33'),
(1015,'Bonanza 35 (G35)',103,'Piston',1,32.83,25.17,3125,'BE35'),
(1016,'747-8F',104,'Jet',4,224.42,250.17,987000,'B748'),
(1017,'747-SP',104,'Jet',4,195.67,184.75,696000,'B74S'),
(1018,'757-300',104,'Jet',2,124.83,178.58,270000,'B753'),
(1019,'767-200',104,'Jet',2,156.08,159.17,315000,'B762'),
(1020,'767-200ER',104,'Jet',2,156.08,159.17,395000,'B762'),
(1021,'Learjet 24',105,'Jet',2,35.58,43.25,13000,'LJ24'),
(1022,'Learjet 24A',105,'Jet',2,35.58,43.25,12499,'LJ24'),
(1023,'Challenger (BD-100-1A10) 350',105,'Jet',2,69,68.75,40600,'CL30'),
(1024,'Challenger (CL-600-1A11) 600',105,'Jet',2,64.33,68.42,36000,'CL60'),
(1025,'Challenger (CL-600-2A12) 601',105,'Jet',2,64.33,68.42,42100,'CL60'),
(1026,'414A Chancellor',106,'Piston',2,44.17,36.42,6750,'C414'),
(1027,'421C Golden Eagle',106,'Piston',2,44.17,36.42,7450,'C421'),
(1028,'425 Corsair-Conquest I',106,'Turboprop',2,44.17,35.83,8600,'C425'),
(1029,'441 Conquest II',106,'Turboprop',2,49.33,39,9850,'C441'),
(1030,'Citation CJ1 (Model C525)',106,'Jet',2,46.92,42.58,10600,'C525'),
(1031,'EMB 175 LR',107,'Jet',2,85.33,103.92,85517,'E170'),
(1032,'EMB 175 Standard',107,'Jet',2,85.33,103.92,82673,'E170'),
(1033,'EMB 175-E2',107,'Jet',2,101.67,106,98767,'E170'),
(1034,'EMB 190 AR',107,'Jet',2,94.25,118.92,114199,'E190'),
(1035,'EMB 190 LR',107,'Jet',2,94.25,118.92,110892,'E190');
SELECT * FROM airplanes;Оператор INSERT использует значения manufacturer_id из таблицы manufacturers. Эти значения служат значениями внешнего ключа, требуемые для столбца manufacturer_id в таблице airplanes. Кроме того, первой строке присваивается значение 1001 в столбце plane_id, а значения plane_id для других строк инкрементируются соответствующим образом. Как и в предыдущем операторе INSERT, я включил оператор SELECT для контроля вставки данных.
Ссылки по теме
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой