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