SQL. Обобщенное табличное выражение и способы его использования
Обобщенное табличное выражение является общим инструментов для многих баз данных. Рассмотрим конкретнее, что же это такое и как с ним работать на примере средств MS SQL.
Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как select, update, insert и delete.
Выведем количество сотрудников, устроившихся на работу, в разбивке по годам:
Еще обобщенное табличное выражение можно составить из результатов нескольких запросов. Последний результирующий запрос обращается к данным нижнего CTE(TABLE_CTE2), но может и к любому из них:
Основные способы использования:
- для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода);
- в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц;
- для создания представлений (VIEW) в части select;
- для написания рекурсивных запросов.
Отличия от вложенного запроса:
- вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса.
Отличия от временной таблицы:
- заполнение временной таблицы при больших объемах создает нагрузку на диск;
- исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb).
Мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.
Когда использовать общее табличное выражение (CTE)
Я начал читать о Common Table Expression и не могу придумать вариант использования, в котором мне нужно было бы их использовать. Они могут показаться излишними, поскольку то же самое можно сделать и с производными таблицами. Что-то мне не хватает или я плохо понимаю? Может ли кто-нибудь дать мне простой пример ограничений с обычными запросами на выборку, производную или временную таблицу, чтобы сделать случай CTE? Приветствуются любые простые примеры.
9 ответов
Один пример: если вам нужно ссылаться / присоединяться к одному и тому же набору данных несколько раз, вы можете сделать это, определив CTE. Следовательно, это может быть форма повторного использования кода.
Примером самореференции является рекурсия: Рекурсивные запросы с использованием CTE
Для захватывающих определений Microsoft Взято из электронной документации:
CTE может использоваться для:
Создайте рекурсивный запрос. Для получения дополнительной информации см. Рекурсивные запросы с использованием общих табличных выражений.
Замена представления, когда общее использование представления не требуется; то есть вам не нужно хранить определение в метаданных.
Включите группировку по столбцу, который получен из скалярного подвыбора, или функции, которая не является детерминированной или имеет внешний доступ.
Ссылаться на полученную таблицу несколько раз в одном выражении.
Я использую их для разбиения сложных запросов, особенно сложных объединений и подзапросов. Я обнаружил, что все чаще и чаще использую их в качестве «псевдо-представлений», чтобы понять смысл запроса.
Моя единственная жалоба на них — они не могут быть использованы повторно. Например, у меня может быть хранимая процедура с двумя операторами обновления, которые могут использовать один и тот же CTE. Но «область действия» CTE — это только первый запрос.
Проблема в том, что «простые примеры», вероятно, действительно не нуждаются в CTE!
Тем не менее, очень удобно.
Я вижу две причины использовать cte.
Чтобы использовать вычисленное значение в предложении where. Мне это кажется немного чище, чем производная таблица.
Предположим, есть две таблицы — Вопросы и Ответы, объединенные вместе с помощью Questions.ID = Answers.Question_Id (и id викторины)
Вот еще один пример, в котором я хочу получить список вопросов и ответов. Я хочу, чтобы ответы были сгруппированы с вопросами в результатах.
Один из сценариев, который я нашел полезным для использования CTE, — это когда вы хотите получить DISTINCT строки данных на основе одного или нескольких столбцов, но вернуть все столбцы в таблице. С помощью стандартного запроса вам, возможно, сначала придется выгрузить отдельные значения во временную таблицу, а затем попытаться объединить их обратно в исходную таблицу, чтобы получить остальные столбцы, или вы можете написать чрезвычайно сложный запрос раздела, который может вернуть результаты в один запуск, но, скорее всего, он будет нечитаемым и вызовет проблемы с производительностью.
Но с помощью CTE (как ответил Тим Шмелтер на Выберите первый экземпляр записи)
Как видите, это намного проще читать и поддерживать. И по сравнению с другими запросами намного лучше по производительности.
Возможно, более целесообразно рассматривать CTE как замену представлению, используемому для одного запроса. Но не требует накладных расходов, метаданных или постоянства формального представления. Очень полезно, когда нужно:
- Создать рекурсивный запрос.
- Используйте набор результатов CTE более одного раза в своем запросе.
- Повысьте ясность вашего запроса, сократив большие куски идентичных подзапросов.
- Включить группировку по столбцу, полученному в наборе результатов CTE
Вот пример вырезания и вставки, с которым можно поиграть:
Сегодня мы собираемся узнать об общем табличном выражении, которое является новой функцией, которая была представлена в SQL Server 2005 и доступна также в более поздних версиях.
Выражение общей таблицы: — Выражение общей таблицы может быть определено как временный набор результатов или, другими словами, его заменитель представлений в SQL Server. Общее табличное выражение допустимо только в пакете инструкций, в котором оно было определено, и не может использоваться в других сеансах.
Синтаксис объявления CTE (Общее табличное выражение): —
Я создал две таблицы «employee» и «Dept» и вставил по 5 строк в каждую таблицу. Теперь я хотел бы объединить эти таблицы и создать временный набор результатов, чтобы использовать его в дальнейшем.
Давайте рассмотрим каждую строку утверждения по очереди и разберемся.
Чтобы определить CTE, мы пишем предложение «with», затем мы даем имя табличному выражению, здесь я дал имя как «CTE_Example»
Затем мы пишем «As» и заключаем наш код в две скобки (—), мы можем объединить несколько таблиц в заключенные скобки.
В последней строке я использовал «Select * from CTE_Example», мы ссылаемся на общее табличное выражение в последней строке кода, поэтому мы можем сказать, что это похоже на представление, где мы определяем и используем представление в одном batch и CTE не хранятся в базе данных как постоянный объект. Но ведет себя как вид. мы можем выполнить операторы удаления и обновления для CTE, и это окажет прямое влияние на ссылочную таблицу, которая используется в CTE. Давайте рассмотрим пример, чтобы понять этот факт.
В приведенном выше операторе мы удаляем строку из CTE_Example, и он удалит данные из указанной таблицы «DEPT», которая используется в CTE.
Игра в прятки с оптимизатором. Гейм овер, это CTE PostgreSQL 12
Эта статья — продолжение рассказа о новом в PostgreSQL 12. Мы уже разобрали SQL/JSON (патч JSONPath) в статье «Что заморозили на feature freeze 2019. Часть I. JSONPath», теперь очередь CTE.
CTE это Common Table Expression — общие табличные выражения, их еще называют конструкциями с WITH. Фактически это создание временных таблиц, но существующих только для одного запроса, а не для сессии. К ним можно обращаться внутри этого запроса. Такой запрос хорошо читается, он понятен, его легко видоизменять, если потребуется. Это очень востребованная вещь, и она в PostgreSQL давно.
Но удобства могут обойтись дорого. Проблемы связаны с материализацией выражения после AS внутри конструкции WITH… AS (). Его еще называют внутренним выражением и вычисляют перед тем, как начать вычисление остального, его нельзя встроить в запрос верхнего уровня (no inlining). Планирование этого выражения происходит без учета остальной части запроса. Такое поведение называют барьером для оптимизации, или fencing. Кроме того, сама материализация требует под себя work_mem. И если выборка большая, то начинаются проблемы (об этом, например, есть в докладе Ивана Фролкова на PGConf 2019).
Игра в прятки с оптимизатором, которую мы разберем ниже, в целом не баг, а фича. Конечно, есть ситуации, когда предварительное вычисление части выражения избавляет, скажем, от ненужных повторных операций в рекурсивных запросах. С другой стороны, многие разработчики пользовались CTE как view, не задумываясь о том самом барьере, а в результате запросы с CTE исполнялись не просто медленнее, чем эквивалентные им (но более замысловатого вида) запросы с подзапросами, а медленней на порядки. Взвесив за и против, сообщество пошло на довольно решительный шаг: изменило поведение по умолчанию.
Будем наблюдать работу CTE на такой табличке:
Начнем с простенького запроса:
Всё считается моментально, используется только индекс.
Запрос с подзапросом, вычисляющий то же самое, но с синтаксисом чуть посложней:
Всё в порядке, очень быстрое вычисление по индексу.
А теперь еще один логически эквивалентный запрос, но уже с CTE:
Такая задержка видна уже и невооруженным глазом. Кофе не попьешь, но в почту заглянуть времени хватит (когда у нас 11-я версия или более ранняя).
А произошло вот что: в случае с подзапросами оптимизатор сразу сообразил, что условия x=2 и y>1 можно объединить в один фильтр и искать по индексу. В случае CTE у оптимизатора нет выбора: он должен сначала разобраться с условием внутри конструкции WITH… AS, материализовать результат и только после этого работать дальше.
И здесь дело не в том, что материализация потребует ресурсов: если условие будет y<3, то материализовать придется не миллионы записей а всего 2. Чудовищное для несложного запроса время тратится на последовательный поиск, оптимизатор не может использовать поиск по индексу из-за того, что составной индекс строится по x, а уже потом по y, а о запросе с условием x=2 он ничего не узнает, пока не выполнит внутренне условие CTE. Оно — за барьером.
Итак, до версии PostgreSQL 12 по умолчанию была материализация, теперь — ее отсутствие. Запускаем тот же запрос на базе новой версии. Барьера как не бывало, оптимизатор сразу видит весь запрос:
Оптимизатор моментально научился объединять условия в оптимальном порядке — как это было с подзапросами.
Но умолчания умолчаниями, а для полного владения ситуацией теперь, в версии 12 имеется контролируемая, управляемая материализация CTE:
Всё как в 11 и до нее, можно глянуть почту в режиме ожидания результатов запроса. Запрещаем материализацию, убираем барьер:
Опять никакой передышки: считается моментально.
Остались нюансы. Но важные нюансы.
CTE материализуется по умолчанию, если к ней обращаются более одного раза.
На первый взгляд материализация в таких случаях разумное решение: зачем вычислять одно и то же дважды. На практике это часто приводит к тому, что мы наблюдали выше. Чтобы заставить отказаться от материализации, надо явно приказать оптимизатору: NOT MATERIALIZED.
Исполняем без NOT MATERIALIZED запрос с двойным WHERE:
А теперь явно пропишем запрет на материализацию:
пишущие CTE исполняются всегда, а CTE, на которые нет ссылок — никогда.
Это видно из плана: not_executed в нем нет. Это верно и для предыдущих версий, но об этом стоит помнить, и к исполняемому выражению в версии 12 применима конструкция (NOT) MATERIALIZED.
И еще одно правило:
рекурсивные запросы с WITH материализуются всегда.
Именно всегда, а не по умолчанию. Если мы прикажем оптимизатору: NOT MATERIALIZED, ошибки не будет, а материализация все равно будет. Это сознательное, обсуждавшееся сообществом решение.
Будем считать иллюстрирующий пример домашним заданием. На этом на сегодня всё.
В этой части обзора, посвященной новому в CTE, используются примеры и фрагменты из доклада «Postgres 12 в этюдах», который прочитал Олег Бартунов на Saint Highload++ в СПБ 9 апреля сего года.
Что такое CTE в PostgreSQL?
База данных
CTE в PostgreSQL означает обычное табличное выражение. Это способ временного хранения результатов запроса PostgreSQL. Иногда мы пишем чрезвычайно сложные запросы, которые очень трудно интерпретировать. В таких случаях использование CTE делает наши запросы более простыми и читаемыми. С помощью этой статьи мы намерены научить вас использовать CTE в PostgreSQL в Windows 10.
Пример: использование CTE в PostgreSQL
Мы будем использовать CTE в PostgreSQL в Windows 10 в следующем примере.
Шаг 1: Создание таблиц PostgreSQL
Прежде всего, мы создадим две таблицы PostgreSQL, чтобы позже использовать CTE для извлечения желаемых результатов из этих таблиц. На этой иллюстрации мы хотим работать с отношениями между врачами и пациентами. Поэтому мы создадим таблицу с именем «доктор», а другую — с именем «пациент».
Для создания таблицы «врач» запустим следующий запрос PostgreSQL:
Этот запрос создаст таблицу «врач» с двумя атрибутами, то есть Doc_ID и Doc_Name. Вы также можете увидеть весь процесс создания таблицы на изображении, показанном ниже:
Теперь для создания таблицы «пациентов» мы запустим следующий запрос PostgreSQL:
# CREATE TABLE patient(Pat_ID SERIAL PRIMARY KEY, Pat_Name VARCHAR (255) NOT NULL, Pat_Temp INT NOT NULL, Doc_ID INT NOT NULL);
Этот запрос создаст таблицу «пациента» с четырьмя атрибутами, то есть Pat_ID, Pat_Name, Pat_Temperature (представляет температуру тела пациента) и Doc_ID (это тот же Doc_ID, который мы объявили в таблице «доктор». Здесь, он используется как внешний ключ, чтобы указать, какие врачи лечили каждого пациента). Вы также можете увидеть весь процесс создания таблицы на изображении, показанном ниже:
Шаг 2: Вставка записей в таблицы PostgreSQL
После создания этих таблиц мы должны вставить в них достаточное количество записей, чтобы использовать эти записи для демонстрации использования CTE в PostgreSQL в дальнейшем. Для вставки записей в таблицу «доктор» запустим следующий запрос PostgreSQL:
# INSERT INTO doctor VALUES(1, ‘Sarah’), (2, ‘Affan’), (3, ‘Irtiza’), (4, ‘Hina’), (5, ‘Naila’);
Этот запрос просто вставит записи пяти разных врачей в таблицу «доктор», как показано на изображении ниже:
Теперь, чтобы вставить записи в таблицу «пациентов», мы запустим следующий запрос PostgreSQL:
# INSERT INTO patient VALUES(1, ‘Saba’, 99, 1), (2, ‘Sidra’, 100, 1), (3, ‘Hamza’, 100, 2), (4, ‘Aslam’, 98, 2), (5, ‘Fizza’, 101, 3), (6, ‘Iqra’, 102, 3), (7, ‘Sadia’, 100, 4), (8, ‘Sobia’, 99, 4), (9, ‘Salman’, 100, 5), (10, ‘Jawad’, 103, 5);
Этот запрос вставит записи 10 разных пациентов в таблицу «пациентов», как показано на изображении ниже:
Примечание. Вам может быть интересно, почему мы сохранили количество записей в таблице «пациент» больше, чем в таблице «врач». Что ж, один врач может обслуживать несколько пациентов одновременно. Однако это просто демонстрация. При желании вы можете сохранить одинаковое количество записей в этих двух таблицах.
Шаг 3: Просмотрите недавно вставленные записи в таблицах PostgreSQL
Прежде чем продолжить, мы быстро просмотрим записи, вставленные в наши две таблицы PostgreSQL. Для таблицы «доктор» мы запустим следующий запрос PostgreSQL:
Вы можете увидеть все записи таблицы «врач» на изображении ниже:
Теперь для таблицы «пациентов» мы запустим следующий запрос PostgreSQL:
Вы можете увидеть все записи таблицы «пациента» на изображении, показанном ниже:
Шаг 4: Используйте CTE для отображения всех записей таблицы PostgreSQL
Этот шаг продемонстрирует относительно простое использование CTE в PostgreSQL. Мы хотим сохранить все записи одной из наших таблиц в общем табличном выражении, а затем просто отобразить его на консоли. Запрос, который мы собираемся выполнить для этой цели, цитируется ниже:
# WITH CTE_Patient AS (SELECT Pat_ID, Pat_Name, Pat_Temp, Doc_ID FROM patient) SELECT * FROM CTE_Patient;
Теперь мы объясним вам весь этот запрос, обсуждая все его компоненты. Перед именем общего табличного выражения всегда стоит ключевое слово WITH, а после него — ключевое слово AS. Это означает, что имя нашего CTE в данном конкретном случае — «CTE_Patient». После ключевого слова «AS» мы указываем весь запрос, результаты которого мы хотим сохранить в нашем общем табличном выражении. В этом примере мы просто хотим выбрать все записи, содержащие все атрибуты таблицы «пациентов», а затем сохранить их в нашем CTE. После этого мы использовали оператор «SELECT» для отображения содержимого этого CTE на нашей консоли. Этот запрос возьмет все десять записей из нашей таблицы «пациентов», временно сохранит их в CTE_Patient, а затем отобразит содержимое CTE_Patient на консоли, как показано на изображении ниже:
Шаг 5: Используйте CTE с предложением «WHERE» в PostgreSQL
Теперь мы перейдем к относительно сложному использованию CTE в PostgreSQL, т.е. мы будем использовать CTE с предложением «WHERE» в PostgreSQL. В этом модифицированном примере мы стремимся проверить температуру у всех пациентов. А затем отобразить имена и идентификаторы только тех пациентов, у которых есть лихорадка. Запрос, который будет служить этой цели, выглядит следующим образом:
# WITH CTE_Patient AS (SELECT Pat_ID, Pat_Name, (CASE WHEN Pat_Temp <= 100 THEN ‘NORMAL’ WHEN Pat_Temp > 100 THEN ‘FEVER’ END) Temperature FROM patient) SELECT Pat_ID, Pat_Name, Temperature FROM CTE_Patient WHERE Temperature = ‘FEVER’ ORDER BY Pat_Name;
В этом запросе мы использовали оператор CASE для переменной температуры. Основным условием для этого утверждения является то, что если температура пациента меньше или равна 100, она будет считаться нормальной, а если она больше 100, то у пациента будет лихорадка. После этого мы просто использовали оператор «SELECT» для отображения Pat_ID, Pat_Name и Temperature всех тех пациентов из нашего общего табличного выражения, у которых есть лихорадка. Кроме того, мы также упорядочили наши результаты в алфавитном порядке по имени пациента, как показано на изображении ниже:
Таким же образом, если вы хотите отобразить на консоли имена и идентификаторы всех тех пациентов, температура тела которых нормальная, вам необходимо немного изменить вышеупомянутый запрос следующим образом:
# WITH CTE_Patient AS (SELECT Pat_ID, Pat_Name, (CASE WHEN Pat_Temp <= 100 THEN ‘NORMAL’ WHEN Pat_Temp > 100 THEN ‘FEVER’ END) Temperature FROM patient) SELECT Pat_ID, Pat_Name, Temperature FROM CTE_Patient WHERE Temperature = ‘NORMAL’ ORDER BY Pat_Name;
Все пациенты из нашей таблицы «пациентов» с нормальной температурой тела показаны на изображении ниже:
Заключение
В этом руководстве говорилось об использовании CTE в PostgreSQL в Windows 10. Чтобы подробнее рассказать об этом использовании, мы сначала создали простой пример, а затем внесли в него некоторую сложность, чтобы читатели могли лучше понять, как CTE работает с таблицами PostgreSQL. После того, как вы внимательно изучите этот исчерпывающий пример, вы сможете изучить базовый синтаксис CTE в PostgreSQL вместе с некоторыми другими техническими деталями, а затем вы сможете эффективно использовать CTE, чтобы ваши запросы выглядели более простыми и удобочитаемыми.