Comment identifier les plages de dates qui se chevauchent dans PostgreSQL pour l'adhésion à l'équipe de joueurs ?

Identification des plages de dates qui se chevauchent dans PostgreSQL

Problème

Un utilisateur cherche à récupérer une liste de joueurs ayant appartenu à un équipe spécifique au cours d’une période d’année donnée. La requête initialement proposée est la suivante :

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)<= ?

Cependant, cette requête n'identifie pas efficacement les plages de dates qui se chevauchent.

Solution correcte

Pour déterminer avec précision les plages de dates qui se chevauchent, la requête doit être modifiée pour prendre en compte les critères suivants :

1. Joueurs ayant rejoint l'équipe avant le début de la plage d'années spécifiée.
2. Joueurs qui n'ont pas quitté l'équipe avant la fin de la plage d'années spécifiée.
3. Joueurs qui n'ont jamais quitté l'équipe (représentés par des valeurs NULL date_leave).

Requête optimisée

La requête suivante fournit une requête plus précise solution :

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

Explication

• La fonction COALESCE est utilisée pour gérer les valeurs NULL date_leave, en les considérant comme des plages ouvertes.
• L'opérateur OVERLAPS définit automatiquement le début de chaque intervalle comme la valeur antérieure de la paire.
• La plage de dates '[2009-01-01,2010-01-01)' représente un intervalle semi-ouvert, à l'exclusion de la limite supérieure.

Considérations supplémentaires

• Dans PostgreSQL 9.2 ou version ultérieure, les types de plage peuvent être utilisés avec la prise en charge des index, offrant ainsi des performances améliorées pour les types de plages. requêtes.
• La requête fournie peut être optimisée davantage en créant un index d'expression sur l'expression daterange(date_join, date_leave).

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!