SQL HowTo: «простое» прогнозирование

В «Тензоре» мы разрабатываем множество сервисов для управления бизнесом. А в бизнесе очень часто возникает желание немного «заглянуть в будущее» — спрогнозировать и увидеть на графике значение каких-то величин, которые мы можем только предполагать на основании данных предыдущих периодов. Например, на какую примерно выручку мы сможем рассчитывать в следующем месяце или сколько продуктов стоит закупить в столовую на следующую неделю.

Для решения этой задачи можно строить сложные математические модели и проверять их на «кластерах с бигдатой», но мы попробуем найти вариант попроще — когда есть всего одна метрика, 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';

Пользуйтесь на свой страх и риск.