В посте Периодические таблицы (не химия) я писал о специфике работы с таблицами, строки которых содержат период действия. В частности, рассматривал способы обеспечения того, чтобы периоды не перекрывались. При этом периоды действия определялись двумя полями: первая дата действия и последняя дата действия, где обе даты включены в период. Такой подход позволяет выбирать действующую строку с помощью оператора between
, как в следующем примере:
select *
from ttt
where current_date between first_date and last_date
;
Подход естественный и очевидный. В самом деле, январь длится с 1 по 31, включительно. Неделя длится с понедельника по воскресенье, включительно. Акция в магазине проходит с 10 по 15 апреля, включительно.
Однако! Магазин работает с 10 до 20 часов, исключая 20 часов. Обеденный перерыв длится с 13 до 14 часов, исключая 14 часов, а с 14 до 14:30 проходит совещание – исключая 14:30.
Такой подход, когда дата завершения одного периода равна дате начала следующего периода, тоже широко распространен. В частности, в PostgreSQL он используется
- в операторе overlaps,
- в работе с диапазонными (range) типами,
- при партиционировании таблиц по диапазонам значений.
Так работает overlaps
:
select
(date '2024-01-01', date '2024-02-01')
overlaps
(date '2024-02-01', date '2024-02-29')
;
false
Так работают диапазоны – оператор @>
проверяет, содержит ли диапазон указанное значение:
select
daterange(date '2024-01-01', date '2024-02-01') @> date '2024-01-01',
daterange(date '2024-01-01', date '2024-02-01') @> date '2024-02-01'
;
true false
Так работает партиционирование по диапазонам значений:
create table test (
id int primary key,
name text
)
partition by range (id)
;
create table test_part_1 partition of test for values from (1) to (1000001);
create table test_part_2 partition of test for values from (1000001) to (2000001);
create table test_part_3 partition of test for values from (2000001) to (3000001);
create table test_part_default partition of test default;
insert into test values
(1, 'qwerty'),
(1000001, 'qwerty'),
(2000001, 'qwerty'),
(3000001, 'qwerty')
;
select * from test_part_1;
id name
------- -------
1 qwerty
select * from test_part_2;
id name
------- -------
1000001 qwerty
select * from test_part_3;
id name
------- -------
2000001 qwerty
select * from test_part_default;
id name
------- -------
3000001 qwerty
drop table test;
Если таблица со смежными периодами использует подход, когда верхняя граница периода равна нижней границе следующего периода, то оператор between
не годится для выбора действующей строки:
select *
from promo
where date '2024-02-01' between first_date and last_date
;
id name first_date last_date
---- ---------- ---------- ----------
15 Promo days 2024-01-01 2024-02-01
17 Big sale 2024-02-01 2024-02-16
Вместо between
нужно использовать составное условие, которое гарантирует возврат только одной (действующей на дату) строки:
select *
from promo
where date '2024-02-01' >= first_date
and date '2024-02-01' < last_date
;
id name first_date last_date
---- ---------- ---------- ----------
17 Big sale 2024-02-01 2024-02-16
Даты, задающие период, могут быть с точностью до дня или до секунд или микросекунд, как требуется приложению.
Как известно, таблицы измерений (dimension tables) для витрин данных чаще всего обновляются по типу 1 или по типу 2. В первом случае позднейшие значения атрибутов переписывают более ранние значения в одной и той же строке таблицы. А при обновлении по типу 2 позднейшие значения атрибутов сохраняются в новой строке таблицы, где дата начала периода действия равна времени загрузки данных, а строка таблицы с прежними значениями атрибутов получает дату окончания периода действия, равную времени загрузки данных.
Пример товара из таблицы измерений Товары
, обновляемой то типу 2:
select *
from d_article
where art_code = 555555
;
art_code oridate enddate name
-------- ------------------- ------------------- -------------------
555555 2022-01-10 14:15:20 2023-06-05 10:27:20 ДВУХФАЗНЫЙ ЛОСЬ
555555 2023-06-05 10:27:20 2100-01-01 00:00:00 ДВУХФАЗНЫЙ ЛОСЬОН
Здесь столбец oridate
содержит дату и время начала периода действия строки, включительно, а столбец enddate
содержит дату и время завершения периода, исключительно. Следующий запрос возвращает актуальные значения атрибутов товара:
select *
from d_article
where art_code = 555555
and current_timestamp >= oridate
and current_timestamp < enddate
;
art_code oridate enddate name
-------- ------------------- ------------------- -------------------
555555 2022-01-10 14:15:20 2100-01-01 00:00:00 ДВУХФАЗНЫЙ ЛОСЬОН
Небезинтересно, что подход с открытой (исключенной) верхней границей диапазона также по умолчанию используется в Python. Например, создадим список (list) с числами от 0 до 10, исключая 10, и сделаем срез (slice) по диапазону индексов с 3 по 6, исключая 6-й:
>>> digits = list(range(10))
>>> digits
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> digits[3:6]
[3, 4, 5]
Комментариев нет:
Отправить комментарий