Организация периодических сведений

 

Довольно часто появляется задача хранения и обработки данных, актуальных на определенный период времени в различных аналитических разрезах. Вот несколько примеров:

1.                 Курсы валют. Для каждой валюты требуется устанавливать и получать курс на определенный период, а возможно и кратность.

2.                 Цены товаров. Для каждого товара по каждому типу цен требуется определять актуальную на момент времени цену.

3.                 Состояния работников организации. В разрезе сотрудников знать на момент времени его состояние: принят, уволен, в отпуске и т.д.

Рассмотрим, какую информацию необходимо при этом хранить. Во-первых, это сами периоды действия данных, границы которых могут быть как датой, так и содержать время. Во-вторых, это аналитика, в разрезе которой эти данные интересны, в примерах это валюты, товары, типы цен, работники. Наконец, сами данные - сведения об аналитике на определенный период.

Для организации хранения таких данных понадобится единственная таблица, но в зависимости от выбранной модели прототип ее будет отличаться. При этом под полем «Аналитика» будет подразумеваться набор полей, если таковых несколько, аналогично и с полем «Сведения».

Модель 1.

Момент начала периода

Аналитика

Сведения

 

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

Ограничение: Дата начала периода + Аналитика образуют уникальный ключ.

Модификация данных: очень быстрая, так как кроме единственного уникального ключа никаких ограничений нет. При этом вставка данных создает новый период, частично перекрывая предыдущий. Удаление продляет предыдущий период (если такой имеется).

Чтение данных в основном заключается в получении сведений на определенный момент времени как заданный непосредственно, так и в соединении по моменту времени с другими наборами данных. В любом случае в запросах необходимо соединять таблицу с собой же для определения начала актуального периода. Возможные шаблоны запросов:

SELECT Аналитика, Сведения

FROM ТаблицаСведений

INNER JOIN

     (SELECT Аналитика, MAX(Момент) AS МинМомент

     FROM ТаблицаСведений

     WHERE Момент <= :Момент

     GROUP BY Аналитика) AS ПодЗапрос

ON (ТаблицаСведений.Момент = ПодЗапрос.МинМомент)

     AND (ТаблицаСведений.Аналитика = ПодЗапрос.Аналитика)

 

 

SELECT ПодЗапрос.ВнешниеДанные,

     ПодЗапрос.Аналитика,

     ТаблицаСведений.Сведения

FROM

     (SELECT ВнешнийНаборДанных.Данные AS ВнешниеДанные,

         ВнешнийНаборДанных.Аналитика AS Аналитика,

         MAX(Момент) AS МинМомент

     FROM ВнешнийНаборДанных

     LEFT JOIN ТаблицаСведений

     ON (ТаблицаСведений.Момент <= ПодЗапрос.МинМомент)

         AND (ТаблицаСведений.Аналитика =

                        ВнешнийНаборДанных.Аналитика)

     GROUP BY ВнешнийНаборДанных.Аналитика

     ) AS ПодЗапрос

LEFT JOIN ТаблицаСведений

     ON (ТаблицаСведений.Момент = ПодЗапрос.МинМомент)

     AND (ТаблицаСведений.Аналитика = ПодЗапрос.Аналитика)

 

Чтение данных получается достаточно громоздким. Частично можно оптимизировать, если изменение периодов достаточно редкое и дискретное, например день. Тогда можно заполнять таблицу без пропусков таких периодов (на каждый день), даже если фактически сведения не менялись. Тогда актуальные сведения легко получаются из равенства периодов. Недостатком оптимизации является увеличение объема данных, запрет пустых интервалов периодов, невозможность получения данных после последней записи.

Модель 2.

Начало периода

Конец периода

Аналитика

Сведения

 

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

Уникальный ключ: Начало периода + Аналитика.

Пропуски между периодами и наложение периодов недопустимы. Поэтому предварительно для изменения необходимо вычисление конца периода. Однако этот механизм можно перенести, например, в триггеры.

При вставке система вычисляет конец периода, а также меняет конец того периода, в который вставляем. Аналогичная логика при изменении начала периода и удалении записи. Естественно это замедлит скорость модификации таблицы, но даст выигрыш  в чтении.

SELECT Аналитика, Сведения

FROM ТаблицаСведений

WHERE (:Момент>=НачалоПериода AND :Момент<КонецПериода)

 

SELECT ВнешнийНаборДанных.Данные,

     ВнешнийНаборДанных.Аналитика AS Аналитика,

     ТаблицаСведений.Сведения

FROM ВнешнийНаборДанных

     LEFT JOIN ТаблицаСведений

     ON (ВнешнийНаборДанных.Момент >=

                             ТаблицаСведений.НачалоПериода)

            AND (ВнешнийНаборДанных.Момент <

                             ТаблицаСведений.КонецПериода)

            AND (ВнешнийНаборДанных.Аналитика =                                                   ТаблицаСведений.Аналитика)

 

Также возможно ввести понятие бесконечно удаленной границы периода, вставляя в одно из полей границы NULL, однако это усложнит запросы на чтение. Решение проблемы – вставка заведомо хронологически  невозможных (очень далёких) моментов времени.

 

Модель 3.

Начало периода

Конец периода

Уровень

Аналитика

Сведения

 

Здесь также периоды хранятся явно, однако возможны наложения и разрывы периодов. Отсутствие записи означает отсутствие сведений. Наложение – действительны сведения с наибольшим уровнем. Поэтому, всё-таки, периоды одного уровня пересекаться не могут. Также посредством NULL или заведомо невозможных моментов времени ввести неограниченные границы периодов.

При вставке пересекающихся  периодов необходимо проверять их уровни, в случае равных – вставка невозможна. Запросы на чтение, также как и в первой модели требую подзапроса для определения максимального уровня. Однако модель удобна, в случае «вытесняющих» данных, например сезонных скидок, расчетов по оплате труда.

 

Выбор той или иной модели зависит от задачи. Первая модель удобна при достаточно интенсивных модификациях таблицы, вторая – при редких изменениях, но частых чтениях, третья – в случае «взаимовытесняющих» периодов.