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

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

Но просто «самые» за весь доисторический период обычно неинтересны — продал ты 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; -- всегда добавляем в сумму
8388f4584286b7cfc991e59efdd9651f.png

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

-- "новый" месяц актуальности
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);
a85fb8af735cf7b965f65ad59a400102.png

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

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

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

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

© Habrahabr.ru