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

Время от времени приходится работать с таблицами БД, где записи имеют период действия, заданный датой начала и датой конца (последнего дня) периода. Это может быть таблица с ценами товаров, где в разные периоды времени цена одного и того же товара разная. Или таблица с конфигурационными параметрами, где значения параметров разные в разные периоды времени. При этом в каждую конкретную дату у товара может быть только одна действующая цена, а у конфигурационного параметра только одно значение параметра. То есть, периоды действия записей с одним и тем же товаром или параметром не должны пересекаться.

В связи с этим при вставке или изменении записи нужно проверять, не приведет ли эта операция к появлению в таблице записей с пересекающимися периодами.

При работе со стандартным SQL такая проверка требует выполнения запроса select перед выполнением insert или update. (А в БД PostgreSQL можно обеспечить непересечение периодов с помощью нестандартного ограничения целостности.) Рассмотрим работу с периодами дат средствами стандартного SQL.

На следующих диаграммах, где по горизонтали идет время (даты, дни), период существующей в таблице записи показан как --------, а новый период добавляемой/изменяемой записи как ~~~~~~~.

___________________----------___________
_______~~~~~~~~~________________________


_______----------_______________________
__________________~~~~~~~~~_____________

Здесь новый период не пересекается с созданным ранее, так как либо целиком ему предшествует, либо целиком за ним следует. Обозначим даты начала и конца нового периода new_first_date и new_last_date, соответственно. Тогда условие непересечения нового периода с существующими в таблице t будет таким:

new_last_date < t.first_date or new_first_date > t.last_date

Если рассмотреть другие возможные отношения нового и существующего периодов, то получим:

____________-----------_________________
________~~~~~~~~~_______________________


____________-----------_________________
_________________~~~~~~~~~~_____________


____________-----------_________________
________~~~~~~~~~~~~~~~~~~~~____________


____________-----------_________________
______________~~~~~~~___________________

Проверка для каждого из этих случаев тривиальна, а все четыре случая пересечения покрываются следующим условием:

new_first_date <= t.last_date and new_last_date >= t.first_date

Если в условии непересечения, которое мы рассморели вначале, заменить or на and, а предикаты на противоположные им, то получим только что приведенное условие для пересечения.

Рассмотрим пример.

Пусть цены товаров с кодом art_code и периодами действия с first_date по last_date, включительно, хранятся в таблице art_price. Тогда следующий запрос находит уже имеющиеся в таблице цены одного и того же товара с пересекающимися периодами (таких не должно быть):

select *
from art_price t, art_price u
where t.art_code = u.art_code
    -- периоды пересекаются
    and u.first_date <= t.last_date
    and u.last_date >= t.first_date
    -- и это разные записи
    and u.first_date != t.first_date
    and u.last_date != t.last_date
;

А проверка на наличие в таблице записей с периодами, пересекающимися с новым периодом, который планируется создать, делается так:

select *
from art_price t
where t.art_code = new_art_code
    -- периоды пересекаются
    and new_first_date <= t.last_date
    and new_last_date >= t.first_date
;

Если ни одной записи не выбрано, то новый период можно создать.

Заметим, что новый период может пересекаться не с одним, а с несколькими уже существующими в таблице периодами, как смежными, так и несмежными. На следующих диаграммах существующие периоды показаны как ------, +++++++ и =========.

______--------++++++++========__________
_________~~~~~~~~~~~~~~~~~~_____________


______-----___++++++____======__________
_________~~~~~~~~~~~~~~~~~~_____________

В случае, когда бизнес-логика требует добавить новый период даже при наличии в таблице записей с пересекающимися периодами, нужно освободить интервал под добавляемый период и после этого добавить его. Сделать это можно так:

-- 1) удалить записи с вложенными периодами
delete art_price t
where t.art_code = new_art_code
    and t.first_date >= new_first_date
    and t.last_date <= new_last_date
;

-- 2) завершить предшествующий период перед началом нового периода
update art_price t
set t.last_date = new_first_date - 1
where t.art_code = new_art_code
    and new_first_date > t.first_date
    and new_first_date <= t.last_date
;

-- 3) начать последующий период после завершения нового периода
update tabl t
set t.first_date = new_last_date + 1
where t.art_code = new_art_code
    and new_last_date >= t.first_date
    and new_last_date < t.last_date
;

-- 4) вставить запись с новым периодом
insert into art_price (
    art_code, first_date, last_date)
values (
    new_art_code, new_first_date, new_last_date)
;

На языке диаграмм, что было и что получилось:

-- было

______--------++++++++========__________
_________~~~~~~~~~~~~~~~~~~_____________

-- 1) удалить записи с вложенными периодами

______--------________========__________
_________~~~~~~~~~~~~~~~~~~_____________

-- 2) завершить предшествующий период перед началом нового периода

______---_____________========__________
_________~~~~~~~~~~~~~~~~~~_____________

-- 3) начать последующий период после завершения нового периода

______---__________________===__________
_________~~~~~~~~~~~~~~~~~~_____________

-- 4) вставить запись с новым периодом

______---~~~~~~~~~~~~~~~~~~===__________

Бонус. Как контролировать непересечение периодов с помощью органичения целостности в PostgreSQL:

create extension btree_gist;

create table demo (
    art_code varchar(10) not null,
    first_date date not null,
    last_date date not null
);

insert into demo (art_code, first_date, last_date) values
('123', '2022-01-01', '2022-01-31'),
('123', '2022-02-01', '2022-02-28')
;

alter table demo 
add constraint demo_period exclude using gist (
    art_code with =,
    daterange(first_date, last_date, '[]') with &&
);

insert into demo (art_code, first_date, last_date) values
('123', '2022-02-28', '2022-04-01')
;

SQL Error [23P01]: ERROR: conflicting key value violates exclusion constraint "demo_period"
  Подробности: Key (art_code, daterange(first_date, last_date, '[]'::text))=(123, [2022-02-28,2022-04-02)) conflicts with existing key (art_code, daterange(first_date, last_date, '[]'::text))=(123, [2022-02-01,2022-03-01)).

insert into demo (art_code, first_date, last_date) values
('456', '2022-02-28', '2022-04-01')
;

insert into demo (art_code, first_date, last_date) values
('123', '2022-03-01', '2022-04-01')
;

select * from demo;

art_code  first_date  last_date
-------- ----------- ----------
     123 2022-01-01  2022-01-31
     123 2022-02-01  2022-02-28
     456 2022-02-28  2022-04-01
     123 2022-03-01  2022-04-01

drop table demo;