Когда использовать общее табличное выражение (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.
SQL. Обобщенное табличное выражение и способы его использования
Обобщенное табличное выражение является общим инструментов для многих баз данных. Рассмотрим конкретнее, что же это такое и как с ним работать на примере средств MS SQL.
Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как select, update, insert и delete.
Выведем количество сотрудников, устроившихся на работу, в разбивке по годам:
Еще обобщенное табличное выражение можно составить из результатов нескольких запросов. Последний результирующий запрос обращается к данным нижнего CTE(TABLE_CTE2), но может и к любому из них:
Основные способы использования:
- для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода);
- в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц;
- для создания представлений (VIEW) в части select;
- для написания рекурсивных запросов.
Отличия от вложенного запроса:
- вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса.
Отличия от временной таблицы:
- заполнение временной таблицы при больших объемах создает нагрузку на диск;
- исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb).
Мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.
Что такое 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, чтобы ваши запросы выглядели более простыми и удобочитаемыми.
Common Table Expression (CTE) in SQL Server
CTE -Common Table Expression are used to make your complex joins and subqueries easier, it is a one-time result set and only exist for duration of query.
In this article we’ll learn what is CTE, Recursive CTE, Non-Recursive CTE, Advantages and Dis-Advantages of CTE.
WHAT IS CTE-(Common Table Expression)
CTE is a temporary-named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement.
CTE Syntax —
WITH cte_name (column_names)
AS (query)
SELECT * FROM cte_name;
Example —
Here, we are going to use a table “[dbo].[tbl_cust_data]” for a demonstration.
In this example, We’ll name the CTE as salary_cust and the subquery columns -name, phone number and salary.
This CTE will return all the data of customer with salary greater than 2000. After defining CTE, we will use SELECT statement to return the data.
Query —
WITH salary_cust ([ID],[Name],[PhoneNumber],[Salary])
AS (select * from [dbo].[tbl_cust_data] where Salary>2000)
SELECT * FROM salary_cust ;
After executing the above statement, it will give the following output.
Not that difficult, right?! You can also use Multiple CTE and use joins and Unions to query the data.
Syntax for Multiple CTE —
WITH
cte_name1 (column_names) AS (query),
cte_name2 (column_names) AS (query)
SELECT * FROM cte_name
UNION ALL
SELECT * FROM cte_name;
Now that we know CTE and Multiple CTE, We will see —
Types of CTE in SQL Server
- Recursive CTE
Recursive CTE that references itself, in simple words a query that calls itself, it should have an END statement so that it doesn’t go on infinitely.
A recursive CTE must have a UNION ALL statement and a second query definition that references the CTE itself in order to be recursive.
2. Non-Recursive CTE
A CTE doesn’t reference itself is known as a non-recursive CTE. It does not use the concept of recursion.
Advantages Of CTE
- CTE facilitates code maintenance easier.
- CTE increases the readability of the code.
- It increases the performance of the query.
- CTE makes it possible to implement recursive queries easily.
Disadvantages of CTE
- CTE are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
- Since it’s just a shortcut for a query or subquery, it can’t be reused in another query.
- The number of columns in the CTE arguments and the number of columns in the query must be the same.
Conclusion
Common table expressions are a powerful resource of the SQL language. They let us create more readable queries and manage format differences between table data and report data. Finally, a CTE is easy and simple for code maintainability.
That’s it for this Article , I hope you liked it. I would really appreciate if you would share this Article and Drop your Thoughts, Suggestions and Recommendations, always looking to Improve, Learn and Grow.
Until Next Time,
