Тема данного поста навеяна чтением Ральфа Кимбалла, который выделяет три вида таблиц фактов при построении хранилищ данных: транзакционные, снэпшотные и накопительные. В первых каждой строке соответствует отдельная транзакция (бизнес-операция, факт снятия показаний, зарегистрированное уникальное событие), загружаемая в хранилище данных из системы-источника. Вторые формруются из периодически загружаемых моментальных снимков (снэпшотов), где каждый снэпшот содержит множество строк. Третьи допускают многократное обновление строк, поскольку их строки описывают некие эволюционирующие сущности, например, заказы с этапами жизненного цикла или продажи за месяц при ежедневном обновлении.
Поговорим о снэпшотных таблицах фактов. И начнем с определения контекста.
Есть свойства или показатели объектов, процессов и явлений, которые изменяюся регулярно. А есть такие, которые изменяются нерегуляно.
Например, время восхода солнца каждый день разное, и этот показатель относится к регулярно (ежедневно) меняющимся:
| Дата | Время восхода |
|---|---|
| 2025-04-15 | 05:45 |
| 2025-04-16 | 05:43 |
| 2025-04-17 | 05:40 |
| 2025-04-18 | 05:37 |
| 2025-04-19 | 05:34 |
| 2025-04-20 | 05:31 |
А вот номер страницы "Войны и мира", на которой вы прервали чтение, может оказаться нерегулярно меняющимся показателем, если вы читали книгу не каждый день:
| Дата | Номер текущей страницы |
|---|---|
| 2025-04-15 | 122 |
| 2025-04-16 | 122 |
| 2025-04-17 | 212 |
| 2025-04-18 | 331 |
| 2025-04-19 | 331 |
| 2025-04-20 | 409 |
| 2025-04-21 | 450 |
Нерегулярно меняющиеся показатели можно представить компактно, фиксируя значения показателя только на даты, когда значение изменялось, так как в промежуточные даты оно неизменно и равно последнему зафиксированному ранее:
| Дата | Номер текущей страницы |
|---|---|
| 2025-04-15 | 122 |
| 2025-04-17 | 212 |
| 2025-04-20 | 409 |
| 2025-04-21 | 450 |
Как видите, для хранения нерегулярно меняющегося показателя без потерь оказалось достаточно 4 строк вместо 7. Регулярно меняющиеся показатели, такие как время восхода солнца, в отличие от нерегулярно меняющихся, такому сжатию не поддаются.
К нерегулярно меняющимся показателям относятся уровни запасов товаров на складе, или на полках магазина, или в пути от поставщика к заказчику. А также любой другой показатель или свойство объекта или процесса, значение которого меняется через неравные интервалы (времени, пространства или иной оси).
Рассмотрим компактное представление уровня запасов товаров на складах в снэпшотной таблице фактов, содержащей историю изменения запасов за период.
Ежедневно в конце рабочего дня мы получаем снэпшот (моментальный снимок) уровней запасов товаров на складах, который содержит следующие поля:
- Код склада
- Код товара
- Количество товара, шт
- Стоимость товара, руб
Для наглядности рассмотрим, как изменялся уровень запаса товара с кодом 12345 на складе с кодом 12 за период с 2025-04-15 по 2025-04-21, включительно, то есть, за неделю. Вот соответствующие строки из ежедневных спэпшотов:
| Дата снэпшота | Код склада | Код товара | Кол-во, шт | Стоимость, руб |
|---|---|---|---|---|
| 2025-04-15 | 12 | 12345 | 156 | 5148 |
| 2025-04-16 | 12 | 12345 | 154 | 5084 |
| 2025-04-17 | 12 | 12345 | 154 | 5084 |
| 2025-04-18 | 12 | 12345 | 154 | 5084 |
| 2025-04-19 | 12 | 12345 | 154 | 5084 |
| 2025-04-20 | 12 | 12345 | 140 | 4620 |
| 2025-04-21 | 12 | 12345 | 140 | 4620 |
Как видите, уровень запаса изменяется не каждый день, и потому приведенные данные можно представить компактно:
| Дата снэпшота | Код склада | Код товара | Кол-во, шт | Стоимость, руб |
|---|---|---|---|---|
| 2025-04-15 | 12 | 12345 | 156 | 5148 |
| 2025-04-16 | 12 | 12345 | 154 | 5084 |
| 2025-04-20 | 12 | 12345 | 140 | 4620 |
Определим структуру таблицы f_stock для хранения истории изменения запасов товаров на складах:
| Имя столбца | Тип данных | Комментарий |
|---|---|---|
| snap_date | date | Дата снэпшота |
| store_code | int | Код склада |
| art_code | int | Код товара |
| qty | int | Количество товара на конец дня, шт |
| amt | numeric(12,2) | Стоимость товара на конец дня, руб |
| last_date | date | Последняя дата периода |
В таблице имеются два столбца с датами. Столбец snap_date содержит дату снэпшота, по данным которого создана строка (то есть, это дата создания строки), а столбец last_date содержит последнюю дату периода, в течение которого уровень запаса товара на складе оставался неизменным.
С учетом этих дат, в таблице f_stock история изменения запаса товара 12345 на складе 12 за неделю представлена так:
| snap_date | store_code | art_code | qty | amt | last_date |
|---|---|---|---|---|---|
| 2025-04-15 | 12 | 12345 | 156 | 5148 | 2025-04-15 |
| 2025-04-16 | 12 | 12345 | 154 | 5084 | 2025-04-19 |
| 2025-04-20 | 12 | 12345 | 140 | 4620 | 2099-12-31 |
Строго логически, столбец last_date избыточен, ведь при изменении уровня запаса в таблице создается новая строка, где snap_date содержит дату снэпшота, в котором уровень запаса изменился. А last_date в строке, где snap_date хронологически предшествует новой строке, получает дату, предшествующую дате новой строки.
Но наличие двух дат в одной строке определяет период времени, в течение которого актуальны представленные в строке данные, и позволяет эффективно выбирать данные на интересующую нас дату. Например, найдем уровень запаса на 17 апреля:
select qty, amt
from f_sale
where store_code = 12
and art_code = 12345
and date '2025-04-17' between snap_date and last_date
;
qty amt
--- ----
154 5084
Обратите внимание, что для последней строки, где хранится текущий уровень запаса, поле last_date содержит дату в далеком будущем, что позволяет таким же запросом получить уровень запаса на любую дату позднее даты последнего снэпшота:
select qty, amt
from f_sale
where store_code = 12
and art_code = 12345
and date '2025-04-21' between snap_date and last_date
;
qty amt
--- -----
140 4620
Таким образом, логически избыточный столбец last_date приносит большую практическую пользу. Не будь его, для получения запасов на дату нам пришлось бы использовать менее эффективные запросы с традиционным агрегированием или оконными функциями.
Приведенные выше запросы возвращают уровень запаса товара на складе на конец дня.
Чтобы получить уровень запаса на начало дня (равный уровню запаса на конец предыдущего дня), достаточно незначительно изменить условие отбора. Давайте получим уровни запаса на начало дня 16 и 21 апреля (что эквивалентно уровням на конец дня 15 и 20 апреля):
select qty, amt
from f_sale
where store_code = 12
and art_code = 12345
and date '2025-04-16' - 1 between snap_date and last_date
;
qty amt
--- ----
154 5148
select qty, amt
from f_sale
where store_code = 12
and art_code = 12345
and date '2025-04-21' - 1 between snap_date and last_date
;
qty amt
--- ----
140 4620
Получить уровень запаса на начало дня 2025-04-15 (дата первого снэпшота), как и уровень запаса на конец дня 2025-04-14 в нашей таблице невозможно. Ведь мы не сохраняли историю запасов до 2025-04-15, следовательно, не знаем их значений раньше этой даты. Для практических целей в разных ситуациях может оказаться целесообразным считать уровень запасов на более ранние даты равным null (неизвестно) или 0.
Итак, мы рассмотрели компактное представление нерегулярно меняющихся данных в снэпшотной таблице фактов, где каждая строка содержит данные за некоторый интервал времени. Из приведенных примеров видно, что компактное представление не приводит к потере данных, а логически избыточный столбец last_date обеспечивает возможность выполнения эффективных запросов.
Наконец, рассмотрим наполнение таблицы фактов f_stock по данным ежедневных снэпшотов. Оно реализуется за два шага:
- Закрыть текущие периоды (где
last_date = date '2099-12-31'), где уровень запаса перестал быть актуальным, установив для нихlast_dateравной дате снэпшота минус 1. - Создать новые текущие периоды с уровнями запаса из снэпшота, где
snap_dateравна дате снэпшота иlast_dateравна дате в далеком будущем.
Пусть ежедневный снэпшот загружен в таблицу daily_stock.
Тогда шаг 1 реализуется следующим предложением SQL:
update f_stock tgt set
last_date = <snapshot_date> - 1
from daily_stock src
where tgt.last_date = date '2099-12-31'
and tgt.store_code = src.store_code
and tgt.art_code = src.art_code
and (tgt.qty != src.qty or tgt.amt != src.amt)
;
В таблице f_stock закрываются все текущие периоды для (код склада, код товара), для которых уровень запаса отличается от уровня запаса в снэпшоте daily_stock. А если уровни запаса равны, то текущий период в f_stock не закрывается.
Шаг 2 реализуется следующим предложением SQL:
insert into f_stock (
snap_date,
store_date,
art_date,
qty,
amt,
last_date
)
select
<snapshot_date>,
src.store_date,
src.art_date,
src.qty,
src.amt,
date '2099-12-31'
from daily_stock src
where not exists (
select 1
from f_stock tgt
where tgt.last_date = date '2099-12-31'
and tgt.store_code = src.store_code
and tgt.art_code = src.art_code
)
;
В таблицу f_stock добавляются строки с текущими периодами для (код склада, код магазина) из daily_stock, для которых в f_stock отсутствуют текущие периоды.
Как вариант, шаги 1 и 2 можно реализовать с помощью двух последовательных предложений merge:
-- Шаг 1
merge into f_stock tgt
using daily_stock src
on (tgt.store_code, tgt.art_code, tgt.last_code) = (src.store_code, src.art_code, date '2099-12-31')
when matched and (tgt.qty != src.qty or tgt.amt != src.amt) then
update set
last_date = <snapshot_date> - 1
;
-- Шаг 2
merge into f_stock tgt
using daily_stock src
on (tgt.store_code, tgt.art_code, tgt.last_code) = (src.store_code, src.art_code, date '2099-12-31')
when not matched then
insert (
snap_date,
store_date,
art_date,
qty,
amt,
last_date
) values (
<snapshot_date>,
src.store_date,
src.art_date,
src.qty,
src.amt,
date '2099-12-31'
)
;
А теперь бонус! Алгоритм будет работать как с ежедневными снэпшотами, включающими строки для всех сочетаний (код склада, код товара), так и с пакетами ежедневных изменений, которые будут включать только строки с (код склада, код товара), для которых уровень запаса изменился за прошедшие сутки.
Замечу в заключение, что приведенный алгоритм наполнения таблицы фактов f_stock не будет работать для случая, когда ежедневный снэпшот содержит строки только для сочетаний (код склада, код товара) с ненулевым уровнем запаса. Если запас товара 12345 на складе 12 вчера был 10 шт и был включен в снэпшот, а сегодня стал 0 шт и поэтому не был включен в снэпшот, то в таблице f_stock после обработки сегодняшнего снэпшота запас 10 шт останется действующим. Так что будьте бдтительны :) Если ваш снэшшот содержит только ненулевые значения, логику обновления таблицы фактов следует скорректировать.
P.S. Иногда таблицы фактов могут быть одновременно снэпшотными (то есть, обновляться из периодичесикх снэпшотов) и накопительными (то есть, допускать многократные изменения одних и тех же строк, которые содержат эволюционирующие или неокончательные данные). Например, такова ежедневно обновляемая таблица, строки которой содержат суммарные продажи товаров в магазинах за месяц. Строки за завершенные месяцы неизменны, а строки за текущий месяц ежедневно обновляются из снэпшота с продажами за истекший день. Но это уже другая история...
Комментариев нет:
Отправить комментарий