Wie identifiziere ich überlappende Datumsbereiche in PostgreSQL für die Mitgliedschaft im Spielerteam?

Identifizieren überlappender Datumsbereiche in PostgreSQL

Problem

Ein Benutzer möchte eine Liste von Spielern abrufen, die zu einem gehörten bestimmtes Team innerhalb eines bestimmten Jahresbereichs. Die ursprünglich vorgeschlagene Abfrage lautet wie folgt:

SELECT * 
FROM   contract 
JOIN   team USING (name_team) 
JOIN   player USING(name_player) 
WHERE  name_team = ? 
AND    DATE_PART('YEAR',date_join)>= ? 
AND    DATE_PART('YEAR',date_leave)<= ?

Diese Abfrage identifiziert jedoch überlappende Datumsbereiche nicht effektiv.

Richtige Lösung

An Um überlappende Datumsbereiche genau zu bestimmen, muss die Abfrage geändert werden, um die folgenden Kriterien zu berücksichtigen:

1. Spieler, die dem Team beigetreten sind vor Beginn des angegebenen Jahresbereichs.
2. Spieler, die das Team nicht vor dem Ende des angegebenen Jahresbereichs verlassen haben.
3. Spieler, die das Team nie verlassen haben (dargestellt durch NULL date_leave-Werte) .

Optimierte Abfrage

Die folgende Abfrage liefert eine genauere Lösung:

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

Erklärung

• Die COALESCE-Funktion wird verwendet, um NULL date_leave-Werte zu verarbeiten und sie als offene Bereiche zu betrachten.
• Der OVERLAPS-Operator legt den Beginn jedes Intervalls automatisch als den früheren Wert des Paares fest.
• Der Datumsbereich „[2009-01-01,2010-01-01)“ stellt ein halboffenes Intervall ohne die Obergrenze dar.

Zusätzliche Überlegungen

• In PostgreSQL 9.2 oder höher können Bereichstypen mit Indexunterstützung verwendet werden, was eine verbesserte Leistung für bereichsbasierte Anwendungen bietet Abfragen.
• Die bereitgestellte Abfrage kann weiter optimiert werden, indem ein Ausdrucksindex für den Ausdruck daterange(date_join, date_leave) erstellt wird.

Das obige ist der detaillierte Inhalt vonWie identifiziere ich überlappende Datumsbereiche in PostgreSQL für die Mitgliedschaft im Spielerteam?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!