SQL HowTo: рейтинг-за-интервал

Одним из наиболее частых требований-"хотелок" бизнеса является построение всяких разных рейтингов - "самые оборотистые клиенты", "самые продаваемые позиции", "самые активные сотрудники", … - любимая тема разных дашбордов.

Например, в нашем решении для автоматизации ресторанов и кафе Presto очень популярен такой:

Но просто "самые" за весь доисторический период обычно неинтересны - продал ты 3 года назад вагон валенок, и теперь он у тебя в "самых" продажах вечно. Поэтому обычно хочется видеть "топ" на каком-то ограниченном последнем интервале - например, "за последний год" (точнее, за последние 12 календарных месяцев).

Традиционно, есть два подхода к этой задаче: запрос по требованию по "сырым" данным или предварительная агрегация. И если "просто посчитать" такой отчет по первичке - упражнение для SQL-новичка, но очень "тяжелое" для производительности СУБД, то вариант сделать так, чтобы он строился практически мгновенно при большом количестве активных аккаунтов независимых бизнесов, как у нас в СБИС, без необходимости пересчитывать агрегированную статистику каждого 1-го числа месяца судорожно по всем клиентам - интересная задача.

Структура хранения

Для начала поймем, что "быстро" может быть только в том случае, когда мы можем просто пройти по "верхушке" нужного индекса и извлечь искомые TOP-10 записей - без всяких суммирований и пересортировок.

То есть для решения задачи нам достаточно таблицы с единственным индексом (рассмотрим только вариант сортировки по сумме, для количества все будет аналогично):

CREATE TABLE item_stat(
  item -- товар
    integer
, sum
    numeric(32,2)
);
CREATE INDEX ON item_stat(sum DESC);

Наполнять ее данными мы можем легко и просто - инкрементом в триггере при проведении продажи. Но как все-таки сделать эффективное "вычитание" данных при завершении месяца?..

"Нужно больше золота"

Чтобы быстро что-то вычесть, нужно четко понимать, что именно.

В нашем случае - это продажи за 12-й месяц "назад" при пересечении границы. То есть наступил июнь - из общих счетчиков нужно вычесть все данные за июнь прошлого года. А для этого их нам нужно хранить отдельно от "годичных", из-за чего таблица принимает структуру:

CREATE TABLE item_stat(
  interval_id -- 0 - текущие счетчики, 202001 - январь 2020, 202002 - февраль, ...
    integer
, item
    integer
, sum
    numeric(32,2)
, UNIQUE(interval_id, item)
);
CREATE INDEX ON item_stat(interval_id, sum DESC);

Момент обновления

Чтобы понять, что вот прямо сейчас надо "вычесть" какой-то месяц, достаточно оперировать единственным дополнительным параметром типа "месяц последней актуализации рейтинга продаж". Хранить его можно даже в служебной записи в этой же таблице (если это не помешает Foreign Key, который вы можете захотеть добавить на item):

INSERT INTO item_stat(
  interval_id
, item
, sum
)
VALUES
  (0, 0, 202012) -- служебный ключ (0, 0), значение - 2020'12 вместо суммы
ON CONFLICT(interval_id, item)
  DO UPDATE SET
    sum = EXCLUDED.sum; -- всегда заменяем значение

Теперь при операции над продажей (отгрузка/аннулирование) вызываем, можно асинхронно, инкремент/декремент сразу для двух записей - "годичной" и текущего месяца:

INSERT INTO item_stat(
  interval_id
, item
, sum
)
VALUES
  (202001, 1, 100) -- + в рейтинг за январь 2020
, (     0, 1, 100) -- + в текущий рейтинг
ON CONFLICT(interval_id, item)
  DO UPDATE SET
    sum = item_stat.sum + EXCLUDED.sum; -- всегда добавляем в сумму

Если текущий месяц операции разошелся с месяцем из параметра, асинхронно стартуем пересчет "годовых" значений, вычитая показатели за ставшие избыточными месяцы, и переактуализируем значение параметра:

-- "новый" месяц актуальности
WITH next AS (
  SELECT 202101
)
-- предыдущий месяц актуальности
, prev AS (
  SELECT
    sum::integer
  FROM
    item_stat
  WHERE
    (interval_id, item) = (0, 0)
)
-- все продажи за период, ставший неактуальным, в разрезе товаров
, diff AS (
  SELECT
    item
  , sum(sum) sum
  FROM
    item_stat
  WHERE
    interval_id BETWEEN (TABLE prev) - 100 AND (TABLE next) - 100
  GROUP BY
    1
)
UPDATE
  item_stat dst
SET
  sum = dst.sum - diff.sum
FROM
  diff
WHERE
  (dst.interval_id, dst.item) = (0, diff.item);

UPDATE
  item_stat
SET
  sum = 202101
WHERE
  (interval_id, item) = (0, 0);

При построении отчета

Если текущий месяц совпадает с месяцем из параметра, то все значения в "годичном" интервале актуальны - просто выводим топ по индексу:

SELECT
  *
FROM
  item_stat
WHERE
  interval_id = 0 -- текущий "годичный" интервал
ORDER BY
  sum DESC
LIMIT 10;

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