Периодические таблицы-2 (не химия)

В посте Периодические таблицы (не химия) я писал о специфике работы с таблицами, строки которых содержат период действия. В частности, рассматривал способы обеспечения того, чтобы периоды не перекрывались. При этом периоды действия определялись двумя полями: первая дата действия и последняя дата действия, где обе даты включены в период. Такой подход позволяет выбирать действующую строку с помощью оператора 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]