[PostgreSQL, SQL, Администрирование баз данных, ERP-системы] SQL HowTo: рейтинг-за-интервал

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
11-Янв-2021 17:31

Цитировать

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

Но просто "самые" за весь доисторический период обычно неинтересны - продал ты 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;

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

Теги для поиска: #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_erpsistemy (ERP-системы), #_postgresql, #_sql, #_sql_tips_and_tricks, #_rejting (рейтинг), #_interval (интервал), #_sbis (сбис), #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
), #_erpsistemy (
ERP-системы
)

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 18:16
Часовой пояс: UTC + 5