mehrfach überlappende Datumsbereiche finden

[wicki]

Hi zusammen,

SQL und Postgres ist nicht wirklich etwas neues für mich - aber dieses
Problemstellung treibt mich grad in den Wahnsinn.
Ich vermute ein Problem akuter Betriebsblindheit und frage jetzt hier mal nach:

Es gibt eine relaltiv "billige" Tabelle

Column | Type
----------+-----------------------
cid | integer
blockvon | date
blockbis | date
Check constraints:
"bldc" CHECK (blockvon <= blockbis)

Diese Tabelle beinhaltet für die jeweilige "id" gesperrte Datumsbereiche.
Ein Trigger verhindert zudem, dass sich Bereiche einer ID bereits überlappen.
Nun kommt das Problem:
Ich möchte eine Liste haben, die alle Sperrzeiten beinhaltet, die für bestimmte
IDs _gleichzeitig_ gelten.

Aber nur das jeweilige Start- und End-Datum dieser Bereiche.


Die diesem Inhalt
cid | blockvon | blockbis
--------+------------+------------
100204 | 2020-11-05 | 2020-12-27
100204 | 2021-02-04 | 2021-02-06
100204 | 2021-03-01 | 2021-03-31
100204 | 2021-01-04 | 2021-01-14
100205 | 2020-11-11 | 2020-12-31
100209 | 2020-12-20 | 2020-12-21
100209 | 2020-12-23 | 2020-12-25
(7 rows)

liefert mir dieses Query:
select (x.blockvon,x.blockbis) overlaps (y.blockvon,y.blockbis),x.cid,x.blockvon,y.blockbis,y.cid,y.blockvon,x.blockbis FROM pgf_park_blockdates as x,pgf_park_blockdates as y where (x.blockvon,x.blockbis) overlaps (y.blockvon,y.blockbis) and x.cid in(100204,100205,100209) and x.cid in(100204,100205,100209) and (x. blockvon <>y. blockvon) and (x.blockbis <>y.blockbis) order by x.blockvon, x.cid ;
LOG: duration: 0.945 ms statement: select (x.blockvon,x.blockbis) overlaps (y.blockvon,y.blockbis),x.cid,x.blockvon,y.blockbis,y.cid,y.blockvon,x.blockbis FROM pgf_park_blockdates as x,pgf_park_blockdates as y where (x.blockvon,x.blockbis) overlaps (y.blockvon,y.blockbis) and x.cid in(100204,100205,100209) and x.cid in(100204,100205,100209) and (x. blockvon <>y. blockvon) and (x.blockbis <>y.blockbis) order by x.blockvon, x.cid ;

Dieses Resultat
overlaps | cid | blockvon | blockbis | cid | blockvon | blockbis
----------+--------+------------+------------+--------+------------+------------
t | 100204 | 2020-11-05 | 2020-12-21 | 100209 | 2020-12-20 | 2020-12-27
t | 100204 | 2020-11-05 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-27
t | 100204 | 2020-11-05 | 2020-12-25 | 100209 | 2020-12-23 | 2020-12-27
t | 100205 | 2020-11-11 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-31
t | 100205 | 2020-11-11 | 2020-12-25 | 100209 | 2020-12-23 | 2020-12-31
t | 100205 | 2020-11-11 | 2020-12-21 | 100209 | 2020-12-20 | 2020-12-31
t | 100209 | 2020-12-20 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-21
t | 100209 | 2020-12-20 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-21
t | 100209 | 2020-12-23 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-25
t | 100209 | 2020-12-23 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-25


So weit, so gut - das kann ich jetzt als View ablegen und auswerten.
Aber wie bekomme ich es hin, dass ich nur den Zeitbereich
2020-12-20 bis 2020-12-21
und
2020-12-23 bis 2020-12-25

zurück geliefert bekomme?
Ist das in einem einzelnen Statement möglich? (ohne PL/PgSQL)
oder gar in Standard-SQL?

Bislang ist mir noch nichts dazu eingefallen.
Ich vermute, ich sehe den Wald vor lauter Bäumen nicht.....

 

[akretschmer]

Dein Ergebniss paßt irgendwie nicht zu den Ausgangsdaten. Da sehe ich kein (100209,2020-12-20,2020-12-31), als Beispiel.

Die Lösung, via TRIGGER zu verhindern, daß sich Bereiche einer ID überlappen, ist suboptimal, da wir exclusion constraints haben. Damit ließe sich das vieeeeel eleganter verhindern.

Derzeit überlappen sich :

test=*# select * from wicki ;
   id   |    von     |    bis     
--------+------------+------------
 100204 | 2020-11-05 | 2020-12-27
 100204 | 2021-02-04 | 2021-02-06
 100204 | 2021-03-01 | 2021-03-31
 100204 | 2021-01-04 | 2021-01-14
 100205 | 2020-11-11 | 2020-12-31
 100209 | 2020-12-20 | 2020-12-21
 100209 | 2020-12-23 | 2020-12-25
(7 rows)

test=*# select distinct  * from wicki w1 cross join wicki w2 where daterange(w1.von,w1.bis) && daterange(w2.von,w2.bis) and w1.id!=w2.id;
   id   |    von     |    bis     |   id   |    von     |    bis     
--------+------------+------------+--------+------------+------------
 100209 | 2020-12-23 | 2020-12-25 | 100205 | 2020-11-11 | 2020-12-31
 100204 | 2020-11-05 | 2020-12-27 | 100205 | 2020-11-11 | 2020-12-31
 100205 | 2020-11-11 | 2020-12-31 | 100204 | 2020-11-05 | 2020-12-27
 100209 | 2020-12-23 | 2020-12-25 | 100204 | 2020-11-05 | 2020-12-27
 100205 | 2020-11-11 | 2020-12-31 | 100209 | 2020-12-20 | 2020-12-21
 100205 | 2020-11-11 | 2020-12-31 | 100209 | 2020-12-23 | 2020-12-25
 100209 | 2020-12-20 | 2020-12-21 | 100205 | 2020-11-11 | 2020-12-31
 100204 | 2020-11-05 | 2020-12-27 | 100209 | 2020-12-20 | 2020-12-21
 100209 | 2020-12-20 | 2020-12-21 | 100204 | 2020-11-05 | 2020-12-27
 100204 | 2020-11-05 | 2020-12-27 | 100209 | 2020-12-23 | 2020-12-25
(10 rows)

wo willst Du nin noch einen Zeitbereich begrenzen?

 

[akretschmer]

um das mit dem exclusion constraint zu zeigen:

test=*# create extension btree_gist;
CREATE EXTENSION
test=*# create table hotel (zimmer int, von_bis daterange, exclude using gist(zimmer with =, von_bis with &&));
CREATE TABLE
test=*# insert into hotel values (1, '[2020-12-01,2020-12-20)');
INSERT 0 1
test=*# insert into hotel values (2, '[2020-12-01,2020-12-20)');
INSERT 0 1
test=*# insert into hotel values (2, '[2020-12-10,2020-12-30)');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »hotel_zimmer_von_bis_excl«
DETAIL:  Schlüssel (zimmer, von_bis)=(2, [2020-12-10,2020-12-30)) kollidiert mit vorhandenem Schlüssel (zimmer, von_bis)=(2, [2020-12-01,2020-12-20)).
test=*#

Man braucht also keinen TRIGGER, der Constraint erzeugt einen speziellen Index:

test=*# \d hotel
                 Table "public.hotel"
 Column  |   Type    | Collation | Nullable | Default
---------+-----------+-----------+----------+---------
 zimmer  | integer   |           |          |
 von_bis | daterange |           |          |
Indexes:
    "hotel_zimmer_von_bis_excl" EXCLUDE USING gist (zimmer WITH =, von_bis WITH &&)

test=*#

Das ist definitiv schneller als Dein TRIGGER ...

 

[wicki]

hi andreas,

[....]
"Das ist definitiv schneller als Dein TRIGGER ..."

Ja, es gibt sicher bessere Lösungen als n Trigger - aber ist vollkommen ohne
Belang - Das wird 2 x im Jahr aufgerufen.
Eigentlich dürfen sich die Bereiche auch gern überlappen - das ist nur wg. der
Übersichtlichkeit der DB drin.

"Dein Ergebniss paßt irgendwie nicht zu den Ausgangsdaten. Da sehe ich kein (100209,2020-12-20,2020-12-31), als Beispiel."

da gibt es ja auch kein (100209,2020-12-20,2020-12-31) -

id | von | bis
--------+------------+------------
100204 | 2020-11-05 | 2020-12-27
100204 | 2021-02-04 | 2021-02-06
100204 | 2021-03-01 | 2021-03-31
100204 | 2021-01-04 | 2021-01-14
100205 | 2020-11-11 | 2020-12-31
100209 | 2020-12-20 | 2020-12-21 <--- da steht es es
100209 | 2020-12-23 | 2020-12-25 <--- und da auch

Nur in diesen Zeitfenstern sind alle 3 IDs gleichzeitig geblockt.

2020-12-20 bis 2020-12-21
und
2020-12-23 bis 2020-12-25

insert into VALUES (100209, '2020-12-20', '2020-12-31')
würde auch eine bl_overlap_check() Exception auslösen;


Wenn ich den Trigger wegwerfe und dieses Datum zusätzlich
reinschreibe, dann sieht das Ergebnis so aus:
overlaps | cid | blockvon | blockbis | cid | blockvon | blockbis
----------+--------+------------+------------+--------+------------+------------
t | 100204 | 2020-11-05 | 2020-12-31 | 100209 | 2020-12-20 | 2020-12-27
t | 100204 | 2020-11-05 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-27
t | 100204 | 2020-11-05 | 2020-12-25 | 100209 | 2020-12-23 | 2020-12-27
t | 100204 | 2020-11-05 | 2020-12-21 | 100209 | 2020-12-20 | 2020-12-27
t | 100205 | 2020-11-11 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-31
t | 100205 | 2020-11-11 | 2020-12-21 | 100209 | 2020-12-20 | 2020-12-31
t | 100205 | 2020-11-11 | 2020-12-25 | 100209 | 2020-12-23 | 2020-12-31
t | 100209 | 2020-12-20 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-21
t | 100209 | 2020-12-20 | 2020-12-25 | 100209 | 2020-12-23 | 2020-12-31
t | 100209 | 2020-12-20 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-31
t | 100209 | 2020-12-20 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-21
t | 100209 | 2020-12-23 | 2020-12-27 | 100204 | 2020-11-05 | 2020-12-25
t | 100209 | 2020-12-23 | 2020-12-31 | 100205 | 2020-11-11 | 2020-12-25
t | 100209 | 2020-12-23 | 2020-12-31 | 100209 | 2020-12-20 | 2020-12-25
(14 rows)

und das Resutat müsste dann sein:
2020-12-20 bis 2020-12-27