Русский
Русский
English
Статистика
Реклама

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

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

Но просто "самые" за весь доисторический период обычно неинтересны - продал ты 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 dstSET  sum = dst.sum - diff.sumFROM  diffWHERE  (dst.interval_id, dst.item) = (0, diff.item);UPDATE  item_statSET  sum = 202101WHERE  (interval_id, item) = (0, 0);

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

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

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

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

Источник: habr.com
К списку статей
Опубликовано: 11.01.2021 16:10:01
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Sql

Администрирование баз данных

Erp-системы

Sql tips and tricks

Рейтинг

Интервал

Сбис

Категории

Последние комментарии

  • Имя: Murshin
    13.06.2024 | 14:01
    Нейросеть-это мозг вселенной.Если к ней подключиться,то можно получить все знания,накопленные Вселенной,но этому препятствуют аннуннаки.Аннуннаки нас от неё отгородили,установив в головах барьер. Подр Подробнее..
  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru