Forums PostgreSQL.fr

martina85

Membre

Deux lignes consécutives avec la même valeur dans un champ

Bonjour

j'ai une table comme la suivante :

ID          type             date
1              3                2015-03-01 13:12:21
2              7                2015-03-01 13:12:21
1              5                2015-03-01 13:12:27
4              4                2015-03-01 13:12:56
1              5                2015-03-01 13:15:27

chaque ligne est un événément. Les IDs sont les personnes qui font un événément.
je dois obtenir une table qui ne contient que les IDs qui ont fait l'événément de type "5" au moins deux fois consécutivement, et qui me dit combien de fois l'événément "5" a été fait consécutivement, sans la présence d'autres événéments dedans.
Par example, l'ID "1" ici a fait l'événément "5" deux fois consécutives sans faire d'autres actions entre les deux.
La table doit être comme la suivante :

ID           count(events)
1             2

vous savez comment m'aider ?
J'ai déjà cherché dans l'internet mais c'est difficile trouver les justes mots clés.

Merci

jpcarayo

Membre

Re : Deux lignes consécutives avec la même valeur dans un champ

Bonjour Martina,

Est que le solution suivante pourrait vous convenir ?

Table test de départ :
======================

postgres=# SELECT * FROM test ;

id | typid |         datid         
----+-------+------------------------
  1 |     3 | 2015-03-01 13:12:21+01
  2 |     7 | 2015-03-01 13:12:21+01
  1 |     5 | 2015-03-01 13:12:27+01  <-
  4 |     4 | 2015-03-01 13:12:56+01  <--
  1 |     5 | 2015-03-01 13:15:27+01  <-
  1 |     8 | 2015-03-01 13:16:21+01  <---
  1 |     8 | 2015-03-01 13:16:22+01  <---
  1 |     8 | 2015-03-01 13:17:21+01  <---
  1 |     5 | 2015-03-01 13:17:27+01
  1 |     3 | 2015-03-01 13:18:21+01
  4 |     4 | 2015-03-01 13:19:56+01  <--
(11 lignes)

Table nb_eventconsecutifs :
==================

postgres=# SELECT * FROM nb_eventconsecutifs  ;

id | nb
----+----
  1 |  5
  4 |  2
(2 lignes)

____________________________________________________

Ce résultat est obtenu grace à l'ordre SQL suivant :

CREATE TABLE nb_eventconsecutifs AS
SELECT id, count(typid) Nb
FROM ( SELECT t1.id, t1.typid, t1.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL
UNION
SELECT t1.id, t1.typid, t2.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL ) AS EventNb
GROUP BY id ;

Je vous laisse le soin de vérifier et de faire plus de tests pour valider (ou non) cette solution.
Je joins mes scripts de tests pour que vous puissiez les reproduire plus facilement si vous le souhaitez :

Creation de la table test:
CREATE TABLE test  AS SELECT 1 id, 2 typid, current_timestamp datid FROM T where 1=0 ;
INSERT INTO test VALUES ( 1,3,'2015-03-01 13:12:21') ;
INSERT INTO test VALUES ( 2,7,'2015-03-01 13:12:21') ;
INSERT INTO test VALUES ( 1,5,'2015-03-01 13:12:27') ;
INSERT INTO test VALUES ( 4,4,'2015-03-01 13:12:56') ;
INSERT INTO test VALUES ( 1,5,'2015-03-01 13:15:27') ;
INSERT INTO test VALUES ( 1,8,'2015-03-01 13:16:21') ;
INSERT INTO test VALUES ( 1,8,'2015-03-01 13:16:22') ;
INSERT INTO test VALUES ( 1,8,'2015-03-01 13:17:21') ;
INSERT INTO test VALUES ( 1,5,'2015-03-01 13:17:27') ;
INSERT INTO test VALUES ( 1,3,'2015-03-01 13:18:21') ;
INSERT INTO test VALUES ( 4,4,'2015-03-01 13:19:56') ;
SELECT * FROM test ;

En procédant par étapes pour essayer d'expliquer :

1- L'ordre SQL suivant permet de retrouver les couples (ID,TYPE ) consécutifs sous la forme ID, TYPE, DATE_DEBUT, DATE_FIN
---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT t1.id, t1.typid, t1.datid, t2.datid,t3.typid, t3.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL
ORDER BY t1.datid ;

id | typid |         datid          |         datid          | typid | datid
----+-------+------------------------+------------------------+-------+-------
  1 |     5 | 2015-03-01 13:12:27+01 | 2015-03-01 13:15:27+01 |       |
  4 |     4 | 2015-03-01 13:12:56+01 | 2015-03-01 13:19:56+01 |       |
  1 |     8 | 2015-03-01 13:16:21+01 | 2015-03-01 13:16:22+01 |       |
  1 |     8 | 2015-03-01 13:16:22+01 | 2015-03-01 13:17:21+01 |       |
(4 lignes)

2 - Pour obtenir la liste de toutes les occurences de couple (ID,TYPE) consécutifs, il faut prendre en compte le début et la fin  :
-----------------------------------------------------------------------------------------------------------------------------------

SELECT t1.id, t1.typid, t1.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL
UNION
SELECT t1.id, t1.typid, t2.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL
ORDER BY 3 ;

id | typid |         datid         
----+-------+------------------------
  1 |     5 | 2015-03-01 13:12:27+01
  4 |     4 | 2015-03-01 13:12:56+01
  1 |     5 | 2015-03-01 13:15:27+01
  1 |     8 | 2015-03-01 13:16:21+01
  1 |     8 | 2015-03-01 13:16:22+01
  1 |     8 | 2015-03-01 13:17:21+01
  4 |     4 | 2015-03-01 13:19:56+01
(7 lignes)

3 - Une fois obtenue la liste précedente, il faut compter le nombre de TYPE par ID :
------------------------------------------------------------------------------------

SELECT id, count(typid) Nb
FROM ( SELECT t1.id, t1.typid, t1.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL
UNION
SELECT t1.id, t1.typid, t2.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
                         and t1.id = t3.id
WHERE t3.id IS NULL ) AS EventNb
GROUP BY id ;

id | nb
----+----
  1 |  5
  4 |  2
(2 lignes)

Voilà, j'espère que cela sera utile.
Cordialement,

martina85

Membre

Re : Deux lignes consécutives avec la même valeur dans un champ

Bonjour jpcarayo

je vous remercie beaucoup pour votre zèle. Malheureusement, j'aurais besoin d'un résultat un peu différent : en prenant en consideration la table que vous avez créée :

id   |typid|         datid         
----+-----+------------------------
  1 |     3 | 2015-03-01 13:12:21+01
  2 |     7 | 2015-03-01 13:12:21+01
  1 |     5 | 2015-03-01 13:12:27+01 
  4 |     4 | 2015-03-01 13:12:56+01 
  1 |     5 | 2015-03-01 13:15:27+01 
  1 |     8 | 2015-03-01 13:16:21+01  <--
  1 |     8 | 2015-03-01 13:16:22+01  <--
  1 |     8 | 2015-03-01 13:17:21+01  <--
  1 |     5 | 2015-03-01 13:17:27+01
  1 |     3 | 2015-03-01 13:18:21+01
  4 |     4 | 2015-03-01 13:19:56+01 

le résultat que je souhaiterais est le suivant :

id   | typid  |  count
----+-------+--------
  1  |   8     |     3   
   
c'est à dire, la valeur "8" du champ "typid" se présente 3 fois consécutivement pour le id "1".
J'ai besoin de mettre en liste tous les "id" qui ont fait une action typid "8" un certain nombre de fois consécutivement.
Si l'action 8 n'est pas suivie par une autre action 8, alors elle ne sera pas tracée.

J'espère que vous pouvez me répondre encore :-)

Martina

rjuju

Administrateur

Re : Deux lignes consécutives avec la même valeur dans un champ

Est-ce que la requête suivante répond à votre problème ?

WITH s AS (SELECT id, typid, lead(typid) OVER (w) AS lead_typid, datid, lead(datid) OVER (w) AS lead_datid
FROM tbl
WINDOW w AS (PARTITION BY id ORDER BY datid))
SELECT id, typid, count(*) + 1 FROM s where typid = lead_typid GROUP BY id, typid;

Sur les données en exemple, cela me remonte :

 id │ typid │ ?column? 
════╪═══════╪══════════
  1 │     5 │        2
  1 │     8 │        3
  4 │     4 │        2

---

Julien.
https://rjuju.github.io/

martina85

Membre

Re : Deux lignes consécutives avec la même valeur dans un champ

Bonjour Julien et jpcarajo,

j'ai bien relu vos 2 réponses et je pense que ensemble elles me donnent la solution recherchée !

Mille merci à tous les deux !

jpcarayo

Membre

Re : Deux lignes consécutives avec la même valeur dans un champ

Très content si j'ai pu aider.

Si le résultat  doit être uniquement :

id   | typid  |  count
----+-------+--------
  1  |   8     |     3   

"Ma requête" doit  être modifier comme suit :
___________________________________________
SELECT id, typid,count(typid) Nb
FROM ( SELECT t1.id, t1.typid, t1.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
WHERE t3.id IS NULL
UNION
SELECT t1.id, t1.typid, t2.datid
  FROM ( test t1 INNER JOIN test t2 ON  t1.id = t2.id
                                         and  t1.typid = t2.typid
                                         and  t1.datid < t2.datid )
       LEFT JOIN test t3 ON  t3.datid > t1.datid and t3.datid < t2.datid
WHERE t3.id IS NULL ) AS EventNb
GROUP BY id, typid ;
_________________________________________

La solution de Julien est certainement meilleure du point de vue des performances. Elle est également beaucoup plus concise.

Bon développement.
Bien Cordialement

Dernière modification par jpcarayo (20/04/2016 01:14:05)

martina85

Membre

Re : Deux lignes consécutives avec la même valeur dans un champ

Parfait
merci beaucoup, je pense que ces solutions me permettent de faire plusieurs choses !

Cordialement :-)