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;

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