В "Тензоре" мы разрабатываем множество сервисов для управления бизнесом. А в бизнесе очень часто возникает желание немного "заглянуть в будущее" - спрогнозировать и увидеть на графике значение каких-то величин, которые мы можем только предполагать на основании данных предыдущих периодов. Например, на какую примерно выручку мы сможем рассчитывать в следующем месяце или сколько продуктов стоит закупить в столовую на следующую неделю.
Для решения этой задачи можно строить сложные математические модели и проверять их на "кластерах с бигдатой", но мы попробуем найти вариант попроще - когда есть всего одна метрика, SQL и немного житейской логики.
Как будем считать
Сначала применим эту самую логику и вспомним, что ключевой момент в массовых продажах - периодичность. Домохозяйка в выходные закупает продукты на неделю вперед, рабочий делает крупные покупки после получки раз в месяц, а мороженое мы больше всего любим есть летом, с интервалом примерно в год. В этот же перечень можно добавить квартальную периодичность в работе компаний.
Итак, мы можем заранее предположить какие периоды актуальны для нашего вида бизнеса:
1 week
1 month
3 months
1 year
И для каждого такого периода мы можем предположить, что значение на следующий день с большой вероятностью будет сохранять отношение между суммой значений за текущий и предыдущий период:
Тогда, если вывести более формально, где d
- это период в днях:
1. A / B = A' / B'
2. ([dt] + sum[dt-1 .. dt-d+1]) / sum[dt-d .. dt-2*d+1] =
= sum[dt-1 .. dt-i] / sum[dt-d-1 .. dt-2*d]
3. [dt] = sum[dt-1 .. dt-d] / sum[dt-d-1 .. dt-2*d] * sum[dt-d .. dt-2*d+1] -
- sum[dt-1 .. dt-d+1]
Как можно увидеть, для расчета по данному алгоритму нам понадобятся данные за "удвоенный период" и еще один день (dt - 2 * d + 1
).
Наложение периодов
Если бы мы рассчитывали таким алгоритмом итеративно значения на несколько дней (или даже на год) вперед, то коэффициент A / B
сохранился бы одинаковым на всем диапазоне, а это не только неинтересно, но и не соответствует ожиданиям бизнеса.
Поэтому вычислим значение конкретного дня просто как среднее по всем периодам. Тут можно добавить весовые коэффициенты в зависимости от длины периода, но в нашей примитивной модели не будем рассматривать такой вариант.
Конструируем SQL-запрос
Для начала сгенерируем немного случайных данных за предыдущие пару лет:
CREATE TABLE tbl_fact AS
SELECT
dt::date
, random() v
FROM
generate_series(
'2021-12-31'::date - '2 year 1 day'::interval -- 2 года и 1 день назад
, '2021-12-31'::date
, '1 day'::interval
) dt;
CREATE INDEX ON tbl_fact(dt);
Да, это даст нам не вполне корректные значения результата прогнозирования, но поможет отладить алгоритм, который вы сможете повторить уже на более реальных данных.
"Си, бейсик, паскаль... русский со словарем"
Поскольку алгоритм у нас итеративный, то на SQL для этого мы будем использовать рекурсивный запрос. В теории, можно было бы вывести некую мегасложную рекуррентную формулу, но мы будем действовать проще.
Согласно приведенной выше формуле, нам понадобятся суммы за d последовательных дней периода до конкретной даты и значение в этой дате. Поэтому сначала Преобразуем интервалы в количество дней:
periods AS (
SELECT
*
, extract(epoch FROM p)::integer/86400 d -- переводим интервалы в дни
FROM
unnest('{1 week,1 month,3 months,1 year}'::interval[]) p
)
Чтобы не извлекать повторно эти данные каждый раз из таблицы и пересчитывать заново, сложим их в json-словарь в формате {[dt:d] : v}
:
src AS (
SELECT
jsonb_object(
array_agg(f.dt || ':' || f.d)::text[]
, array_agg(f.v)::text[]
) s -- свертка в {[dt:d] : v}
FROM
periods
, LATERAL (
SELECT
dt
, 1 d
, v -- данные за [dt]
FROM
tbl_fact
UNION ALL
SELECT
dt
, d
, coalesce(sum(v) OVER(ROWS BETWEEN d-1 PRECEDING AND CURRENT ROW), 0) v -- данные за [dt-d+1 .. dt]
FROM
tbl_fact
) f
)
Возьмем типичный шаблон для итеративной работы со словарем, аналогичный рассмотренному в статье SQL HowTo: пишем while-цикл прямо в запросе, или «Элементарная трехходовка»:
T AS (
SELECT
'2022-01-01'::date - 1 dt -- стартовая дата
, NULL::double precision v
, (TABLE src) s -- начальное состояние словаря
UNION ALL
SELECT
dt + 1
, X.v
, X.s
FROM
T
, LATERAL (
-- ... magic!
) X
WHERE
dt < '2022-12-31' -- финальная рассчитываемая дата
)
То все присказка была... Пора реализовать сам алгоритм вычисления следующего значения. Сначала потренируемся вычислять значение дня для первого шага, без рекурсии, а для этого продолжим вывод значения с использованием уже агрегированных данных:
3. [dt] = sum[dt-1 .. dt-d] / sum[dt-d-1 .. dt-2*d] * sum[dt-d .. dt-2*d+1] -
- sum[dt-1 .. dt-d+1]
4. [dt] = sum[dt-1:d] / sum[dt-d-1:d] * sum[dt-d:d] - (sum[dt-1:d] - sum[dt-d:1])
Тут мы заменили сумму на интервале в d - 1
день как разность сумм на d
-интервале и единичном. К счастью, мы ранее позаботились, чтобы они у нас были, и теперь наш модельный запрос выглядит так:
SELECT
X.v
, Y.s
FROM
(
SELECT
'2021-12-31'::date dt
, (TABLE src) s
) _
, LATERAL(
SELECT
greatest(avg(
(s ->> ((dt - 1) || ':' || d))::double precision -- A'
/ (s ->> ((dt - d - 1) || ':' || d))::double precision -- / B'
* (s ->> ((dt - d ) || ':' || d))::double precision -- * B
- (s ->> ((dt - 1) || ':' || d))::double precision -- - A'
+ (s ->> ((dt - d ) || ':' || 1))::double precision -- + [dt-d]
), 0) v -- прогнозируемое значение не может быть орицательным
FROM
periods
) X
, LATERAL(
SELECT
jsonb_object(
array_agg((dt + 1) || ':' || d)::text[]
, array_agg(
(s ->> ((dt - 1) || ':' || d))::double precision -- A'
- (s ->> ((dt - d) || ':' || 1))::double precision -- - [dt-d]
+ v -- + v
)::text[]
) s -- словарь рассчитанныx сумм
FROM
periods
) Y
Обратите внимание, что сначала мы вычислили по всему набору периодов ожидаемое значение v
, и только потом через LATERAL
использовали его для пересчета сумм по каждому периоду.
Теперь осталось собрать все в один запрос:
WITH RECURSIVE periods AS (
SELECT
*
, extract(epoch FROM p)::integer/86400 d
FROM
unnest('{1 week,1 month,3 months,1 year}'::interval[]) p
)
, src AS (
SELECT
jsonb_object(
array_agg(f.dt || ':' || f.d)::text[]
, array_agg(f.v)::text[]
) s -- свертка в {[dt:d] : v}
FROM
periods
, LATERAL (
SELECT
dt
, 1 d
, v -- данные за [dt]
FROM
tbl_fact
UNION ALL
SELECT
dt
, d
, coalesce(sum(v) OVER(ROWS BETWEEN d-1 PRECEDING AND CURRENT ROW), 0) v -- данные за [dt-d+1 .. dt]
FROM
tbl_fact
) f
)
, T AS (
SELECT
'2022-01-01'::date - 1 dt -- стартовая дата
, NULL::double precision v
, (TABLE src) s -- начальное состояние словаря
UNION ALL
SELECT
dt + 1
, X.v
, X.s
FROM
T
, LATERAL (
SELECT
X.v
, T.s || Y.s s
FROM
(
SELECT
greatest(avg(
(s ->> ((dt - 1) || ':' || d))::double precision -- A'
/ (s ->> ((dt - d - 1) || ':' || d))::double precision -- B'
* (s ->> ((dt - d ) || ':' || d))::double precision -- B
- (s ->> ((dt - 1) || ':' || d))::double precision -- A' - [dt-d]
+ (s ->> ((dt - d ) || ':' || 1))::double precision
), 0) v -- прогнозируемое значение не может быть отрицательным
FROM
periods
) X
, LATERAL(
SELECT
jsonb_object(
array_agg((dt + 1) || ':' || d)::text[]
, array_agg(
(s ->> ((dt - 1) || ':' || d))::double precision
- (s ->> ((dt - d) || ':' || 1))::double precision
+ v
)::text[]
) s -- словарь рассчитанныx сумм
FROM
periods
) Y
) X
WHERE
dt < '2022-12-31' -- финальная рассчитываемая дата
)
SELECT
dt
, v
FROM
T
WHERE
dt >= '2022-01-01';
Пользуйтесь на свой страх и риск.