В прошлый раз я описал компактную форму хранения нерегулярно меняющихся данных в диапазонной таблице фактов (ДТФ) на основе периодических снэпшотов.
Речь идет о таблице фактов в широком смысле:
"Каждая таблица с составным ключом из нескольких внешних ключей – это таблица фактов." Ральф КимбаллТакая таблица не обязательно принадлежит презентационному слою хранилища данных, но на законных основаниях может жить в промежуточном слое, который предоставляет данные для витрин. (Кстати, такая таблица в терминах Data Vault — сателлит линка.)
Итак, поскольку описанный подход прост и эффективен, через короткое время мы обнаруживаем в промежуточном слое ХД несколько таких таблиц. Вслед за чем возникает задача их соединения в одном запросе. Рассмотрим, как это сделать.
Пусть у нас есть ДТФ с остатками товаров на складе f_stock и ДТФ с резервами товаров на складе f_reserve. Резерв товаров - это их количество, зарезервированное для отгрузки по заказам.
Для наглядности ограничимся двумя товарами и одной неделей с 15 по 21 апреля 2025:
select *
from f_stock
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
order by first_date
;
art_code qty first_date last_date
-------- ----- ---------- ----------
12345 156 2025-04-10 2025-04-15
12345 154 2025-04-16 2025-04-19
12404 1200 2025-04-17 2099-12-31
12345 140 2025-04-20 2099-12-31
select *
from f_reserve
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
order by first_date
;
art_code qty first_date last_date
-------- ----- ---------- ----------
12345 2 2025-04-15 2025-04-15
12345 0 2025-04-16 2025-04-18
12345 10 2025-04-19 2025-04-19
12345 0 2025-04-20 2099-12-31
12404 25 2025-04-21 2099-12-31
Как видим,
- диапазоны дат, в течение которых не меняются остатки на складе, и диапазоны дат, в течение которых не меняется резерв, различны;
- если товар
12345был на складе до начала интересующей нас недели, то товар12404впервые появился на складе в течение этой недели.
Покрывают ли диапазоны в таблицах f_stock и f_reserve весь охваченный промежуток времени без разрывов? Покрывают, начиная со дня первого появления товара на складе и первого резерва, соответственно. Так, товар 12404 впервые появляется на складе 2025-04-17, и, начиная с этой даты, ежедневные изменения его остатка отражаются в таблице f_stock, покрывая период до 2099-12-31. Но мы не найдем в этой таблице нулевой остаток этого товара ранее 2025-04-17.
Таким образом, не для всякого сочетания (склад, товар) есть данные на любую дату промежутка времени, представленного в таблицах f_stock и f_reserve.
Проделав предварительные наблюдения, построим запрос, который одновременно отобразит остатки и резервы товаров в течение интересующей нас недели.
Такой результат можно получить,
- обеспечив непрерывность данных на интересующей нас неделе,
- соединив остатки и резерв по коду товара и пересечению диапазонов.
with stock as (
-- ограничиваем диапазоны неделей
select art_code,
qty,
greatest(first_date, date '2025-04-15') first_date,
least(last_date, date '2025-04-21') last_date
from f_stock
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
union
-- добавляем ведущий диапазон для новых товаров,
-- обеспечивая непрерывность данных на неделе
select art_code,
0,
date '2025-04-15',
min(first_date) - 1
from f_stock
group by art_code
having min(first_date) > date '2025-04-15'
), reserve as (
-- ограничиваем диапазоны неделей
select art_code,
qty,
greatest(first_date, date '2025-04-15') first_date,
least(last_date, date '2025-04-21') last_date
from f_reserve
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
union
-- добавляем ведущий диапазон для новых товаров,
-- обеспечивая непрерывность данных на неделе
select art_code,
0,
date '2025-04-15',
min(first_date) - 1
from f_reserve
group by art_code
having min(first_date) > date '2025-04-15'
)
select s.art_code,
s.qty stock_qty,
r.qty reserve_qty,
greatest(s.first_date, r.first_date) first_date,
least(s.last_date, r.last_date) last_date
from stock s
join reserve r on
s.art_code = r.art_code
and s.first_date <= r.last_date
and s.last_date >= r.first_date
order by first_date
;
art_code stock_qty reserve_qty first_date last_date
-------- --------- ----------- ---------- ----------
12404 0 0 2025-04-15 2025-04-16
12345 156 2 2025-04-15 2025-04-15
12345 154 0 2025-04-16 2025-04-18
12404 1200 0 2025-04-17 2025-04-20
12345 154 10 2025-04-19 2025-04-19
12345 140 0 2025-04-20 2025-04-21
12404 1200 25 2025-04-21 2025-04-21
Если нас не интересуют диапазоны, когда одновременно и остаток и резерв равны нулю, то достаточно в финальный запрос добавить условие
where s.qty != 0 or r.qty != 0
Между данными в таблицах f_stock и f_reserve имеется неявная зависимость: товар может быть зарезервирован не ранее, чем он появился на складе. Но построенный нами запрос универсален в том смысле, что может соединять диапазонные таблицы с показателями, которые никак логически не связаны между собой, например, остаток на складе и закупочную цену товара.
Если понадобится соединить три диапазонных таблицы или более, можно делать это последовательно: сначала соединяем таблицу 1 и таблицу 2, затем результат их соединения соединяем с таблицей 3 и так далее.
Описанный подход к соединению диапазонных таблиц, конечно, не единственный.
В самом деле, первое, что приходит в голову — это превратить каждый диапазон дат в множество строк, представляющих отдельные даты диапазона, а затем соединить результат по коду товара и дате:
with stock as (
-- ограничиваем диапазоны неделей
select art_code,
qty,
greatest(first_date, date '2025-04-15') first_date,
least(last_date, date '2025-04-21') last_date
from f_stock
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
), reserve as (
-- ограничиваем диапазоны неделей
select art_code,
qty,
greatest(first_date, date '2025-04-15') first_date,
least(last_date, date '2025-04-21') last_date
from f_reserve
where first_date <= date '2025-04-21'
and last_date >= date '2025-04-15'
), daily_stock as (
-- раскрываем диапазоны до дней
select art_code,
qty,
first_date + generate_series(0, last_date - first_date) fact_date
from stock
), daily_reserve as (
-- раскрываем диапазоны до дней
select art_code,
qty,
first_date + generate_series(0, last_date - first_date) fact_date
from reserve
)
select s.art_code,
s.fact_date,
s.qty stock_qty,
coalesce(r.qty, 0) reserve_qty
from daily_stock s
left join daily_reserve r
on (s.art_code, s.fact_date) = (r.art_code, r.fact_date)
order by fact_date
;
art_code fact_date stock_qty reserve_qty
-------- ---------- --------- -----------
12345 2025-04-15 156 2
12345 2025-04-16 154 0
12345 2025-04-17 154 0
12404 2025-04-17 1200 0
12345 2025-04-18 154 0
12404 2025-04-18 1200 0
12345 2025-04-19 154 10
12404 2025-04-19 1200 0
12345 2025-04-20 140 0
12404 2025-04-20 1200 0
12345 2025-04-21 140 0
12404 2025-04-21 1200 25
Здесь одна строка представляет одну дату. Если вместо этого нам нужны диапазоны дат, в течение которых данные не меняются, то из полученного результата придется дополнительно выделить острова.
А если вспомнить, что в реальной ДТФ миллионы строк, то станет очевидно, что превращать каждую строку в множество строк для каждой даты очень неэффективно. В связи с чем первое предложенное решение выглядит лучше. Ведь в самом худшем случае мы всего лишь удваиваем количество соединяемых строк, когда добавляем ведущий диапазон с нулевыми показателями.
А вот здесь описан еще один подход к соединению диапазонных таблиц. Которым я попробовал воспользоваться на реальных таблицах фактов — и который показал себя медленным и прожорливым, сравнительно с описанным выше подходом.
Впрочем, "истина всегда конкретна" — то есть, в конкретной ситуации нужно учитывать множество конкретных факторов, — и потому выбор за вами.
Комментариев нет:
Отправить комментарий