Les intervalles de valeurs
- 6 minutes de lecture
Ce mois-ci, je vous propose de réviser un peu le langage SQL en l’appliquant pour des cas d’usage assez fréquents qui mettent en scène des types temporels, notamment les intervalles de dates. Ce sera l’occasion également de revenir sur l’implémentation très originale qu’en a fait PostgreSQL avec les types d’intervalle de valeurs, ou range types dans la documentation.
Les intervalles de valeurs sont des types dits complexes, au même titre que les tableaux, le JSON ou les types géométriques. Chacun propose une réponse plus adaptée à un problème donné, bien plus confortable que les types numériques, temporels ou textuels présents dans tous les sytèmes de bases de données.
Par défaut, PostgreSQL supporte les intervalles de types numériques (int4, int8
et numeric), horodatés (timestamp avec sans timezone) et datés (date).
Pour l’exemple, nous pouvons prendre le dernier de ces trois types pour répondre
à la question suivante : « Lesquels de mes salariés (dans un table staff) est
en activité aujourd’hui ? »
Mon jeu de données présente deux types de contrat de travail, durée
déterminée et durée indéterminée, il faut donc que je gère les bornes supérieures
pouvant être nulles. En l’absence d’intervalle de valeurs, il est possible de
définir deux colonnes de types date avec une série de condition sur le début
et la fin de contrat d’un⋅e salarié⋅e, comme suit :
SELECT id, name, start, finish FROM staff
WHERE current_date BETWEEN start AND finish
OR (current_date >= start AND finish IS NULL);
id | name | start | finish
----+----------+------------+------------
1 | Élodie | 2020-05-01 |
3 | Stéphane | 2021-01-04 | 2021-07-01
5 | Martine | 2021-02-01 |
6 | Philippe | 2021-02-20 | 2021-07-02
7 | Jean | 2021-06-01 |
Ici, nous cherchons donc à retrouver les lignes dont la date du jour current_date
est comprise dans un intervalle borné par deux dates. Ces deux colonnes peuvent
se fusionner en un type dédié à l’aide de la fonction daterange(). Son troisième
argument correspond à l’inclusion de valeurs des bornes basses et hautes au sein
de l’intervalle. Dans le cas de notre table staff, les deux bornes sont incluses.
SELECT name, start, finish, daterange(start, finish, '[]') AS period
FROM staff WHERE id IN (1,2);
name | start | finish | period
--------+------------+------------+-------------------------
Élodie | 2020-05-01 | | [2020-05-01,)
Louise | 2021-01-04 | 2021-02-25 | [2021-01-04,2021-02-26)
Ainsi, il devient plus aisé d’écrire la recherche des salariés actifs à l’aide
de l’opérateur d’inclusion @> sur ce nouveau champ. Dans le cas d’un
contrat à durée indéterminée, la borne haute avec une valeur nulle représente
l’infini et sera parfaitement interprétée par la clause d’inclusion de notre
recherche.
SELECT id, name, period FROM staff
WHERE period @> current_date;
id | name | period
----+----------+-------------------------
1 | Élodie | [2020-05-01,)
3 | Stéphane | [2021-01-04,2021-07-02)
5 | Martine | [2021-02-01,)
6 | Philippe | [2021-02-20,2021-07-03)
7 | Jean | [2021-06-01,)
Une variante plus poussée serait de demander la liste des salariés pleinement actifs durant le mois de mars. L’opérateur est tout à fait capable de déterminer si l’un des deux intervalles est inclus dans le second.
SELECT id, name, period FROM staff
WHERE period @> daterange('2021-03-01', '2021-04-01');
id | name | period
----+----------+-------------------------
1 | Élodie | [2020-05-01,)
3 | Stéphane | [2021-01-04,2021-07-02)
5 | Martine | [2021-02-01,)
6 | Philippe | [2021-02-20,2021-07-03)
Il peut être possible que nous cherchions également à comparer deux intervalles, par exemple pour le calcul d’une intersection ou la recherche de chevauchement. Prenons le nouveau problème suivant : « Quels sont les salariés qu’Édouard est susceptible d’avoir connu durant la durée de son contrat de travail ? »
Dans ce cas de figure, nous cherchons le chevauchement entre les dates de début
et de fin de contrat entre deux ensembles. Les requêtes suivantes sont
équivalentes afin de comprendre ce que réalise l’opérateur && entre deux
intervalles.
SELECT s1.id, s1.name FROM staff s1 JOIN staff s2
ON (s1.start <= s2.finish OR s2.finish IS NULL)
AND (s1.finish >= s2.start OR s1.finish IS NULL)
WHERE s1.name <> s2.name AND s2.name = 'Édouard';
SELECT s1.id, s1.name FROM staff s1 JOIN staff s2
ON s1.period && s2.period
WHERE s1.name <> s2.name AND s2.name = 'Édouard';
id | name
----+----------
1 | Élodie
3 | Stéphane
5 | Martine
6 | Philippe
7 | Jean
Au délà des opérateurs qui permettent de réduire notre ensemble de données, il
existe également une série de fonctions dédiées aux intervalles comme celle
citée plus haut, daterange(). Grâce à certaines d’entre elles, il devient possible
de répondre à une question plus large que la première de cet article, à savoir :
« Quels salariés font ou feront partie de mes effectifs à compter d’aujourd’hui ? »
Les méthodes upper() et upper_inf() permettent de traiter l’intervalle sur la
seule borne haute comme une simple date, respectivement l’une extrait la dernière
date de l’intervalle, quant à l’autre, elle détermine si la borne haute correspond
à l’infini (et retourne un bool). Les deux expressions suivantes sont ainsi
équivalentes.
SELECT id, name, start, finish FROM staff
WHERE current_date < finish OR finish IS NULL;
SELECT id, name, period FROM staff
WHERE current_date < upper(period) OR upper_inf(period);
id | name | period
----+----------+-------------------------
1 | Élodie | [2020-05-01,)
3 | Stéphane | [2021-01-04,2021-07-02)
5 | Martine | [2021-02-01,)
6 | Philippe | [2021-02-20,2021-07-03)
7 | Jean | [2021-06-01,)
9 | Lucas | [2021-07-01,2021-09-01)
10 | Mickaël | [2021-07-01,)
À partir de la version 14, actuellement en beta1 au moment où j’écris ces lignes,
il sera possible de réaliser des aggrégations d’ensemble sur les intervalles de
valeurs. Les méthodes range_agg() et range_intersect_agg() ont été pensées
pour les requêtes de regroupement avec GROUP BY à l’image de la fonction
range_merge() et de l’opérateur *, respectivement l’union et l’intersection
des données. Ces fonctions ont vu le jour grâce au support des multi-intervalles
publié en décembre dernier.
La requête suivante permet de résoudre le problème : « Quels jours seraient propice à un rassemblement général avec l’ensemble des salariés dans un avenir proche ? »
SELECT range_intersect_agg(period) FROM staff
WHERE current_date < upper(period) OR upper_inf(period);
range_intersect_agg
-------------------------
[2021-07-01,2021-07-02)
Parmi les salariés actuels et futurs, l’intersection de toutes les périodes d’activité ne laisse que la date du 1er juillet pour organiser une rencontre. Ce résultat peut devenir incertain, si un contrat était interrompu avant la date ou qu’une nouvelle personne venait à rejoindre les effectifs au-delà de cette date.
INSERT INTO staff (name, start, finish)
VALUES ('Marie', '2021-08-01', null);
SELECT range_intersect_agg(period) FROM staff
WHERE current_date < upper(period) OR upper_inf(period);
range_intersect_agg
---------------------
empty
La valeur empty correspond à l’intervalle nul, celui qui ne contient aucune
valeur. Il n’y aurait donc dans notre ensemble de données, aucune date possible
pour faire converger le planning de tout le personnel.
Conclusion
Manipuler les dates en tant qu’intervalles permet de résoudre des situations cocasses tels que les chevauchements de planning ou les réservations de salle. Il s’agit d’ailleurs d’un des cas d’usage promu par la documentation, avec la gestion des contraintes et de la méthode d’accès GiST au service de la cohérence des données.
CREATE TABLE reservation (
during tsrange,
EXCLUDE USING GIST (during WITH &&)
);
INSERT INTO reservation VALUES
('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR: conflicting key value violates exclusion constraint
DETAIL: Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00"))
conflicts with existing key
(during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).