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;

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

    Тензор
    Разработчик системы СБИС

    Comments 26

      0

      А не о кубах не думали? Пусть каждую ночь куб обновляется, а там уж можно анализировать и смотреть цифры по периодам, например YoY, в сравнении с предыдущим периодом, текущий период и т. д.

        0
        Вот как раз «каждую ночь обновляется» — это зло в наших условиях: независимых клиентов много (миллионы), ждать «пока куб обновится» в Мск-ночи они не особо готовы во Владивостоке.
          0

          Нет, ждать-то не надо — куб в это время остается доступен для запросов. У меня есть таблица с 40 млн транзакций в месяц, и вот по ней такие adhoc-запросы, что вы показали, делать за приемлемое время невозможно

            0
            Я имел в виду не «не иметь доступа к статистике», а «не иметь актуальной статистики» в период пересчета.
        0
        Я так понимаю, в MSSQL проблема решается просто построением колоночного индекса по таблице.
          0
          Если я правильно понял, и речь про columnstore, то это может существенно ускорить само вычисление агрегатов, но чтобы найти «топ» их все равно придется вычислить и отсортировать — это все-таки проигрывает линейному чтению индекса.
          +1
          Мне кажется вы изобрели обычные материализованные представления, но где-то посередине всё сильно усложнили. Это же просто суммарные обороты по месяцам — а потом с этими оборотами можно делать что угодно — брать сумму за последние 12 месяцев, полгода, 3 года.
          Не знаю как это реализовано в PostgreSQL, но в MSSQL и Oracle это нормально получается. С десятками миллионов проводок за год всё работает быстро и данные всегда гарантированно актуальные.
            +1
            В PG примерно это решается REFRESH MATERIALIZED VIEW CONCURRENTLY, но таки они не учитывают «текущие» изменения и требуют солидных ресурсов в моменте перегенерации.
            0
            Для решения проблемы вагона валенков лучше бы подошла оконная функция.
            Ограничение интервала — частный случай оконной функции, но довольно топорный.
            Не путать с оконными функциями SQL
              –3
              Господи наконец-то статья о рейтингах. Ты мой герой!
              Я тут уже год всем спрашиваю как устроены рейтинги по типу hot / trend и т.д. никто нихрена не знает… не формул ничего, сидят 10 лет на хабре читают, ничего не знаю, ничего подсказать не могут…
              Подписался лайк, жду новый статей в этом направлении
                0
                А можно просто взять Кликхаус
                SELECT CounterID, count(*) as last_years_hits
                FROM hits_100m_obfuscated
                WHERE EventDate > today() - INTERVAL 20 year
                GROUP BY CounterID
                ORDER BY last_years_hits DESC
                limit 20

                В песочнице на 100 миллионах записей не тормозит. Интервал любой по вкусу.
                https://play.clickhouse.tech
                  0
                  Технически, там используется MergeTree, что в модели статьи аналогично «суммировать от хранимых помесячных агрегатов»:

                  CREATE TABLE datasets.hits_100m_obfuscated (`WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16,
                    ... `CLID` UInt32) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID), EventTime)
                    SAMPLE BY intHash32(UserID) SETTINGS index_granularity_bytes = 1048576, index_granularity = 8192

                  Конечно, много быстрее, чем по сырым данным, но медленнее простого Index Scan.
                  Почему-то EXPLAIN SELECT в песочнице не работает, чтобы окончательно убедиться.
                    0
                    Технически, там используется MergeTree, что в модели статьи аналогично «суммировать от хранимых помесячных агрегатов»:

                    Вы не пробовали документацию открывать? MergeTree не про «суммировать от хранимых помесячных агрегатов», вообще ничего общего.
                    Это просто сырые данные без аггрегирования.
                      0
                      MergeTree с посуточным секционированием и колоночным хранением дает возможность быстрого вычисления агрегатов в разрезе каждой секции. После этого агрегаты всех секций интервала суммируем, сортируем и обрезаем. Все так?
                        0
                        Не совсем. Партиции дают возможность сразу исключить и не обратывать те которые не попали в фильтр. Все попавшие честно сканируются по индексу и считаются.

                        Никаких предрассчитанных аггрегатов или чего-то подобного нет.
                          0
                          Но индекс-то — колоночный. Грубо, там будет записано «дальше в столбце записано 100500 раз значение CounterID=123» (или как там реализовано RLE в деталях), что позволяет прочитать только такую заголовочную запись и уже иметь готовый агрегат.
                            0
                            Не попали. Индекс Кликхауса говорит что в следующих N блоках нет нужных данных.

                            Те блоки в которых нужные данные есть надо читать и считать аггрегат.
                            Поколоночно, естесвенно. База колоночная.
                  0
                  в песочнице на 4х кластерной системе kafka ваш запрос в KSQL обрабатывается почти моментально (данных около 2 тб)
                    0
                    ClickHouse Playground gives the experience of m2.small Managed Service for ClickHouse instance (4 vCPU, 32 GB RAM) hosted in Yandex.Cloud.
                    От меня: vCPU — это гипертрединг ядра. 2 реальных ядра.

                    Согласитесь, разница принципиальная? По деньгам уходящим на железо, ественно.
                      0
                      Это все удобно, когда требуется вычислить абстрактные неизвестные заранее агрегаты. Но если они определены заранее, то чтение топа может занять всего несколько килобайт данных, для чего хватит существенно более слабой машины, даже если дашборд смотрят сотни раз в час.
                        0
                        Приходит к вам однажды аналитик.
                        А потом важный клиент.
                        А потом продакт с новой идеей.

                        Писать кастомное решение под каждого гораздо сложнее и дороже чем просто обычный запрос. Аналитик так и вообще сам себе запрос написать может.
                          0
                          Это ключевое отличие между заказной и массово-тиражной разработкой.

                          Если уж мы предлагаем использовать отчет в контексте готового бизнес-процесса, под который заточен и интерфейс приложения, то и работать он должен предельно быстро, создавая минимальную нагрузку. А если хочется «покрутить куб» в свое удовольствие, то там и подождать пользователь может.
                            0
                            Но ведь можно и то и другое и с маслом тоже можно.

                            Стандартные тайм ту маркет, стоимость разработки, гибкость.
                            Когда нужная и не совсем упоротая фича делается оперативно, без особых багов и не вызывает переделку половины приложения.

                            Гибкие отчеты показывающие то что хочется пользователю за адекватное время на адекватном железе, вместо прибитых гвоздями отчетов показывающих то что вы считаете нужным это полезная фича.
                            Без нее вы обречены или отставать от соседа или тратить невообразимо дорогое время программистов на постоянные доделки.
                            Пресеты для типичных сценариев конечно нужны, но не только они.
                              0
                              Все правильно. Только не «вместо», но «вместе».

                              Оперативный отчет на основном пути работы — предельно быстр и оптимизирован, со сложными пользовательскими фильтрами/группировками — в балансе ожиданий пользователя по времени работы, создаваемой нагрузки и стоимости разработки.

                Only users with full accounts can post comments. Log in, please.