SQL для начинающих: вложенные запросы и временные таблицы

RU
Перед вами наша следующая статья о том, как работать с SQL. В этот раз рассмотрим несколько примеров использования временных таблиц и вложенных запросов.
SQL для начинающих: вложенные запросы и временные таблицы
Published
17.02.2022
|
devtodev

Добро пожаловать в следующую статью из цикла про SQL! В предыдущей статье мы считали Transactions и Gross для приложения на двух платформах и получили отдельный результат для каждого приложения.

Вложенные запросы и временные таблицы SQL
Transactions и Gross для приложения «3 in a row»‎. Скриншот из демо devtodev
Но что если мы хотим обобщить его и для каждой метрики иметь только одно значение? Для этого мы будем использовать результат, получившийся с помощью операции union, как таблицу в операторе from. И затем в select вычислять сумму по полю transactions и gross из объединенной таблицы (скриншот выше).
Залогиньтесь на сайте, зайдите в демо и найдите SQL отчёт во вкладке Reports.

select 'Metrics for all projects' as "App"
sum(transactions) as "Transactions"
sum(gross) as "Gross"
from (
select count() as transactions
sum(priceusd) as gross
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date

union all
select
 count
() as "Transactions"
sum(priceusd) as "Gross"
from p104704.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
) as metrics_by_platform

Вложенные запросы и временные таблицы SQL
Результат запроса. Скриншот из демо devtodev

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

) as metrics_by_platform

Такую конструкцию можно использовать во всех операторах, обращающихся к таблицам. Например, в join.

Inner join (select ... from ... where ...) as join_table
on join_table.param = t.param

Метрики по отдельным приложениям и суммарно по всем

Давайте в одном запросе посчитаем суммарные метрики по всем приложениям, а также выведем расшифровку (метрики по каждому из приложенийниже.

select 'Metrics for all projects' as "App"
sum(transactions) as "Transactions"
sum(gross) as "Gross"
from (
select '3 in a row. iOS' as "App"
count() as transactions
sum(priceusd) as gross
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date

union all
select
 
'3 in a row. Android' as "App"
count() as transactions
sum(priceusd) as gross
from p104704.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
) metrics_by_platform

union all
select '3 in a row. iOS' as "App"
count() as transactions
sum(priceusd) as gross
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date

union
select
 
'3 in a row. Android' as "App"
count() as "Transactions"
sum(priceusd) as "Gross"
from p104704.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
order by 
3 desc

Вложенные запросы и временные таблицы SQL
Результат запроса. Скриншот из демо devtodev

Получается довольно громоздкий запрос, в котором мы по два раза обращаемся к каждой из таблиц payments, и к тому же мы два раза написали один и тот же код (при изменении запроса нам придётся вносить изменения в двух местах).

Чтобы избежать этого, мы можем создать представление (Common Table Expression –  CTE), и затем в ходе запроса обращаться к нему несколько раз. Конструкция может содержать в себе сколь угодно сложные запросы и обращаться к другим представлениям. Она выглядит следующим образом:

with temp_table_name as 
(select ... from ...)

Можно сказать, что мы создаем временную таблицу, которая рассчитывается один раз в ходе выполнения запроса даже если вы обращаетесь к ней из разных мест. Использование представлений CTE также сильно упрощает чтение запроса и его последующее редактирование.

Вот как вышеуказанный запрос будет выглядеть с использованием представлений CTE:

with metrics_by_platform as (
select '3 in a row. iOS' as app
count() as transactions
sum(priceusd) as gross
from p102968.payments
where eventtime > current_date - interval '9 day' and eventtime < current_date

union all
select
 
'3 in a row. Android' as app
count() as transactions
sum(priceusd) as gross
from p104704.payments
where eventtime > current_date - interval '9 day' and eventtime < current_date
) 
select 'Metrics for all projects' as "App"
sum(transactions) as "Transactions"
sum(gross) as "Gross"
from metrics_by_platform

union all
select app
transactions
gross
from metrics_by_platform
order by 3 desc

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

Доля пользователей, совершивших максимальное количество платежей (вложенные запросы)

Рассмотрим более сложный пример. Давайте узнаем, какое максимальное число платежей совершено одним пользователем за 7 дней и сколько таких пользователей.

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

У нас есть таблица со всеми платежами пользователей from p102968.payment. Из нее мы посчитаем количество совершенных платежей для каждого из пользователей, сгруппировав их по devtodevid, а потом найдем максимальное число таких платежей с помощью max().

select max(user_payments) as "max_payments"
from (
select devtodevid
, count() 
user_payments
from p102968.payments
where eventtime > current_date - interval '9 day' and eventtime < current_date
group by devtodevid
) as payments_count

Вложенные запросы SQL
Результат запроса. Скриншот из демо devtodev

Осталось узнать, сколько пользователей совершили 12 платежей за это же время. Для этого только что выполненный запрос мы помещаем в фильтр where user_payments = (запрос), который оставит нам только пользователей с соответствующим максимальному количеством платежей. Сам запрос будет возвращать число таких пользователей select count() as "Users" и максимальное количество платежей max(user_payments) as "Max payments count" из таблицы from (...) as  payments_count.

select count(devtodevid) as "Users"
max(user_payments) as "Max payments count"
from (
select devtodevid
, count() 
user_payments
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
group by devtodevid
) as payments_count

where user_payments = (select max(user_payments)
from
(
select devtodevid
, count() 
user_payments
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
group by
 devtodevid) as payments_count
)

SQL max payments
Результат запроса. Скриншот из демо devtodev

При выполнении для каждой строчки из внешнего запроса будет производиться сравнение максимального количества платежей пользователей where user_payments = (...) . В коде мы два раза использовали один и тот же запрос, поэтому давайте оптимизируем его с помощью представления CTE.

with payments_count as (
select devtodevid
, count() 
user_payments
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
group by
 devtodevid
)

select count() as "Users"
max(user_payments) as "Payments count"
from payments_count
where user_payments = (select max(user_payments)
from payments_count

А какова доля пользователей с таким количеством платежей среди всех платящих пользователей? Может быть он всего один и платил?

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

with payments_count as (
select devtodevid
, count() 
user_payments
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
group by
 devtodevid
)

select count() as "Users with max payments count"
max(user_payments) as "Payments count"
round(count()*100::numeric / (select count(distinct devtodevid)
from p102968.payments
where eventtime > current_date - interval '7 day' and eventtime < current_date
2) ||'%' as "% of all payers"
from payments_count
where user_payments = (select max(user_payments)
from payments_count)

SQL вложенный запрос
Результат запроса. Скриншот из демо devtodev

Вложенные запросы внутри select – довольно распространенная практика. Они часто используются для расчета доли от чего-либо, либо отображения информации из другой таблицы без использования join.

P.S.

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

Read more