В прошлый раз я описал компактную форму хранения нерегулярно меняющихся данных в таблице фактов на основе периодических снэпшотов.
Речь идет о таблице фактов в широком смысле:
"Каждая таблица с составным ключом из нескольких внешних ключей – это таблица фактов." Ральф КимбаллТакая таблица не обязательно принадлежит презентационному слою хранилища данных, но на законных основаниях может жить в промежуточном слое, который предоставляет данные для витрин. (Кстати, такая таблица в терминах 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впервые появился на складе в течение этой недели.
Плотные (dense) ли данные в таблицах 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
Здесь одна строка представляет одну дату. Если вместо этого нам нужны интервалы, в течение которых данные не меняются, то из полученного результата придется дополнительно выделить острова.
А если вспомнить, что в реальной интервальной таблице фактов миллионы строк, то станет очевидно, что превращать каждую интервальную строку в множество строк для каждой даты очень неэффективно. В связи с чем первое предложенное решение выглядит лучше. Ведь в самом худшем случае мы всего лишь удваиваем количество соединяемых строк, когда добавляем ведущий интервал с нулевыми показателями.
А вот здесь описан еще один подход к соединению интервальных таблиц. Которым я попробовал воспользоваться на реальных таблицах фактов — и который показал себя медленным и прожорливым, сравнительно с описанным выше подходом.
Впрочем, "истина всегда конкретна" — то есть, в конкретной ситуации нужно учитывать множество конкретных факторов, — и потому выбор за вами.
Комментариев нет:
Отправить комментарий