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

image-loader.svg

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

Для решения этой задачи можно строить сложные математические модели и проверять их на «кластерах с бигдатой», но мы попробуем найти вариант попроще — когда есть всего одна метрика, SQL и немного житейской логики.

Как будем считать

Сначала применим эту самую логику и вспомним, что ключевой момент в массовых продажах — периодичность. Домохозяйка в выходные закупает продукты на неделю вперед, рабочий делает крупные покупки после получки раз в месяц, а мороженое мы больше всего любим есть летом, с интервалом примерно в год. В этот же перечень можно добавить квартальную периодичность в работе компаний.

Итак, мы можем заранее предположить какие периоды актуальны для нашего вида бизнеса:

  • 1 week

  • 1 month

  • 3 months

  • 1 year

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

A / B = A' / B'A / B = A' / B'

Тогда, если вывести более формально, где 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 сохранился бы одинаковым на всем диапазоне, а это не только неинтересно, но и не соответствует ожиданиям бизнеса.

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

|six x| + |sin x/2||six x| + |sin x/2|

Конструируем 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])

A = A' - [dt - 7] = sum[dt-1:7] - sum[dt-7:1]A = A' — [dt — 7] = sum[dt-1:7] — sum[dt-7: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';

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

© Habrahabr.ru