Упрощаем вложенные SQL-запросы с помощью представлений

Заметка для читателей онлайн-курса по аналитике, которые прошли хотя бы 5 уроков, уже знают, что такое SQL и умеют писать запросы к базе. Если SQL для вас в новинку, почитайте вводный урок про базы данных.

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

Например, в седьмом уроке я рассказываю, как с помощью SQL достать данные для когортного анализа.

Начинаем с безобидного «давайте подсчитаем, сколько людей в каждой когорте»:

SELECT
reg_month AS cohort_id,
count(*) AS cohort_size
FROM
users
GROUP BY 1

Потом считаем, сколько денег принесла каждая когорта:

SELECT
users.reg_month AS cohort_id,
orders.month AS month,
sum(orders.sum) AS cohort_revenue
FROM
users INNER JOIN orders
ON users.uid = orders.uid
GROUP BY 1, 2

Потом объединяем эти запросы, группируем и получаем вот такого монстра:

SELECT
rev.cohort_id AS cohort_id,
rev.month AS month,
rev.month - rev.cohort_id AS cohort_age,
rev.cohort_revenue / size.cohort_size AS LTV,
rev.cohort_revenue / size.costs * 100 AS ROI
FROM
(
SELECT
reg_month AS cohort_id,
count(*) AS cohort_size
FROM
users
GROUP BY 1
) AS size
INNER JOIN
(
SELECT
users.reg_month AS cohort_id,
orders.month AS month,
sum(orders.sum) AS cohort_revenue
FROM
users INNER JOIN orders
ON users.uid = orders.uid
GROUP BY 1, 2
) AS rev
ON size.cohort_id = rev.cohort_id

Запрос правильный, но слишком громоздкий. Исправлять ошибки и вносить в него изменения — особый тип пытки.

Код можно упростить, если сохранить промежуточные результаты в виде представлений.

Что такое представления и как они работают

Представления (ещё их называют «вью» от английского view) — это сохранённые запросы.

Внешне представление выглядит как ещё одна таблица. Если его использовать в запросе, база найдёт и подставит сохранённый код.

Например, мы сохранили запрос SELECT * FROM some_table в представление my_view. Теперь my_view можно использовать в запросах:

SELECT
FROM my_view
WHERE

Отправляем запрос — база находит и подставляет сохранённый код:

SELECT
FROM (SELECT * FROM some_table)
WHERE

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

Как создать представления

В Бигквери нужно сначала выполнить запрос, затем нажать на кнопку Save View и выбрать название.

В других системах кнопки нет, но зато есть специальный запрос CREATE VIEW.

CREATE VIEW my_view AS
SELECT * FROM some_table

Покажу подробней, как это работает в Бигквери, упрощу огромный запрос из начала статьи.

Сохраню первый запрос в представлении cohort_sizes:

Теперь могу просто запрашивать SELECT * FROM cohort_size, добавлять фильтры, группировки и сортировки.

Повторю процесс для второго запроса: отправлю, получу результат, сохраню представление cohorts.

Теперь заменю вложенные селекты в большом запросе на созданные представления. Смотрите, насколько аккуратней стал запрос:

Было:

SELECT
rev.cohort_id AS cohort_id,
rev.month AS month,
rev.month - rev.cohort_id AS cohort_age,
rev.cohort_revenue / size.cohort_size AS LTV,
rev.cohort_revenue / size.costs * 100 AS ROI
FROM
(
SELECT
reg_month AS cohort_id,
count(*) AS cohort_size
FROM
users
GROUP BY 1
) AS size
INNER JOIN
(
SELECT
users.reg_month AS cohort_id,
orders.month AS month,
sum(orders.sum) AS cohort_revenue
FROM
users INNER JOIN orders
ON users.uid = orders.uid
GROUP BY 1, 2
) AS rev
ON size.cohort_id = rev.cohort_id

Стало:

SELECT
rev.cohort_id AS cohort_id,
rev.month AS month,
rev.month - rev.cohort_id AS cohort_age,
rev.cohort_revenue / size.cohort_size AS LTV,
rev.cohort_revenue / size.costs * 100 AS ROI
FROM
cohort_sizes AS size
INNER JOIN
cohorts AS rev
ON size.cohort_id = rev.cohort_id

На этом всё. Успехов.

Поделитесь со знакомыми аналитиками: