SQL для начинающих: основные метрики нескольких приложений

RU
Встречайте следующую статью из цикла про SQL! Вы узнаете, как проводить сквозную аналитику по нескольким приложениям с помощью оператора Union.
SQL для начинающих: основные метрики нескольких приложений
Published
17.12.2021
|
devtodev

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

Настало время рассказать об операции union. В структуре devtodev приложения на различных платформах должны находиться в разных проектах, объединённых в один спейс. Так, например, в нашем демо есть два приложения 3 in a row: на Android и на iOS. У каждого из них будут свои таблицы с пользователями, свои ивенты (или одинаковые), свои параметры. В случае, если необходимо сделать сквозную аналитику по нескольким приложениям сразу, нам потребуется вертикальное объединение таблиц union.
Залогиньтесь на сайте, зайдите в демо и найдите SQL отчёт во вкладке Reports.

Объединение таблиц Union

Оператор union объединяет данные из нескольких таблиц в одну, при этом оставляет только уникальные строки (как бы добавляет к строкам первой таблицы строки из следующей). При объединении двух таблиц, в которых есть абсолютно идентичные строки, дубликат не будет включен в результат (операция distinct по умолчанию).

Если вам нужны все данные, включая дубликаты (например, вы считаете количество совершенных событий), то лучше использовать union all – данный оператор вернет все строки из объединяемых таблиц.

select имена_столбцов (1...N)
from имя_таблицы
union
select имена_столбцов (1...N)
from имя_таблицы

Существует несколько условий для работы оператора union:

  • При объединении количество столбцов во всех таблицах должно совпадать, иначе возникнет ошибка.

  • Типы данных этих полей также должны совпадать в каждом запросе.

  • Order by следует применять к результату объединения и размещать только в конце запроса.
  • Количество платежей и доход по платформам

    В одной из предыдущих статей мы уже рассказывали, как посчитать метрики Gross и Transactions. А теперь просто берём количество событий payments и сумму параметра priceusd этих событий из таблицы p102968.payments (для iOS приложения) и из таблицы p104704.payments (для Android приложения), а затем объединяем результаты с помощью union. Мы также ограничили период совершения платежей последней неделей.

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

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

Результатом выполненного запроса будут две строки: первая – метрики по iOS приложению, вторая – по Android приложению. Но если вы добавите в запрос сортировку или будете его ещё как-либо использовать, то вы не сможете идентифицировать метрики и приложение.

Поэтому в select мы можем указать название приложения и обозначить его “App”.

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

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

В итоге, не боясь потерять связь с приложением, мы можем сортировать эти данные или использовать в дальнейших расчетах.

Использование переменных

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

eventtime > current_date - interval '7 day'and eventtime < current_date

Если нам потребуется изменить временной промежуток (30 дней вместо 7), то придётся делать это несколько раз. А в случаях объединения десятка таблиц, можно запросто пропустить изменение условия в одной из них и получить некорректный результат.

Для таких случаев, а также для улучшения читаемости запроса, можно предопределять переменные. Объявляются переменные следующим образом:

DECLARE @LOCAL_VARIABLE data_type [ = value ] }

В следующем запросе мы считаем различные монетизационные метрики для когорты пользователей, установившей приложение в течение последних семи дней. Для этого мы определили переменную @install_interval типа interval и задали начальное значение в 7 дней, а затем в каждом отдельном запросе фильтровали пользователей where created > current_date - @install_interval.

Остальные переменные использовались как для фильтрации в where, так и для обозначения самого фильтра, например, select 'Users paid ' || @payments_count ||' and more times'.

declare 
@date_from date = '2021-08-01',
@install_interval interval = '7 day',
@payments_count int = 2,
@itemlike text = 'offer%',
@item_list text[] '{"offer1", "offer2", "special offer"}';
 
select 'New users' as "Metric"
count(distinct devtodevid)
from p104704.users
where created > current_date - @install_interval and created < current_date
 
union all
select 
'Paying users'
count(distinct devtodevid)
from p102968.payments
where created > current_date - @install_interval and created < current_date
 
union all
select 'Users paid' || @payments_count || 'and more times'
count(distinct devtodevid)
from p102968.payments
where created > current_date - @install_interval and created < current_date and paymentcount >=@payments_count
 
union all
select 'Users bought items like' || @itemlike
count(distinct devtodevid)
from p102968.payments
where created > current_date - @install_interval and created < current_date and product like =@itemlike
 
union all
select 'Users bought items from the list' || @itemlike
count(distinct devtodevid)
from p102968.payments
where created > current_date - @install_interval and created < current_date and product = any (@item_list)
order by 2 desc
оператор union
Результат запроса. Скриншот из демо devtodev

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

Если мы хотим сохранить порядок строк в отчете, например, в том порядке, как объединяются таблицы, можно добавить в select каждого запроса еще одно поле и отсортировать по нему:

select as "№"

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

P.S.

В следующей статье мы рассмотрим ещё больше примеров использования union и join конструкций, а также расскажем, как использовать результаты одного запроса внутри другого (вложенные запросы).

Read more