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

RU
Из этой статьи вы узнаете, как заполнить пустые даты на графике и сформировать гистограмму распределения с помощью SQL.
SQL для начинающих: как генерировать даты и числа
Published
25.03.2022
|
devtodev

Приветствуем вас в следующей статье из цикла «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
SQL заполнение пустых дат
Результат запроса. Скриншот из демо devtodev

На первый взгляд кажется, что всё хорошо, но если внимательнее посмотреть на ось дат, то можно увидеть, что некоторые из них отсутствуют. Всё потому, что с 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(15)

SQL функция generate_series
Функция generate_series. Скриншот из демо devtodev

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

select generate_series(
ut('2021-10-01')
, ut('2021-10-14')
'1 day'::interval)::date as days

функция generate_series
Функция generate_series. Скриншот из демо devtodev

Данный запрос вернул нам все даты, находящиеся от 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
using (days)
order by 1

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

Конечно, теперь график не такой красивый – у него есть пустые, незаполненные значения для некоторых дней. Однако такой график гораздо правдивее.

Если вы хотите улучшить его вид, можете использовать функцию 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

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

В условии 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 всегда будет больше значения на 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)+1010) as t
from ARPPU_table
)

Таблица intervals будет содержать следующие значения до максимально подсчитанного значения ARPPU: 

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

Осталось объединить данные запросов. Для этого выбираем данные из таблицы intervals, присоединяем к ней таблицу ARPPU_table с помощью left join таким образом, чтобы каждый пользователь из ARPPU_table попал в собственную строку со значением ARPPU>=f and ARPPU <t. В select, для читаемости, мы объединили f и t через конкатенацию, в результате чего интервалы стали выглядеть как $0 - $10, $10 - $20 и т.д.

select '$' || || '- $' || t as intervals, count() as "Users count"
from intervals
left join ARPPU_table on ARPPU >= and ARPPU t
group by f,t
order by f

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

Наглядно видно, как распределяются суммы платежей пользователей, и можно предположить, что «мальками» для данного приложения будут пользователи, платящие $0 - $10, «дельфины» платят $10 - $50, «киты» – $50 - $100 и т.д.

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

Read more