Что такое CTE в PostgreSQL?

установить и настроить PostgreSQL на Ubuntu База данных

CTE в PostgreSQL означает обычное табличное выражение. Это способ временного хранения результатов запроса PostgreSQL. Иногда мы пишем чрезвычайно сложные запросы, которые очень трудно интерпретировать. В таких случаях использование CTE делает наши запросы более простыми и читаемыми. С помощью этой статьи мы намерены научить вас использовать CTE в PostgreSQL в Windows 10.

Пример: использование CTE в PostgreSQL

Мы будем использовать CTE в PostgreSQL в Windows 10 в следующем примере.

Шаг 1: Создание таблиц PostgreSQL

Прежде всего, мы создадим две таблицы PostgreSQL, чтобы позже использовать CTE для извлечения желаемых результатов из этих таблиц. На этой иллюстрации мы хотим работать с отношениями между врачами и пациентами. Поэтому мы создадим таблицу с именем «доктор», а другую — с именем «пациент».

Для создания таблицы «врач» запустим следующий запрос PostgreSQL:

# CREATE TABLE doctor(Doc_ID SERIAL PRIMARY KEY, Doc_Name VARCHAR (255) NOT NULL);

Этот запрос создаст таблицу «врач» с двумя атрибутами, то есть Doc_ID и Doc_Name. Вы также можете увидеть весь процесс создания таблицы на изображении, показанном ниже:

Этот запрос создаст таблицу «врач» с двумя атрибутами, то есть Doc_ID

Теперь для создания таблицы «пациентов» мы запустим следующий запрос 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 разных пациентов в таблицу «пациентов», как показано на изображении ниже:

Этот запрос вставит записи 10 разных пациентов в таблицу «пациентов», как показано на изображении ниже

Примечание. Вам может быть интересно, почему мы сохранили количество записей в таблице «пациент» больше, чем в таблице «врач». Что ж, один врач может обслуживать несколько пациентов одновременно. Однако это просто демонстрация. При желании вы можете сохранить одинаковое количество записей в этих двух таблицах.

Шаг 3: Просмотрите недавно вставленные записи в таблицах PostgreSQL

Прежде чем продолжить, мы быстро просмотрим записи, вставленные в наши две таблицы PostgreSQL. Для таблицы «доктор» мы запустим следующий запрос PostgreSQL:

# SELECT * FROM doctor;

Вы можете увидеть все записи таблицы «врач» на изображении ниже:

Вы можете увидеть все записи таблицы «врач» на изображении ниже

Теперь для таблицы «пациентов» мы запустим следующий запрос PostgreSQL:

# SELECT * FROM patient;

Вы можете увидеть все записи таблицы «пациента» на изображении, показанном ниже:

Вы можете увидеть все записи таблицы «пациента» на изображении, показанном ниже

Шаг 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 всех тех пациентов из нашего общего табличного выражения, у которых есть лихорадка. Кроме того, мы также упорядочили наши результаты в алфавитном порядке по имени пациента, как показано на изображении ниже:

В этом запросе мы использовали оператор CASE для переменной температуры

Таким же образом, если вы хотите отобразить на консоли имена и идентификаторы всех тех пациентов, температура тела которых нормальная, вам необходимо немного изменить вышеупомянутый запрос следующим образом:

# 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, чтобы ваши запросы выглядели более простыми и удобочитаемыми.

Читайте также:  Введение в SQLite с Python
Оцените статью
bestprogrammer.ru
Добавить комментарий