В прошлой статье мини-цикла о работе с агрегатами я рассказывал, как организовать эффективное многопоточное преобразование потока первичных данных в данные агрегированные. Там мы рассматривали задачу "свертки" продаж в агрегаты вида товар/дата/кол-во.
Сегодня мы рассмотрим более сложный вариант, который зачастую начинается со слов "А заказчик захотел…" и приводит нас к иерархичным агрегатам в нескольких одновременных разрезах, которые позволяют нам в СБИС практически мгновенно строить оперативные отчеты в подсистемах организации торговли, бухгалтерского учета и даже управления активными продажами.
Бизнес-требования
уметь быстро получить информацию не только по товарам, но и по складам
в том числе и сводка-TOP продаж товаров на интервале
в том числе с фильтром по складу... или без
а еще график динамики продаж за месяц по дням... и за год по месяцам... и за все время по годам
... и с любым из фильтров склад/товар
... и чтобы все быстро работало!
Итак, вычленяем ключевое для нас относительно предыдущей задачи:
появляется новый разрез агрегации - по складу
необходима агрегация "без учета" или "по всем" - например, сводные продажи всех товаров склада или продажи товара по всем складам
нужны агрегаты для динамики (дневные/месячные/годовые) в разрезе любого фильтра
Структура новых агрегатов
Добавим в таблицу агрегатов новое поле - тип интервала агрегации (D/M/Y) и новый аналитический разрез - склад.
CREATE TABLE agg(
it -- товар
integer
, wh -- склад
integer
, dt -- дата продажи/начала кванта
date
, quant -- тип кванта D/M/Y
"char"
, qty -- количество
double precision
);Замечу, что тут для экономии размера данных мы использовали однобайтный спецтип "char". Например, такой тип имеет поле relkind (тип объекта) в системной таблице pg_class.
Неудобный NULL и удобный ноль
Для аналитики "по всем" используем значение = 0 (не NULL) соответствующего разреза. Это позволит нам всегда передавать значения в один и тот же запрос, без изменения его модели на … IS [NOT] NULL.
То есть для запроса "какие товары продавались лучше всего в таком-то месяце" будем использовать запрос вида:
SELECT
it
, qty
FROM
agg
WHERE
wh = $1::integer AND -- передадим 0 для разреза "по всем складам"
(quant, dt) = ($2::"char", $3::date) -- передадим 'M' для обращения к "месячному" агрегату
ORDER BY
qty DESC
LIMIT ...;А если нам понадобится этот же рейтинг по конкретному складу, то просто передадим сюда же ID склада! Очевидно, для такого запроса подходящим будет индекс (quant, dt, wh, qty DESC).
В результате, за единственный Index Scan мы получим сразу все, что хотим, без какой-либо динамической агрегации на моменте получения данных.
Динамика в разрезе фильтра
Давайте теперь сконструируем запрос, который поможет нам нарисовать красивый график по динамике:
SELECT
dt
, qty
FROM
agg
WHERE
(quant, it, wh) = ($1::"char", $2::integer, $3::integer) AND
dt BETWEEN $4::date AND $5::date -- период графика
ORDER BY
dt;Индекс под него - (quant, it, wh, dt). Почему на первом месте именно quant? Потому что у него очень маленькая селективность, и индекс будет занимать меньше места.
Сборка агрегатов
Итак, вернемся к предыдущей статье и проблемам, которые мы успешно решали там - разделению кросс-блокировок между параллельно работающими потоками. В нынешней ситуации, разделив обработку по ключу (it, wh), мы заведомо устраним конфликты между обработчиками.
Но возникнет небольшая проблема - давайте посмотрим, как именно эффективнее всего добиться формирования агрегатов:
При проходе по курсору над flow-таблицей мы формируем в памяти "дифф" для инкремента записей соответствующих агрегатов по обрабатываемому этим потоком ключу (it, wh) - сразу для каждого из типов интервалов.
Вместе с этим мы вставляем во flow новую "первичку" для последующих "надагрегатов", заменяя нулем каждый из вариантов разрезов анализа.
Задача: "Вскипятить воду в чайнике"
И физик и математик: налить воду в чайник, зажечь плиту, поставить чайник на огонь и подогреть до 100*С.
Новая задача: "Вскипятить воду в чайнике. Чайник уже налит, огонь горит"
Физик: поставить чайник на огонь и подогреть.
Математик: выливаем воду из чайника на плиту - чайник пуст, огонь не горит - задача сведена к предыдущей!
(c) народный анекдот
Понятно, что при последующей обработке такого ключа, содержащего хотя бы один ноль, записи "надагрегатов" формировать уже не нужно.
Обходим блокировки
Единственная точка, в которой у нас могут пересечься два параллельно работающих потока, это формирование/обновление записи queue, соответствующей этому ключу "надагрегата" - например, (0, 0).
К счастью, это достаточно просто обходится вставкой новой записи в queue, если pg_try_advisory_xact_lock(it, wh) для такого ключа вернула нам FALSE. То есть да, в разрезе ключа распределения записи в очереди могут быть неуникальны. Но в этом нет ничего страшного, потому что они всего лишь выполняют функцию сигнализатора "во flow что-то может быть по этому ключу". И если нет - не страшно, при обработке этой записи очереди мы заглянем во flow, ничего не найдем, и спокойно завершим обработку.
Итого - мы получили в БД все нужные агрегаты во всех требуемых разрезах, которые помогут нам обеспечить быстрый показ отчета/графика в любой комбинации фильтров:
Мини-серия "Агрегаты в БД":
