Добро пожаловать в следующую статью из цикла про SQL! В предыдущей статье мы считали Transactions и Gross для приложения на двух платформах и получили отдельный результат для каждого приложения.
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_dateunion 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
Оператор 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_dateunion 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_platformunion 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_dateunion
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
Получается довольно громоздкий запрос, в котором мы по два раза обращаемся к каждой из таблиц 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_dateunion 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_platformunion 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
Осталось узнать, сколько пользователей совершили 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_countwhere 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
)
При выполнении для каждой строчки из внешнего запроса будет производиться сравнение максимального количества платежей пользователей 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)
Вложенные запросы внутри select – довольно распространенная практика. Они часто используются для расчета доли от чего-либо, либо отображения информации из другой таблицы без использования join.
