В прошлой статье мини-цикла о работе с агрегатами я рассказывал, как организоватьэффективное многопоточное преобразование потока первичных данныхв данные агрегированные. Там мы рассматривали задачу "свертки" продаж в агрегаты видатовар/дата/кол-во.
Сегодня мы рассмотрим более сложный вариант, который зачастую начинается со слов "А заказчик захотел" и приводит нас к иерархичным агрегатам в нескольких одновременных разрезах, которые позволяют нам в СБИС практически мгновенно строить оперативные отчеты в подсистемах организации торговли, бухгалтерского учета и даже управления активными продажами.
Бизнес-требования
И
швец, и жнец, и на дуде игрец...
-
уметь быстро получить информацию не толькопо товарам, но ипо складам
-
в том числе исводка-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, qtyFROM aggWHERE wh = $1::integer AND -- передадим 0 для разреза "по всем складам" (quant, dt) = ($2::"char", $3::date) -- передадим 'M' для обращения к "месячному" агрегатуORDER BY qty DESCLIMIT ...;
А если нам понадобится этот же рейтинг по конкретному складу, то
простопередадим сюда же ID склада! Очевидно, для
такого запроса подходящим будет индекс(quant, dt, wh, qty
DESC).
В результате,за единственный Index Scanмы получим сразу все, что хотим, без какой-либо динамической агрегации на моменте получения данных.
Динамика в разрезе фильтра
Давайте теперь сконструируем запрос, который поможет нам нарисовать красивый график по динамике:
SELECT dt, qtyFROM aggWHERE (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 новую "первичку" для последующих "надагрегатов",заменяя нулем каждый из вариантов разрезованализа. Понятно, что при последующей обработке такого ключа, содержащего хотя бы один ноль, записи "надагрегатов" формировать уже не нужно.
Обходим блокировки
Единственная точка, в которой у нас могут пересечься два
параллельно работающих потока, это формирование/обновление
записи queue, соответствующей этому ключу "надагрегата" -
например,(0, 0).
К счастью, это достаточно просто обходитсявставкой новой
записи в queue, еслиpg_try_advisory_xact_lock(it,
wh)для такого ключа вернула намFALSE. То есть
да, в разрезе ключа распределения записи в очереди могут быть
неуникальны. Но в этом нет ничего страшного, потому что они всего
лишь выполняют функциюсигнализатора "во flow что-то может
быть по этому ключу". И если нет - не страшно, при
обработке этой записи очереди мы заглянем во flow, ничего не
найдем, и спокойно завершим обработку.
Итого - мы получили в БД все нужные агрегаты во всех требуемых разрезах, которые помогут нам обеспечить быстрый показ отчета/графика в любой комбинации фильтров:
