Finden überschneidende Zeiträume in PostgreSQL

Den derzeit akzeptierte Antwort Antwort auf die Frage nicht. Und es ist prinzipiell falsch. a BETWEEN x AND y übersetzt:

a >= x AND a <= y

Einschließlich der oberen Grenze, während die Menschen müssen in der Regel ausschließen es:

a >= x AND a < y

Mit Termine können Sie leicht anpassen. Für das Jahr 2009 verwenden '2009-12-31' als Obere Grenze.

Aber es ist nicht so einfach mit Zeitstempel, die es erlauben Nachkommastellen. Moderne Postgres-Versionen verwenden Sie ein 8-byte-Ganzzahl intern zum speichern von bis zu 6 Bruch-Sekunden (µs Auflösung). Dieses wissen wir könnte noch machen, damit es funktioniert, aber das ist nicht intuitiv und richtet sich auf eine Implementierung detail. Schlechte Idee.

Darüber hinaus a BETWEEN x AND y findet nicht überlappende Bereiche. Wir brauchen:

b >= x AND a < y

Und Spieler, die nie verlassen sind nicht als, noch.

Richtige Antwort

Vorausgesetzt, die Jahr 2009, werde ich formuliere die Frage, ohne seine Bedeutung zu verändern:

"Finden sich alle Spieler eines Teams, die sich vor 2010 nicht verlassen, bevor Sie 2009."

Basic-Abfrage:

SELECT p.* 
FROM   team     t
JOIN   contract c USING (name_team) 
JOIN   player   p USING (name_player) 
WHERE  t.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND    c.date_leave >= date '2009-01-01';

Aber es gibt noch mehr:

Wenn die referenzielle Integrität erzwungen wird mit FK-constraints der Tabelle team selbst ist nur Lärm in die Abfrage und entfernt werden kann.

Während die gleichen Spieler verlassen können und wieder das gleiche team, wir brauchen auch die Falte möglich, Duplikate, zum Beispiel mit DISTINCT.

Und wir kann müssen für einen besonderen Fall: Spieler, die nie verlassen. Vorausgesetzt, die Spieler haben NULL in date_leave.

"Ein Spieler, der nicht bekannt ist, verlassen haben, wird davon ausgegangen, dass das spielen für die Mannschaft an diesem Tag."

Verfeinert Abfrage:

SELECT DISTINCT p.* 
FROM   contract c
JOIN   player   p USING (name_player) 
WHERE  c.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND   (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);

Operator-Rangfolge arbeitet gegen uns, AND bindet, bevor OR. Wir brauchen Klammern.

Ähnlichen Antwort mit optimierten DISTINCT (wenn Duplikate sind üblich):

• Viele Viele Tisch - Performance ist schlecht

Normalerweise Namen von natürlichen Personen sind nicht einzigartig und ein Surrogat Primärschlüssel verwendet wird. Aber offensichtlich name_player ist der Primärschlüssel der player. Wenn alles, was Sie brauchen, ist die Namen der Spieler, die wir nicht brauchen, die Tabelle player in der Abfrage entweder:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    date_join  <  date '2010-01-01'
AND   (date_leave >= date '2009-01-01' OR date_leave IS NULL);

SQL OVERLAPS Betreiber

Das Handbuch:

OVERLAPS nimmt automatisch den früheren Wert des Paares, wie die
start. Jeder Zeitraum wird als Vertretung des halb-offene
Intervall start <= time < end, es sei denn start und end gleich sind, in dem
Fall stellt es das einzige mal instant.

Kümmern potential NULL Werte, VERSCHMELZEN scheint am einfachsten:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
       (date '2009-01-01', date '2010-01-01');  -- upper bound excluded

Bereich Typ mit index-Unterstützung

In Postgres 9.2 oder höher Sie können auch arbeiten mit tatsächlichen range-Typen:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    daterange(date_join, date_leave) &&
       daterange '[2009-01-01,2010-01-01)';  -- upper bound excluded

Range-Typen hinzufügen, die einigen Aufwand und mehr Platz einnehmen. 2 x date = 8 Byte; 1 x daterange = 14 Byte auf der Festplatte oder 17 bytes im RAM. Aber in Kombination mit der overlap-operator && die Abfrage kann unterstützt werden mit einem GiST index.

Auch, keine Notwendigkeit zu speziellen-Fall NULL-Werte. NULL bedeutet "open range" in einem Bereich geben - genau das, was wir brauchen. Die definition der Tabelle selbst nicht verändern: wir können das Angebot geben Sie on-the-fly - und unterstützen Sie die Abfrage mit einem passenden Ausdruck-index:

CREATE INDEX mv_stock_dr_idx ON mv_stock USING gist (daterange(date_join, date_leave));

Verwandte:

• Durchschnitt Aktie Historie Tabelle