Теория: VIEW и CTE

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

В этом уроке мы изучим представления и обобщенные табличные выражения — инструменты, которые позволяют разбить сложный запрос на несколько простых частей.

Применяем CTE

Пусть в таблице books хранится информация о книгах: идентификатор, название, жанр, возрастной рейтинг и оценка пользователей — books (book_id, book_author_id, title, genre, age_rating, users_score).

books

View on DB Fiddle

Сервис отображает подборки книг, например, 50 лучших приключенческих книг. Такую подборку можно сформировать с помощью запроса:

SELECT bk.title
FROM books AS bk
WHERE bk.genre = 'Роман'
ORDER BY bk.users_score DESC
LIMIT 50

View on DB Fiddle

В запросе выполняется фильтрация по жанру, упорядочивание по оценке от лучших к худшим и ограничение выборки первыми 50 строками.

Дополнительно у каждого пользователя могут стоять индивидуальные настройки фильтрации. Например, ограничение по возрастному рейтингу, если приложением пользуется ребенок.

Ниже запрос, который отбирает все книги без рейтинга 18+:

SELECT *
FROM books AS bk
WHERE bk.age_rating <> '18+'

View on DB Fiddle

Каждый раз, когда мы составляем новую подборку, нам надо учитывать и индивидуальные настройки, и правила для подборки. В реальной системе итоговый запрос может оказаться сложным.

Упростить решение этой задачи можно с помощью приема «запрос к запросу». Для этого нам понадобятся обобщенные табличные выражения — CTE (Common Table Expressions).

Рассмотрим такое решение:

WITH user_filtered_books AS ( -- объявляем CTE
    -- запрос, который отбирает книги по индивидуальному фильтру
  SELECT *
  FROM books AS bk
  WHERE bk.age_rating <> '18+'
)

-- запрос, который формирует подборку
SELECT ufb.title
FROM user_filtered_books AS ufb -- используем CTE
WHERE ufb.genre = 'Роман'
ORDER BY ufb.users_score DESC
LIMIT 50

View on DB Fiddle

Синтаксис этого запроса имеет следующую структуру:

WITH <CTE-Name> AS (
    <CTE-Query>
)
<Main-Query>

Сначала выполняется запрос <CTE-Query>, из его результата формируется таблица <CTE-Name>. Затем выполняется основной запрос — <Main-Query>, он обращается к данным временной таблицы. Когда весь запрос выполнен, таблица удаляется.

В нашем примере формируется таблица UserFilteredbooks с книгами подходящего возрастного рейтинга. А основной запрос использует ее для создания подборки лучших приключенческих книг.

Таблица, созданная с помощью оператора WITH, видна только основному запросу и исчезает после его завершения. Если такая же таблица нужна в других запросах, то лучше воспользоваться представлениями.

Применяем представления

Представление или VIEW — это виртуальная таблица. Ее данные нигде не хранятся, а формируются с помощью специального SQL-запроса. Этот запрос выполняется каждый раз, когда происходит обращение к таблице.

Рассмотрим тот же пример и решим его с помощью представления.

Создадим представление:

CREATE VIEW books_for_non_adult AS
SELECT *
FROM books AS bk
WHERE bk.age_rating <> '18+'

View on DB Fiddle

Представление называется books_for_non_adult. Ему соответствует запрос, который отбирает книги по возрастному рейтингу.

Воспользуемся представлением и сформируем подборку:

CREATE VIEW books_for_non_adult AS
SELECT *
FROM books AS bk
WHERE bk.age_rating <> '18+';

SELECT bfna.title
FROM books_for_non_adult AS bfna -- используем VIEW
WHERE bfna.genre = 'Роман'
ORDER BY bfna.users_score DESC
LIMIT 10

View on DB Fiddle

Этот запрос обращается к нашему представлению — к виртуальной таблице, в которой отобраны книги по возрастному рейтингу.

Логически решение с CTE и VIEW работают одинаково. Но одно и то же представление можно использовать по имени в разных запросах, а временную таблицу из CTE — только там, где она объявлена.

Выводы

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

Когда внешний запрос обращается к виртуальной таблице, ее данные формируются заново, так как они нигде не хранятся. При этом CTE доступны только в том запросе, где они объявлены. А VIEW, напротив, можно использовать в разных запросах.