Агрегаты в БД — многомерные суперагрегаты

В прошлой статье мини-цикла о работе с агрегатами я рассказывал, как организовать эффективное многопоточное преобразование потока первичных данных в данные агрегированные. Там мы рассматривали задачу «свертки» продаж в агрегаты вида товар/дата/кол-во.

Сегодня мы рассмотрим более сложный вариант, который зачастую начинается со слов «А заказчик захотел…» и приводит нас к иерархичным агрегатам в нескольких одновременных разрезах, которые позволяют нам в СБИС практически мгновенно строить оперативные отчеты в подсистемах организации торговли, бухгалтерского учета и даже управления активными продажами.

Бизнес-требования

• уметь быстро получить информацию не только по товарам, но и по складам

• в том числе и сводка-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, ничего не найдем, и спокойно завершим обработку.

Итого — мы получили в БД все нужные агрегаты во всех требуемых разрезах, которые помогут нам обеспечить быстрый показ отчета/графика в любой комбинации фильтров: