Продолжая тему проектирования БД («Мир ПК», №3/07, с. 72), рассмотрим хранение и обработку изменяющейся во времени информации. Примеры, когда необходимо помнить историю изменений или регистрировать последовательности событий, мы можем найти в самых разных областях: архив документов, история изменения цен или котировок, журнал событий (аудит), журнал хозяйственных операций, протоколы измерений эксперимента (показания датчиков) и т.д.
Следует выделить два наиболее общих случая, для которых требуется использование временных рядов: изменение во времени состояния объекта и регистрация событий, происходящих с ним.
В первом случае перед разработчиком стоит задача хранить историю изменения объекта (как правило, документа), чтобы иметь возможность восстановить его состояние в заданный момент времени. Кроме собственно организации структур данных, рассматриваемых в рамках статьи, необходимо создать целую подсистему, основанную на принципах документооборота. Наиболее критичным при этом будет время отклика системы для получения проекций документов на определенный момент времени в оперативном режиме. Во втором случае требуется хранить и восстанавливать историю действий, связанных с объектом. Как правило, эти данные предназначены для последующей аналитической обработки, поэтому здесь более важной будет скорость первичного сбора информации.
Перечисленные действия не являются взаимоисключающими. Например, хранение истории изменения атрибутов документа не исключает ведения журнала произведенных с ним операций. В технической литературе также иногда встречается термин «темпоральные базы данных», но он относится только к первому случаю.
Для реализации задачи выберем Microsoft SQL Server 2005. Надеюсь, пользователи других СУБД не испытали проблем при чтении первой статьи и потому смогут воспользоваться данным материалом, сделав лишь минимальные изменения в коде.
Хранение даты изменения
Наиболее простой способ организации временных рядов кажется очевидным: к имеющемуся ключу (идентификатору объекта) нужно добавить еще одно поле — дату изменения объекта (рис. 1).
Поле «Дата изменения» имеет простой физический смысл — обозначение момента, в который было изменено состояние объекта, например атрибута документа. Промежуток между этой датой и самой ближней к ней новой и будет определять период актуальности состояния.
Моделировать открытый интервал очень просто: он отсчитывается от максимальной даты в таблице и уходит в бесконечное будущее. Если объект некоторое время был недействительным, то такую семантику придется отражать использованием пустых (NULL) значений полей либо специальным полем-флагом.
Из описания сразу виден недостаток этого способа: чтобы определить период, необходимо просматривать и другие строки таблицы. Это ведет к утяжелению запросов, в которых придется делать соединение таблицы на саму себя (self-join):
SELECT *
FROM Документы
WHERE [Дата изменения] =
(SELECT MAX([Дата изменения])
FROM Документы
WHERE [Дата изменения] <= ‘2007-02-01’) — дата актуальности
К достоинствам способа относятся простота, отсутствие избыточности, быстрая вставка новых записей, а к недостаткам — относительно «тяжелые» соединяющиеся на себя (self-join) вложенные запросы и NULL для атрибутов или дополнительное поле-флаг при моделировании «пустых» периодов.
Метод хранения даты состояния можно рекомендовать в следующих случаях: при интенсивной вставке записей и при отсутствии частых массивных запросов по периодам.
Хранение интервала
Данный метод является естественной попыткой устранить недостатки предыдущего. Чтобы избежать вложенных соединяющихся на себя запросов, можно хранить интервал целиком. Поле «Дата изменения», таким образом, становится полем «Начало интервала». Оно остается в составе ключа в предположении, что интервалы не пересекаются. Также нужно добавить в таблицу второе поле — «Окончание интервала» (рис. 2).
Модификация даст возможность извлечь данные простым запросом:
SELECT * FROM Цены
WHERE ‘2007-02-01’ BETWEEN [Начало интервала] AND [Окончание интервала]
Впрочем, если избавиться от тяжелых запросов, то встанет новая проблема: для проверки непротиворечивости границ интервалов необходимо создать в таблице триггер, что, несомненно, отразится на скорости вставки новых записей и модификации существующих.
Структура также вызывает определенные осложнения для моделирования открытых интервалов, например, когда цена действует с момента ее утверждения до неизвестной пока даты установления новой. Для решения такой проблемы можно использовать фиктивные значения минимальной и максимальной даты, поддерживаемые конкретной СУБД. Например, для MS SQL 2005 это «1 января 1753 года» и «31 декабря 9999 года».
Преимущества способа — простота и эффективность запросов, недостатки — необходимость дополнительного поля даты для хранения окончания интервала, накладные расходы на поддержание непротиворечивости, меньшая скорость вставки, сложности моделирования открытых периодов.
Метод хранения интервалов можно рекомендовать в случае интенсивных и массивных запросов поиска при невысоких требованиях к скорости вставки и допустимом использовании процедурного расширения (триггеров) конкретной СУБД.
Хранение номера периода (интервала)
Данный метод включает достоинства и недостатки двух предыдущих. Он наиболее уместен тогда, когда одни и те же интервалы многократно используются для различных сущностей (рис. 3). Наиболее характерный пример его применения — бухгалтерские задачи с их понятиями учетных периодов.
В общем случае запрос выглядит так:
SELECT *
FROM [Хозяйственные операции]
WHERE [Номер периода] =
(SELECT [Номер периода]
FROM Периоды
WHERE ‘2007-02-01’ BETWEEN Начало AND Окончание)
Следует заметить, что запросы получения данных последнего периода или выполнение нескольких запросов поиска по одному периоду эффективно оптимизируются. В первом случае нужен простой MAX([Номер периода]) по ключу без условий WHERE, во втором значение номера периода предварительно запоминается в локальной переменной, после чего выполняется пакет запросов. Тем самым сложности моделирования открытых периодов снижаются, например, если учет ведется только постфактум, актуальным считается период с максимальным номером, для нахождения которого вообще не требуется поиск по датам начала и конца.
Преимущества метода — меньшая избыточность за счет повторного использования интервалов (периодов), относительная простота запросов, разнесение логики хранения периодов и самих объектов по разным таблицам; недостатки — более сложная структура, накладные расходы на поддержание непротиворечивости, меньшая скорость вставки.
Метод хранения номеров периодов можно рекомендовать для решения задач бухгалтерского и управленческого учета.
Итоги
Характеристики рассмотренных способов представлены в таблице.
Выбор оптимального варианта будет лежать в плоскостях «простота — избыточность» и «скорость вставки — скорость извлечения». Поэтому вам придется решать, что для вас важнее в каждом конкретном случае.
ОБ АВТОРЕ
Сергей Тарасов — инженер, e-mail: serge@arbinada.com.
Характеристики способов хранения хронологических данных
