Время от времени мне приходится сталкиваться с задачами, когда нужно в рамках имеющейся СУБД выполнить соединение двух и более исторических таблиц между собой, да так, чтобы получить красивые исторические интервалы на выходе. Зачем? Чтобы отчет смог правильно отобразить данные на выбранную пользователем дату, или приложение подтянуло в себя эти данные для обработки.
Часто коллеги и братья по цеху сталкиваются с подобными задачами и советуются как лучше их решить.
В этой статье я хочу поделиться опытом как решались различные ситуации подобного типа.
Сразу обмолвлюсь, что говоря словосочетание «историческая таблица» я подразумеваю
SCD Type 2 или SCD Type 6.
Так же речь пойдет в основном про хранилища данных. Но некоторые из описанных ниже подходов применены и для OLTP решений. Предполагается, что в соединяемых таблицах ссылочная целостность поддерживается или естественным путем, или путем генерации значений-потеряшек при загрузке (значения с ключом, но с дефолтными значениями изменяемых атрибутов).
Казалось бы, есть 2 таблицы, в каждой есть ключ, меняющиеся атрибуты, есть даты интервала действия записи — соедини их по ключу и придумай что-нибудь с интервалами. Но не все так просто.
Визуально задача выглядит следующим образом:
А вот как добиться результата оптимальным способом зависит от вашей ситуации.
Вариант первый – данных немного, таблиц тоже, можно считать каждый раз все «на лету»
В таком варианте можно просто создать представление (view) с соединением таблиц между собой, но с учетом пересечения интервалов.
Условие соединения можно схематично описать в следующем виде:
First_Table.Start_dt <= Second_Table.End_dt AND Second_Table.Start_dt <= First_Table.End_dt
Тут читатель может начать представлять это в голове, и просчитывать варианты, которые могут быть не учтены данным условие. Графически возможные варианты пересечения интервалов выглядят так:
Варианты так же верны, если поменять «Интервал А» и «Интервал В» местами. И, разумеется, конец любого из интервалов может быть открыт. Каким образом открыт (выбрана бесконечно далекая дата или стоит NULL) вопрос дизайна и для решения не принципиален, но при написании SQL вам надо учитывать какой именно вариант в вашей системе.
Пытливые умы могут проверить – условие покрывает все варианты.
Но, постойте, в результате соединения 2-х строк из разных таблиц подобным образом получится 2 даты начала и 2 даты конца. Надо из них что-то выбрать. У результирующего интервала, который получается в результате соединения двух пересекающихся интервалов границы будут вычисляться как наибольшая из 2-х дата начала и наименьшая из 2-х дата конца. В терминах функций СУБД Oracle это звучит как GREATEST(Start_DT) и LEAST(End_DT) соответственно. Полученный результирующий интервал можно соединять с 3-ей таблицей. Результат соединения, после вычисления результирующих дат начала и конца, можно соединять с 4-ой таблицей, и т.д.
В зависимости от используемой СУБД, SQL получается разной степени награможденности, но результат дает верный. Остается только обернуть его в CREATE VIEW и создать результирующую витрину, к которой и будут обращаться потребители (отчеты, приложения, пользователи) указывая интересующую дату актуальности.
Вариант второй – данных много
Если данных много, и использование обычного представления описанного в первом варианте не удовлетворяет требованиям производительности, то есть альтернатива – можно предрасчитывать витрину шаг за шагом. Да, я говорю именно о том, чтобы сохранять результаты расчета витрины в таблицу, и отправлять потребителей данных в таблицу.
Когда и как рассчитывать данные? Каждый раз после завершения загрузки детальных данных.
В таком случае задача сводится к тривиальной, главное знать бизнес-дату данных (отчетную дату, дату актуальности, кто как называет) которые только что загрузились. Это больше задача ETLELT Framework’a который контролирует эти процессы. Нас интересует дата (или дискретный набор дат). Создается процедура (хранимая ли, или ETLELT это уже определяется религией) которая на вход принимает дату или набор дат. А далее в цикле по ним начинает выполнять SQL, который соединяет все необходимые таблицы по ключам, и на каждую историческую таблицу накладывается условие вида: WHERE Input_Date BETWEEN Current_Table.Start_DT and Current_Table.End_DT. SQL такого рода в большинстве систем, которые мне доводилось видеть, отрабатывает довольно быстро, т.к. записи фильтруются по очень селективному условию, и дальше быстро соединяются.
Полученные результаты уже помещаются в витрину согласно механизму выделения изменений, т.к. витрина представляет из себя сущность SCD Type 2 или SCD Type 6, поэтому вам надо проверить является ли полученная запись измененной.
Недостатком такого варианта является то, что может делаться большой объем работы в «холостую». Например, никакого изменения в данных в загружаемую дату не произошло, но мы все равно построим snapshot всех данных на эту дату и сравним с витриной. И только после этого узнаем что изменений не произошло или произошло всего для 0.1% строк.
Вариант третий – модель данных представляет «снежинку» вокруг одного (или ограниченного набора) ключа, а данных может быть даже много
Такой вариант типичен для хранилищ данных имеющих модель близкой к 3-ей нормальной форме.
Схематично вариант модели выглядит примерно так:
При такой схеме, можно оптимизировать процесс расчета и выбрать из всего множества дат только те, в которые были какие-то реальные изменения. Как? Да очень просто — выбрать уникальные комбинации Key_id и Start_dt из всех таблиц. Т.е. UNION (замечу, не UNION ALL) из комбинаций Key_id, Start_dt. Выбирать UNION или DISTINCT из UNION ALL (или union ALL от DISTINCT'ов) зависит от используемой СУБД и вероисповедания разработчика.
В результате такого запроса мы получим набор ключей и дат, когда с этими ключами что-то происходило. Далее полученный набор можно соединять с таблицами по условию равенства Key_id и вхождения полученной Start_dt в интервалы действия записи конкретной таблицы. Многие догадаются, что в этой выборке будут присутствовать абсолютно все ключи, потому что они хотя бы раз попадали в хранилище. Но такой вариант все равно может быть выигрышным по производительности, если физическая модель данных позволяет обеспечивать производительность запросов по конкретным Key_id. И в тех случаях, когда есть весомое подмножество Key_id которые часто меняют состояния.
Описанный вариант может быть реализован двумя способами (зависит от СУБД и объемов данных). Можно делать это через view с использованием конструкции WITH (для Oracle можно даже делать --+ materialize, чтобы ускорить процесс) или же в два шага через процедуру и промежуточную таблицу. Промежуточная таблица, на самом деле, может быть одной большой (темпоральной) и использоваться для многих витрин, если комбинации ключей совпадают по типам данных. Т.е. в 1-ый шаг процедуры совершается выборка, и ее результаты сохраняются в таблицу. Во 2-ой шаг, выполняется соединение таблицы из шага 1 с детальными данными. Шагом 1.5 может быть сбор статистики по таблице, если это оправданно в данном конкретном случае.
Вариантов может быть много больше, ведь жизнь и фантазия разработчиков и заказчиков куда богаче, но хочется надеяться, что хотя бы эти описанные варианты помогут кому-то сохранить время или будут основой для создания еще более оптимальных решений.
Автор: mrz0diak
