calculer la durée de survenue d'un événement dans une série chronologique

Problème posé

La table mesure contient 2 colonnes :

  • mesure_date : la date/heure/minute d'une mesure
  • oxygene : le taux d'oxygène mesuré

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.

Méthode employé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_date
from mesure m
where m.oxygene < 4
order 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_last

Grâ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_last
2005-06-23 11:04:00    2005-06-23 12:00:00
2005-06-23 11:14:00    2005-06-23 12:00:00
2005-06-23 11:24:00    2005-06-23 12:00:00
2005-06-23 11:34:00    2005-06-23 12:00:00
2005-06-23 11:44:00    2005-06-23 12:00:00
2005-06-23 11:54:00    2005-06-23 12:00:00
2005-06-23 16:56:00    2005-06-23 18:20:00
2005-06-23 17:06:00    2005-06-23 18:20:00
2005-06-23 17:16:00    2005-06-23 18:20:00
2005-06-23 17:26:00    2005-06-23 18:20:00
2005-06-23 17:36:00    2005-06-23 18:20:00
2005-06-23 17:46:00    2005-06-23 18:20:00
2005-06-23 17:56:00    2005-06-23 18:20:00
2005-06-23 18:06:00    2005-06-23 18:20:00
2005-06-23 18:16:00    2005-06-23 18:20:00

Ce 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_minute
from minmax

Cette 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_minute
2005-06-23 11:04:00    2005-06-23 12:00:00    56
2005-06-23 17:06:00    2005-06-23 18:20:00    74
2005-06-23 22:18:00    2005-06-24 04:33:00    375
2005-06-24 06:29:00    2005-06-24 07:33:00    64
2005-06-24 13:19:00    2005-06-24 16:50:00    211

Et oui, nous avons travaillé avec 3 requêtes imbriquées...