La table mesure contient 2 colonnes :
Nous voulons calculer la durée pendant laquelle le taux d'oxygène descend en dessous d'une certaine valeur, pour chaque période (ou événement) concernée.
Dans un premier temps, la requête est très simple : nous recherchons les enregistrements pendant lesquels le taux d'oxygène est inférieur à 4 :
select m.mesure_datefrom mesure mwhere m.oxygene < 4order by 1;La première difficulté consiste à identifier la dernière date de l'événement, c'est à dire la dernière date avant que la valeur ne revienne au-dessus du seuil. Pour cela, nous allons rechercher la première valeur supérieure ou égale à 4, dont la date est strictement supérieure à la date initiale : c'est la première mesure obtenue après la fin de l'événement. Nous aurons ainsi la sous-requête suivante :
select min(m2.mesure_date) from mesure m2 where m2.mesure_date > m.mesure_date and m2.oxygene >= 4À partir de là, il est facile de récupérer la dernière date de l'événement : il s'agit de la date la plus récente (max(mesure_date)) supérieure ou égale à la date de départ, et inférieure à la date calculée précédemment :
select max (m1.mesure_date) from mesure m1 where m1.mesure_date >= m.mesure_date and m1.mesure_date < ( select min(m2.mesure_date) from mesure m2 where m2.mesure_date > m.mesure_date and m2.oxygene >= 4) ) as date_lastGrâce à ce script, nous récupérons facilement le tableau suivant, après avoir inséré la sous-requête précédente dans la première requête :
date_debut date_last2005-06-23 11:04:00 2005-06-23 12:00:002005-06-23 11:14:00 2005-06-23 12:00:002005-06-23 11:24:00 2005-06-23 12:00:002005-06-23 11:34:00 2005-06-23 12:00:002005-06-23 11:44:00 2005-06-23 12:00:002005-06-23 11:54:00 2005-06-23 12:00:002005-06-23 16:56:00 2005-06-23 18:20:002005-06-23 17:06:00 2005-06-23 18:20:002005-06-23 17:16:00 2005-06-23 18:20:002005-06-23 17:26:00 2005-06-23 18:20:002005-06-23 17:36:00 2005-06-23 18:20:002005-06-23 17:46:00 2005-06-23 18:20:002005-06-23 17:56:00 2005-06-23 18:20:002005-06-23 18:06:00 2005-06-23 18:20:002005-06-23 18:16:00 2005-06-23 18:20:00Ce n'est pas tout à fait satisfaisant : si la date de fin est bien la même partout, nous avons une ligne pour chaque mesure pendant l'événement : il faut donc ne conserver que la première ligne. Pour cela, nous pouvons nous appuyer sur l'implémentation particulière de PostgreSQL en ce qui concerne la clause DISTINCT : il est possible de ne la faire porter que sur une colonne particulière. Dans notre cas particulier, nous rajouterons la clause ainsi :
select distinct on (date_last) date_debut, date_last (...)Mais voilà : la clause DISTINCT ON ne peut pas travailler sur une colonne calculée... Il faut donc passer par une requête intermédiaire, grâce à la clause WITH, qui permet de créer une requête temporaire préalable.
with minmax as ( select m.mesure_date as date_debut, (select max (m1.mesure_date) from mesure m1 where m1.mesure_date >= m.mesure_date and m1.mesure_date < (select min(m2.mesure_date) from mesure m2 where m2.mesure_date > m.mesure_date and m2.oxygene >= 4) ) as date_last from mesure m where m.oxygene < 4 and m.oxygene > 0 order by 1)select distinct on (date_last) date_debut, date_last, (extract (minute from (date_last - date_debut)) + extract(hour from (date_last - date_debut)) * 60 + extract(day from (date_last - date_debut)) * 1440) as nb_minutefrom minmaxCette première requête crée une table temporaire appelée ici minmax, qui sera utilisée, dans la seconde partie, pour intégrer la clause DISTINCT ON () sur la valeur date_last calculée précédemment.
La clause DISTINCT ON conserve la première ligne rencontrée, d'où l'importance de trier la première requête par m.mesure_date (order by 1).
Nous en avons profité pour calculer le nombre de minutes entre deux événements, en convertissant les différentes valeurs de l'intervalle en minutes (aucun événement ne dure plus d'une année).
Et voilà le résultat :
date_debut date_last nb_minute2005-06-23 11:04:00 2005-06-23 12:00:00 562005-06-23 17:06:00 2005-06-23 18:20:00 742005-06-23 22:18:00 2005-06-24 04:33:00 3752005-06-24 06:29:00 2005-06-24 07:33:00 642005-06-24 13:19:00 2005-06-24 16:50:00 211Et oui, nous avons travaillé avec 3 requêtes imbriquées...