Приветствуем вас в следующей статье из цикла «SQL для начинающих»! В более ранних статьях мы рассказывали о том, с чего начать обучение языку запросов, как вычислять количество активных пользователей, посчитать средний чек и ARPPU с помощью SQL. А теперь предлагаем вам научиться заполнять пустые даты на графике и формировать гистограмму распределения.
Чтобы начать, зайдите в демо devtodev и найдите SQL отчёт во вкладке Reports.
Представим, что нам предстоит построить график совершения какого-либо события, которое происходит в приложении довольно редко, а иногда оно не происходит ни разу за день.
Давайте обратимся к графику покупок пользователей из Италии за последние 14 дней (с 01/10 по 14/10) с помощью следующего запроса:
select eventtime :: date as days
, count() as "Payments count by users from Italy"
from p102968.payments
where eventtime > current_date - interval '14 day'and eventtime < date_trunc ('day', current_date)
and country = 'IT'
group by 1
order by 1
На первый взгляд кажется, что всё хорошо, но если внимательнее посмотреть на ось дат, то можно увидеть, что некоторые из них отсутствуют. Всё потому, что с 5/10 по 7/10 не было совершено ни одного платежа, и запрос не смог ничего сгруппировать в эти дни. Аналогично и для периода с 11/10 по 14/10.
Такие графики вводят пользователя в заблуждение, потому что не всегда можно заметить отсутствующие даты. Чаще всего это происходит, когда пользователь применяет много фильтров, условиям которых соответствует малое количество данных. Поэтому лучше использовать generate_series() при сохранении запроса на дашборд, а уже затем применять фильтры.
generate_series для дат
generate _series(start, stop, step) – это функция, возвращающая множество строк и принимающая два обязательных аргумента start и stop, и один опциональный – step.
Самый простой пример такой функции, возвращающей список чисел от 1 до 5:
select generate_series(1, 5)
Кроме чисел, эта функция в качестве аргументов может принимать даты и генерировать список дат. При генерации всегда используйте интервальную переменную в качестве step.
select generate_series(
ut('2021-10-01')
, ut('2021-10-14')
, '1 day'::interval)::date as days
Данный запрос вернул нам все даты, находящиеся от ut('2021-10-01') до ut('2021-10-14') с интервалом в один день – '1 day'::interval.
Другие примеры генерации дат и времени:
- 5-минутные интервалы: select generate_series(ut('2021-10-01'), ut('2021-10-14'), '5 min'::interval)::timestamp
- 1-часовые интервалы: select generate_series(ut('2021-10-01'), ut('2021-10-14'), '1 hour'::interval)::timestamp
- 1-дневные интервалы: select generate_series(ut('2021-10-01'), ut('2021-10-14'), '1 day'::interval)::date
- Недельные интервалы (Обратите внимание на использование функции date_trunc в качестве start и stop аргумента. Это сделано для того, чтобы сгенерированные недели всегда начинались с понедельника, независимо от дня недели current_date): select generate_series(date_trunc('week',current_date-interval '2 week'), date_trunc('week',current_date), '1 week'::interval)::date
- Месячные интервалы: select generate_series(date_trunc('month', current_date-interval '3 month'), date_trunc('month',current_date), '1 month'::interval)::date
Заполнение пустых дат в графике
Чтобы использовать generate_series() в нашем запросе, мы отдельно запишем генерацию дат в CTE (Common Table Expression). Затем, с помощью left join, к сгенерированному списку дат добавим таблицу с рассчитанными данными в начале статьи:
with generated_days as (
select generate_series(ut('2021-10-01'), ut('2021-10-10'), '1 day'::interval)::date as days
)
select days, "Payments count by users from Italy"
from generated_days
left join (
select eventtime :: date as days
, count() as "Payments count by users from Italy"
from p102968.payments
where eventtime > current_date - interval '14 day'and eventtime < date_trunc ('day', current_date)
and country = 'IT'
group by 1
) t using (days)
order by 1
Конечно, теперь график не такой красивый – у него есть пустые, незаполненные значения для некоторых дней. Однако такой график гораздо правдивее.
Если вы хотите улучшить его вид, можете использовать функцию coalesce() и проставить значение «0» для незаполненных полей.
Результат:
select days, coalesce("Payments count by users from Italy",0)
Читайте также: SQL для начинающих: вложенные запросы и временные таблицы
Построение гистограммы распределения ARPPU
Теперь проанализируем ARPPU – средний доход от одного платящего пользователя. Попробуем понять, какие суммы мы получаем от платящих пользователей, и как их можно сегментировать по «малькам» и «китам».
Считаем ARPPU пользователей:
select devtodevid, sum(priceusd):: int ARPPU
from p102968.payments
where created between current_date - interval '2 month' and current_date - interval '1 month'and eventtime - created < interval '1 month'
group by 1
order by 2 desc
В условии where мы использовали eventtime - created < interval '1 month', что возвращает только совершенные в течение месяца после установки платежи. Таким образом все пользователи будут в равных условиях. Также мы привели все суммы платежей пользователей к целочисленному значению sum(priceusd)::int ARPPU.
Теперь сформируем интервалы для гистограммы. Мы можем задать их сами, например, от 0 до 1000 с интервалом в 10 – generate_series(0, 1000, 10). Но зачем нам интервалы, в которых не будет ни одного пользователя? Давайте сгенерируем их в зависимости от получившихся значений ARPPU.
Читайте также: 4 способа сегментации платящих пользователей
Для этого расчет ARPPU помещаем в CTE ARPPU_table. Обращаемся к ней, генерируя интервал от 0 до максимального значения ARPPU – generate_series(0, max(ARPPU), 10) as f. Второй интервал t генерируется для ограничения интервала справа, чтобы сделать интервалы вида 0-10, 10-20. При этом t всегда будет больше значения f на 10.
with ARPPU_table as (
select devtodevid, sum (priceusd):: int ARPPU
from p102968.payments
where created between current_date - interval '2 month' and current_date - interval '1 month'and eventtime - created < interval '1 month'
group by 1
order by 2 desc
)
, intervals as (
select generate_series(0, max(ARPPU), 10) as f
, generate_series(10, max(ARPPU)+10, 10) as t
from ARPPU_table
)
Таблица intervals будет содержать следующие значения до максимально подсчитанного значения ARPPU:
Осталось объединить данные запросов. Для этого выбираем данные из таблицы intervals, присоединяем к ней таблицу ARPPU_table с помощью left join таким образом, чтобы каждый пользователь из ARPPU_table попал в собственную строку со значением ARPPU>=f and ARPPU <t. В select, для читаемости, мы объединили f и t через конкатенацию, в результате чего интервалы стали выглядеть как $0 - $10, $10 - $20 и т.д.
select '$' || f || '- $' || t as intervals, count() as "Users count"
from intervals
left join ARPPU_table on ARPPU >= f and ARPPU < t
group by f,t
order by f
Наглядно видно, как распределяются суммы платежей пользователей, и можно предположить, что «мальками» для данного приложения будут пользователи, платящие $0 - $10, «дельфины» платят $10 - $50, «киты» – $50 - $100 и т.д.
Теперь вы знаете, как заполнять пустые даты на графиках и строить гистограммы распределения. В следующей статье мы расскажем, как рассчитывать долю дохода, которую приносит каждый продукт.
