Fonction sql window pour détecter les changements dans les valeurs des colonnes

Question

J'aimerais détecter des changements dans les valeurs des colonnes de cette base de données (exemple).

    WITH events(id, row,event) AS (
    VALUES
    (1,1, 0 )
   ,(1,2, 0 ) 
   ,(1,3, 1 )
   ,(1,4, 0 )
   ,(1,5, 1 )
   ,(2,1, 0 )
   ,(2,2, 1 )
   ,(3,1, 0 )
   ,(3,2, 0 )
   )
   select * from events

Ce que je recherche, c'est un code pour une nouvelle colonne "code" qui passe à 1 APRÈS Dans le même identifiant, le code reste à 1. Pour cet exemple, cette nouvelle colonne ressemblera à ceci

    WITH events2(id, row,event, code) AS (
    VALUES
    (1,1, 0, 0 )
   ,(1,2, 0, 0 ) 
   ,(1,3, 1, 0 )
   ,(1,4, 0, 1 ) -- notice the switch here
   ,(1,5, 1, 1 ) -- 
   ,(2,1, 0, 0 )
   ,(2,2, 1, 0 )
   ,(3,1, 0, 0 )
   ,(3,2, 0, 0 )
   )
   select * from events2

J'ai l'intuition que la réponse sera liée à la réponse à cette question : Fonction fenêtre de PostgreSQL : partition par comparaison

D'une manière ou d'une autre, je n'arrive pas à comprendre moi-même

Pierre

Answer 1

COALESCE sur une sous-requête scalaire :

WITH events(id, zrow, zevent) AS (
    VALUES
    (1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
   ,(2,1, 0 ) ,(2,2, 1 )
   ,(3,1, 0 ) ,(3,2, 0 )
   )
SELECT id, zrow, zevent
        , COALESCE((SELECT 1 FROM events ex WHERE ex.id = ev.id AND ex.zrow < ev.zrow AND ex.zevent> 0),0) AS oevent
 FROM events ev
        ;

---

Vous pouvez également éviter la fonction COALESCE() en convertissant le booléen EXISTS() en INTEGER :

WITH events(id, zrow,event) AS (
    VALUES
    (1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
   ,(2,1, 0 ) ,(2,2, 1 )
   ,(3,1, 0 ) ,(3,2, 0 )
   )
SELECT id, zrow, event
        , EXISTS(SELECT 1 FROM events ex WHERE ex.id = ev.id AND ex.zrow < ev.zrow AND ex.event> 0)::integer AS oevent
FROM events ev
        ;

---

Trouver la valeur MAX() des enregistrements précédents dans le même groupe ( cadre ) :

WITH events(id, zrow,event) AS (
    VALUES
    (1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
   ,(2,1, 0 ) ,(2,2, 1 )
   ,(3,1, 0 ) ,(3,2, 0 )
   )
, drag AS (
        SELECT id, zrow, event, MAX(event)
                OVER (PARTITION BY id
                        ORDER BY zrow
                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                        ) AS lagged
        FROM events ev
        )
SELECT id, zrow, event
        , COALESCE(lagged,0) AS oevent
        FROM drag dr
        ;

---

La même chose sans le CTE supplémentaire :

WITH events(id, zrow,event) AS (
    VALUES
    (1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
   ,(2,1, 0 ) ,(2,2, 1 )
   ,(3,1, 0 ) ,(3,2, 0 )
   )
SELECT id, zrow, event, COALESCE(MAX(event) OVER (PARTITION BY id
                                                ORDER BY zrow
                                                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                                ),0) AS lagged
        FROM events ev
        ;

---

Une autre façon d'effectuer l'auto-jonction serait d'utiliser une requête récursive.