Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: postgresql

Три способа рассчитать накопленную сумму в SQL

Fri, 10 Dec 2021 19:10:34 +0300

Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.

Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.

Что у нас есть?

Date	Revenue
10.11.2021	1200
11.11.2021	1600
12.11.2021	800
13.11.2021	3000

Что мы хотим найти?

Date	Revenue	Cumulative Revenue
10.11.2021	1200	1200 ↓
11.11.2021	1600	2800↓
12.11.2021	800	3600 ↓
13.11.2021	3000	6600

На графике две этих переменных выглядят следующим образом:

Итак, без лишних слов, давайте приступать к решению задачи.

Способ 1 — Идеальный — Используем оконные функции

Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.

SELECT
	date,
	revenue,
	SUM(revenue) OVER (ORDER BY date asc) as total
FROM leftjoin.daily_sales_sample 
ORDER BY date;

Способ 2 — Хитрый — Решение без оконных функций

Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:

SELECT * 
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
ORDER BY ds1.date, ds2.date;

А его результат:

Date 1	Revenue 1	Date 2	Revenue 2
10.11.2021	1200	10.11.2021	1200
11.11.2021	1600	10.11.2021	1200
11.11.2021	1600	11.11.2021	1600
12.11.2021	800	10.11.2021	1200
12.11.2021	800	11.11.2021	1600
12.11.2021	800	12.11.2021	800
13.11.2021	300	10.11.2021	1200
13.11.2021	300	11.11.2021	1600
13.11.2021	300	12.11.2021	800
13.11.2021	300	13.11.2021	300

А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:

SELECT
	ds1.date,
	ds1.revenue,
	SUM(ds2.revenue) as total
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
GROUP BY ds1.date, ds1.revenue
ORDER BY ds1.date;

Способ 3 — Специфический — Решение с помощью массивов в ClickHouse

Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.

SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)

Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе

Так, мы получим три массива значений:

dates	revs	total
[’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’]	[1200, 1600, 800, 300]	[1200, 2800, 3600, 3900]

Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.

SELECT dates, revs, total FROM
(SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)) as t
ARRAY JOIN dates, revs, total;

Бонус — Оконные функции в Clickhouse

Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи. Можно использовать оконные функции, например функцию runningAccumulate(), которая суммирует значения всех ячеек с первой до текущей.

SELECT date, runningAccumulate(revenue)
  FROM 
  (
    SELECT date, sumState(revenue) AS revenue
    FROM leftjoin.daily_sales_sample
    GROUP BY date 
    ORDER BY date ASC
  )
ORDER BY date

Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа :)

Моделирование LTV в SQL

Mon, 16 Aug 2021 09:32:37 +0300

У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, что такое Retention и как его посчитать. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.

Для материала были взяты данные из одного реального игрового проекта. Нулевой день не отображен для того, чтобы видеть динамику в деталях

В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.

Как посчитать LTV?

В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.

Посмотрим на классический график Retention:

Lifetime — это площадь фигуры под Retention:

Откуда взялись интегралы и площади можно подробнее узнать в этом материале

Значит, чтобы посчитать Lifetime, нужно взять интеграл от функции удержания по времени. Формула приобретает следующий вид:

Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:

При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:

ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.

Строим LTV

Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:

--общее количество юзеров в когорте
with cohort as (
    select count(distinct id) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),

Теперь посмотрим, как ведут себя эти пользователи в последующие 90 дней:

--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),

Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:

По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными агрегатными функциями в PostgreSQL и для известных X и Y по методу наименьших квадратов.

Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:

Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в этом мануале

Из свойства натурального логарифма следует, что:

Наклон считаем аналогичным образом:

Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:

--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),

И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:

lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),

Сравним прогноз на 180 дней с Retention:

Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:

select cumulative_lt as LT,
           cumulative_lt * 83.7 as LTV
from lt

Наконец, построим график LTV на 180 дней:

Весь запрос:

--общее количество юзеров в когорте
with cohort as (
    select count(*) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),
--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),
--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),
lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),
select cumulative_lt as LT,
            cumulative_lt * 83.7 as LTV
from lt