Accueil / Blog / Métier / 2014 / Détecter un changement de valeurs entre deux lignes avec PostgreSQL

Détecter un changement de valeurs entre deux lignes avec PostgreSQL

Par Florent Lebreton publié 22/05/2014
Contributeurs : Régis Leroy
Une fonction de fenêtrage permet de réaliser des calculs sur un ensemble de lignes qui sont liées à la ligne courante. Voici un exemple d'utilisation des fonctions de fenêtrage lag et lead pour détecter un changement de valeur entre deux lignes successives.
Détecter un changement de valeurs entre deux lignes avec PostgreSQL

Sur un projet Django en cours, j'ai eu il y a quelques jours à résoudre un problème SQL que je n'avais encore jamais rencontré ... quelque chose comme "La dernière fois que cette valeur a changé entre deux enregistrements de la table".

Fonctions de fenêtrage

Pour résoudre ce type de problématique, ou n'importe qu'elle requête pour laquelle vous avez à comparer des lignes semblables, PostgreSQL founit un outil très pratique: les fonctions de fenêtrage ou window functions. La documentation officielle de PostgreSQL introduit cette fonctionnalité en disant:

Une fonction de fenêtrage effectue un calcul sur un jeu d'enregistrements liés d'une certaine façon à l'enregistrement courant. On peut les rapprocher des calculs réalisables par une fonction d'agrégat mais, contrairement à une fonction d'agrégat, l'utilisation d'une fonction de fenêtrage (de fenêtrage) n'entraîne pas le regroupement des enregistrements traités en un seul. Chaque enregistrement garde son identité propre. En coulisse, la fonction de fenêtrage est capable d'accéder à d'autres enregistrements que l'enregistrement courant du résultat de la requête.

Différentes fonctions de fenêtrage natives permettent de calculer le rang d'un enregistrement dans un sous-ensemble de lignes, de récupérer une valeur d'un enregistrement précédent ou suivant, etc. Une fonction de ce type doit être invoquée selon la syntaxe appropriée, avec la clause OVER.

Un exemple simple

Voici un cas d'utilisation simpliste mais assez parlant :

  • Chaque jour, je prends des notes sur la météo (quelle température fait-il ? est-ce qu'il pleut ?)

  • Je souhaite extraire certaines informations comme :

    • Quels jours le temps a-t-il changé ?
    • Quand est-ce qu'il a commencé à pleuvoir pour la dernière fois ?
    • ...

En base de données, une représentation toute simple donne ceci :

db=> \d weather
          Table « public.weather »
    Column      |  Type   | Modifiers
    ------------+---------+---------------
    date        | date    | non NULL
    temperature | integer | non NULL
    rainy       | boolean | non NULL

et avec quelques données :

db=> SELECT * FROM weather ORDER BY date DESC;
    date       | temperature | rainy
    -----------+-------------+-------
    2014-04-08 |          22 | f
    2014-04-07 |          20 | f
    2014-04-06 |          16 | t
    2014-04-05 |          18 | t
    2014-04-04 |          19 | t
    2014-04-03 |          22 | f
    2014-04-02 |          20 | f
    2014-04-01 |          18 | t

La partie très intéressante est ici : grâce aux fonctions de fenêtrage lag et lead, il est possible de sélectionner pour chaque valeur d'un enregistrement la valeur des enregistrements précédent et suivant :

SELECT
    date,
    rainy,
    lead(rainy) OVER (ORDER BY date DESC) as prev_rainy,
    lag(rainy) OVER (ORDER BY date DESC) as next_rainy
FROM
    weather
ORDER BY
    date DESC
    date    | rainy | prev_rainy | next_rainy
------------+-------+------------+------------
 2014-04-08 | f     | f          |
 2014-04-07 | f     | t          | f
 2014-04-06 | t     | t          | f
 2014-04-05 | t     | t          | t
 2014-04-04 | t     | f          | t
 2014-04-03 | f     | f          | t
 2014-04-02 | f     | t          | f
 2014-04-01 | t     |            | f

Il est à noter que la clause ORDER BY est très importante dans cet exemple.

En imbriquant ceci dans une requête plus construite, il est possible de détecter les changements de valeurs entre lignes successives. Par exemple, la requête ci-dessous donne "chaque jour où le temps a changé" (changement de valeur du booléen rainy) :

SELECT
    w1.date, w1.rainy
FROM
    (SELECT
        w2.date,
        w2.rainy,
        lead(w2.rainy) OVER (ORDER BY w2.date DESC) as prev_rainy
     FROM
        weather w2
     ORDER BY
        w2.date DESC) as w1
WHERE
    w1.rainy IS DISTINCT FROM w1.prev_rainy
ORDER BY
    w1.date DESC;
    date   | rainy
-----------+-------
2014-04-07 | f
2014-04-04 | t
2014-04-02 | f
2014-04-01 | t

À partir de cette première sélection, il est facile d'extraire des informations supplémentaires comme "la dernière fois qu'il a commencé faire beau" :

SELECT
    w1.date, w1.rainy
FROM
    (SELECT
        w2.date,
        w2.rainy,
        lead(w2.rainy) OVER (ORDER BY w2.date DESC) as prev_rainy
     FROM
        weather w2
     ORDER BY
        w2.date DESC) as w1
WHERE
    w1.rainy IS DISTINCT FROM w1.prev_rainy
AND
    w1.rainy IS FALSE
ORDER BY
    w1.date DESC
LIMIT 1;
    date   | rainy
-----------+-------
2014-04-07 | f

Aller plus loin

Le cas d'utilisation ci-dessus est juste un exemple axé sur les fonctions de fenêtrage lag et lead (je n'ai d'ailleurs pas d'autre solution en tête pour résoudre ce type de problème) mais PostgreSQL fournit d'autres fonctions de fenêtrage natives.

De plus, il est possible d'appeler n'importe quelle fonction d'agrégat native ou définie programmatiquement comme une fonction de fenêtrage !

Restons connectés

ABONNEZ-VOUS À LA NEWSLETTER !
Voir aussi
Formation PostgreSQL / PostGIS du 27 au 29 septembre à Paris Formation PostgreSQL / PostGIS du 27 au 29 septembre à Paris 06/07/2016

Découvrez les outils Libres pour gérer vos données spatiales.

Nouvelle formation base de données PostgreSQL Nouvelle formation base de données PostgreSQL 10/12/2015

Découvrez notre plan de formation PostgreSQL disponible sur Toulouse, Nantes et Paris.

What you don't know about PostgreSQL 8.3 What you don't know about PostgreSQL 8.3 30/10/2008

With the new PostgreSQL server versions in place (8.2 and 8.3) and in a more general way in the 8.x ...

Dessiner une ville à la main avec TileMill (3 – Occupation des sols et cours d'eau) Dessiner une ville à la main avec TileMill (3 – Occupation des sols et cours d'eau) 20/03/2014

Nous avons vu comment représenter les bâtiments. Je m'intéresse maintenant à la mise en forme ...

Dessiner une ville à la main avec TileMill (2 - Découper 340 000 polygones) Dessiner une ville à la main avec TileMill (2 - Découper 340 000 polygones) 19/03/2014

Comme nous l'avons vu dans un précédent article, la création d'une carte urbaine reproduisant un ...