Nombre de valeurs identiques consécutives [Résolu]

calou_33 · 02/12/2010, 14h12

Bonjour,

Sous FB 2.1 je cherche à récupérer le nombre de valeurs identiques consécutives (classement par date) dans une table
Par exemple :

date Valeur
01/01/10 09:10:00 1
01/01/10 09:20:00 2
01/01/10 09:30:00 2
01/01/10 09:40:00 2
01/01/10 09:50:00 3
01/01/10 10:00:00 3
01/01/10 10:10:00 2

Dans cet exemple il faudrait retourner 1*1,3*2, 2*3 et 1*2

Est ce faisable avec une requete SQL?

Merci

freud · 02/12/2010, 14h45

Bonjour,

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT DATE, VALEUR FROM TABLE ORDER BY DATE ASC

ORDER BY DATE DESC =décroissant

TryExceptEnd · 02/12/2010, 14h51

Citation:

Envoyé par calou_33

Bonjour,

Sous FB 2.1 je cherche à récupérer le nombre de valeurs identiques consécutives (classement par date) dans une table
Par exemple :

date Valeur
01/01/10 09:10:00 1
01/01/10 09:20:00 2
01/01/10 09:30:00 2
01/01/10 09:40:00 2
01/01/10 09:50:00 3
01/01/10 10:00:00 3
01/01/10 10:10:00 2

Dans cet exemple il faudrait retourner 1*1,3*2, 2*3 et 1*2

Est ce faisable avec une requete SQL?

Merci

La table utilisée n'a pas d'autres colonnes ?
parsque la c'est pas possible simplement.

calou_33 · 02/12/2010, 14h56

Bonjour,

Non il n'y a pas d'autre colonne. Je me demandais si avec des requetes recursives on ne pouvait pas faire quelque chose mais de ces requetes là je ne connais que le nom

SergioMaster · 02/12/2010, 15h42

sans tester , je proposerais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT COUNT(*),VALEUR FROM TABLE
GROUP BY VALEUR

Barbibulle · 02/12/2010, 15h55

C'est faisable facilement avec une PS.

TryExceptEnd · 02/12/2010, 17h22

Citation:

Envoyé par Barbibulle

C'est faisable facilement avec une PS.

et comment ?

Barbibulle · 02/12/2010, 18h10

Quelque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE procedure Ma_PS
returns (dateGroupe date, Nombre integer, valeurGroupe integer)
AS
declare variable dt date;           /* date Enregistrement courant */
declare variable valeur integer;    /* Valeur Enregistrement courant */
declare variable old_dt date;       /* date Enregistrement précédent */
declare variable old_valeur integer;/* Valeur Enregistrement précédent */
begin
  /* Initialisation des valeurs de l'enregistrement précédent */
  old_dt = NULL;
  old_valeur = NULL;
  Nombre = 0;
 
  FOR SELECT cast(m.dt AS date), m.valeur
  FROM matable m
  ORDER BY m.dt
  INTO :dt, :valeur
  do
  begin
    IF (old_dt IS NULL) then  /* c'est le 1er enregistrement qu'on traite*/
    begin
      old_dt = dt;            /* On mémorise les données */
      old_valeur = valeur;
      Nombre = 0;
    end
    IF (old_dt = dt AND old_valeur = valeur) then /* Si même date et même valeur */
    begin
      Nombre = nombre + 1;                        /* On incrémente le compteur */
    end
    else                        /* Sinon c'est qu'il y a un changement */
    begin                       /* restitution du comptage */
      dateGroupe = old_dt;
      valeurGroupe = old_valeur;
      suspend;
      old_dt = dt;              /* mémoriser les nouvelles données */
      old_valeur = valeur;
      Nombre = 1;
    end
  end
  dateGroupe = old_dt;          /* Ne pas oublier de restituer le dernier élément*/
  valeurGroupe = old_valeur;
  suspend;
end

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DATEGROUPE	NOMBRE	VALEURGROUPE
01/10/2010	1	1
01/10/2010	3	2
01/10/2010	2	3
01/10/2010	1	2

calou_33 · 02/12/2010, 18h12

Bonjour Sergio,

Cette requete ne me retourne pas le nombre de valeurs identiques qui se suivent mais le nombre total de valeurs identiques.

Merci

TryExceptEnd · 02/12/2010, 18h44

Et comme ça ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE procedure NEW_PROCEDURE
returns (
    RECORD_COUNT integer,
    VALEUR integer)
AS
declare variable VAR_DATE timestamp;
declare variable VAR_VALEUR integer;
begin
 RECORD_COUNT = 0;
 VALEUR = 0;
 FOR SELECT  date,  Valeur   -- les colonnes de votre table
     FROM VOTRE_TABLE        -- le nom de votre table
     ORDER BY 1
     INTO :VAR_DATE, :VAR_VALEUR
 do begin
  IF ((VALEUR = VAR_VALEUR) OR (VALEUR = 0)) then
  begin
   RECORD_COUNT = RECORD_COUNT + 1;
   VALEUR = VAR_VALEUR;
  end
  else IF (VALEUR <> VAR_VALEUR) then
  begin
   RECORD_COUNT = RECORD_COUNT + 1;
   suspend;
   VALEUR = VAR_VALEUR;
   RECORD_COUNT = 0;
  end
 end
 suspend;
end

freud · 02/12/2010, 22h02

Désolé pour mon post calou_33, il etait HS. Je pensais que vous cherchiez le classement par date. Je n'ai pas réalisé la difficulté de votre problème.

calou_33 · 03/12/2010, 03h32

Merci pour les PS je vais tester ca

Freud pas de pb

SergioMaster · 03/12/2010, 07h43

Citation:

Envoyé par calou_33

Bonjour Sergio,

Cette requete ne me retourne pas le nombre de valeurs identiques qui se suivent mais le nombre total de valeurs identiques.

Merci

désolé , je n'avais pas lu le date consécutive

makowski · 03/12/2010, 17h18

c'est surement améliorable, et surtout il nous manque les windows function pour faire mieux, mais à priori c'est faisable en SQL avec les CTE et sans récursivité

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE TA1 
(
  F_DT            TIMESTAMP         NOT NULL,
  F_V               INTEGER         NOT NULL,
 CONSTRAINT PK_TA1 PRIMARY KEY (F_DT, F_V)
);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 09:10:00.000', 1);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 09:20:00.000', 2);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 09:30:00.000', 2);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 09:40:00.000', 2);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 09:50:00.000', 3);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 10:00:00.000', 3);
INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 10:10:00.000', 2);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH CTE1 (F_DT,F_V,F_DTn1,F_V1) AS
(SELECT TB1.F_DT, TB1.F_V, 
 COALESCE((SELECT FIRST 1 TB2.F_DT FROM TA1 TB2 WHERE 
 TB2.F_DT>TB1.F_DT), CAST('01-01-2011' AS TIMESTAMP)),
 (SELECT FIRST 1 TB2.F_V FROM TA1 TB2 WHERE TB2.F_DT>TB1.F_DT) 
FROM TA1 TB1),
CTE2 (MINDT,MAXDT,FV) AS
(SELECT MIN(F_DT),MAX(F_DTn1),F_V 
 FROM CTE1 WHERE F_V=F_V1 OR F_V1 IS NULL
 OR F_V=(SELECT FIRST 1 F_V FROM TA1 ORDER BY F_DT) 
GROUP BY F_V ,F_V1)
SELECT COUNT(*),CTE2.FV,CTE2.MINDT
FROM CTE2 JOIN TA1 ON CTE2.FV=TA1.F_V 
 AND TA1.F_DT BETWEEN CTE2.MINDT AND CTE2.MAXDT
 GROUP BY CTE2.FV,CTE2.MINDT
 ORDER BY CTE2.MINDT,CTE2.FV

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
COUNT      FV         MINDT             
----------------------------------------
1          1          01/01/2010 09:10:00
3          2          01/01/2010 09:20:00
2          3          01/01/2010 09:50:00
1          2          01/01/2010 10:10:00

Barbibulle · 03/12/2010, 19h41

humm diabolique ces CTE.

Mais la PS sera plus performante car il n'y a qu'un seul balayage de la table.

Il me semble qu'en plus le code de la CTE proposée ne fonctionne pas si on ajoute par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 10:20:00.000', 9);

J'ai un peu mal au crane là, pour regarder de plus pres

TryExceptEnd · 03/12/2010, 22h06

Petite rectification de la PS que j'ai proposé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE procedure NEW_PROCEDURE
returns (
    RECORD_COUNT integer,
    VALEUR integer)
AS
declare variable VAR_DATE timestamp;
declare variable VAR_VALEUR integer;
begin
 RECORD_COUNT = 0;
 VALEUR = 0;
 FOR SELECT  Dates,  Valeur   -- les colonnes de votre table
     FROM  VOTRE_TABLE        -- le nom de votre table
     ORDER BY 1
     INTO :VAR_DATE, :VAR_VALEUR
 do begin
  IF (VALEUR = 0) then
  begin
   VALEUR = VAR_VALEUR;
  end
  else IF (VALEUR = VAR_VALEUR) then
  begin
   RECORD_COUNT = RECORD_COUNT + 1;
   VALEUR = VAR_VALEUR;
  end
  else IF (VALEUR <> VAR_VALEUR) then
  begin
   RECORD_COUNT = RECORD_COUNT + 1;
   suspend;
   VALEUR = VAR_VALEUR;
   RECORD_COUNT = 0;
  end
 end
 RECORD_COUNT = RECORD_COUNT + 1;
 suspend;
end

makowski · 04/12/2010, 00h06

Citation:

Envoyé par Barbibulle

Mais la PS sera plus performante car il n'y a qu'un seul balayage de la table.

oui

Citation:

Envoyé par Barbibulle

Il me semble qu'en plus le code de la CTE proposée ne fonctionne pas si on ajoute par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 10:20:00.000', 9);

C'est vrai

désolé

Barbibulle · 04/12/2010, 10h28

Je pense que c'est à cause du group by de cte2 qui induit un tri.

je proposerai plutôt un truc du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH itv (Valeur, Min_date, Max_date, V1) AS
(SELECT t.f_v,
  coalesce((SELECT first 1 t2.f_dt FROM ta1 T2 WHERE T2.f_dt<=t.f_dt AND T2.f_v<>t.f_v ORDER BY t2.f_dt DESC ),
    t.f_dt-0.00000001) AS min_date,
  t.f_dt AS max_date,
  (SELECT first 1 t2.f_v FROM ta1 T2 WHERE T2.f_dt>t.f_dt ORDER BY t2.f_dt ASC )
FROM ta1 t
ORDER BY t.f_dt),
intervalles (Valeur, Min_date, Max_date) AS
(SELECT i.valeur, i.Min_date, i.Max_date FROM itv i
WHERE (i.valeur<>i.V1) OR (i.v1 IS NULL))
SELECT (SELECT count(*) FROM TA1 t WHERE t.f_dt > i.Min_date AND t.f_dt <=i.max_date ) ,
  i.valeur
  FROM intervalles i

Inspiré de l'idée de makowski qui je pense cherchait à trouver les intervalles de date pour chaque valeur.

Barbibulle · 05/12/2010, 22h26

Dans ma PS, j'ai remis a zéro le comptage des valeurs identiques consécutives, lors d'un changement de date.

donc pour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
date     Valeur
01/01/10 09:10:00 1
01/01/10 09:20:00 2
02/01/10 09:30:00 2
02/01/10 09:40:00 2
03/01/10 09:50:00 3
03/01/10 10:00:00 3
03/01/10 10:10:00 2

Ma PS retourne
01/01/2010 1*1
01/01/2010 1*2
02/01/2010 2*2
03/01/2010 2*3
03/01/2010 1*2

Ce que je n ai pas fait dans la requête cte.

Pour cela il faut la changer comme ci dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH itv (Valeur, Min_date, Max_date, V1) AS
(SELECT t.f_v,
  coalesce((SELECT first 1 t2.f_dt
              FROM ta1 T2
              WHERE T2.f_dt<=t.f_dt
                AND (   (T2.f_v<>t.f_v)
                     OR (cast(t.f_dt AS date)<>cast(T2.f_dt AS date)))  ORDER BY t2.f_dt DESC ),
    t.f_dt-0.00000001) AS min_date,
  t.f_dt AS max_date,
  (SELECT first 1 t2.f_v FROM ta1 T2 WHERE T2.f_dt>t.f_dt ORDER BY t2.f_dt ASC )
FROM ta1 t
ORDER BY t.f_dt),
intervalles (Valeur, Min_date, Max_date) AS
(SELECT i.valeur, i.Min_date, i.Max_date FROM itv i
WHERE (i.valeur<>i.V1) OR (i.v1 IS NULL) OR ((cast(i.min_date AS date)<>cast(i.max_date AS date)) AND (i.valeur=i.V1)) )
SELECT cast(i.max_date AS date), (SELECT count(*) FROM TA1 t WHERE t.f_dt > i.Min_date AND t.f_dt <=i.max_date ) ,
  i.valeur
  FROM intervalles i

La PS modifiée pour compter les valeurs consécutives sans tenir compte des changements de date.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE procedure Ma_PS
returns (dateGroupe date, Nombre integer, valeurGroupe integer)
AS
declare variable dt date;           /* date Enregistrement courant */
declare variable valeur integer;    /* Valeur Enregistrement courant */
declare variable old_dt date;       /* date Enregistrement précédent */
declare variable old_valeur integer;/* Valeur Enregistrement précédent */
begin
  /* Initialisation des valeurs de l'enregistrement précédent */
  old_dt = NULL;
  old_valeur = NULL;
  Nombre = 0;
 
  FOR SELECT cast(m.dt AS date), m.valeur
  FROM matable m
  ORDER BY m.dt
  INTO :dt, :valeur
  do
  begin
    IF (old_dt IS NULL) then  /* c'est le 1er enregistrement qu'on traite*/
    begin
      old_dt = dt;            /* On mémorise les données */
      old_valeur = valeur;
      Nombre = 0;
    end
    IF (old_dt = dt AND old_valeur = valeur) then /* Si même date et même valeur */
    begin
      Nombre = nombre + 1;                        /* On incrémente le compteur */
    end
    else                        /* Sinon c'est qu'il y a un changement */
    begin                       /* restitution du comptage */
      dateGroupe = old_dt;
      valeurGroupe = old_valeur;
      suspend;
      old_dt = dt;              /* mémoriser les nouvelles données */
      old_valeur = valeur;
      Nombre = 1;
    end
  end
  dateGroupe = old_dt;          /* Ne pas oublier de restituer le dernier élément*/
  valeurGroupe = old_valeur;
  suspend;
end

makowski · 06/12/2010, 14h56

oui, le passage par "intervalles" est cool, merci d'avoir corrigé

juste une critique sur celle qui renvoie les dates, d'abord tu fais un cast par date , ce qui fait que l'info est moyennement passionnante, donc ok on peux changer ça pour un timestamp, mais du coup toi tu renvoies la fin de l'intervalle, alors que je cherchai à indiquer le début de l'intervalle

02/12/2010, 14h12	#1
calou_33 Membre régulier Inscription : mai 2008 Messages : 260 Détails du profil Informations forums : Inscription : mai 2008 Messages : 260 Points : 89 Points : 89	Nombre de valeurs identiques consécutives Bonjour, Sous FB 2.1 je cherche à récupérer le nombre de valeurs identiques consécutives (classement par date) dans une table Par exemple : date Valeur 01/01/10 09:10:00 1 01/01/10 09:20:00 2 01/01/10 09:30:00 2 01/01/10 09:40:00 2 01/01/10 09:50:00 3 01/01/10 10:00:00 3 01/01/10 10:10:00 2 Dans cet exemple il faudrait retourner 11,32, 23 et 12 Est ce faisable avec une requete SQL? Merci
	00

02/12/2010, 22h02	#11
freud Membre éprouvé Analyste-développeur Inscription : mai 2002 Messages : 989 Détails du profil Informations personnelles : Sexe : Localisation : Algérie Informations professionnelles : Activité : Analyste-développeur Informations forums : Inscription : mai 2002 Messages : 989 Points : 426 Points : 426	Désolé pour mon post calou_33, il etait HS. Je pensais que vous cherchiez le classement par date. Je n'ai pas réalisé la difficulté de votre problème. __________________ Seul le Savoir est le Pouvoir
	00

03/12/2010, 19h41	#15
Barbibulle Membre Expert Frédéric Inscription : octobre 2002 Messages : 1 722 Détails du profil Informations personnelles : Nom : Frédéric Âge : 42 Informations forums : Inscription : octobre 2002 Messages : 1 722 Points : 2 025 Points : 2 025	humm diabolique ces CTE. Mais la PS sera plus performante car il n'y a qu'un seul balayage de la table. Il me semble qu'en plus le code de la CTE proposée ne fonctionne pas si on ajoute par exemple : Code : Sélectionner tout - Visualiser dans une fenêtre à part INSERT INTO TA1 (F_DT, F_V) VALUES ('01/01/2010 10:20:00.000', 9); J'ai un peu mal au crane là, pour regarder de plus pres
	00

06/12/2010, 14h56	#20
makowski Expert Confirmé Philippe Makowski Consultant spécialité Firebird Inscription : mai 2002 Messages : 2 213 Détails du profil Informations personnelles : Nom : Philippe Makowski Âge : 49 Localisation : France Informations professionnelles : Activité : Consultant spécialité Firebird Secteur : Conseil Informations forums : Inscription : mai 2002 Messages : 2 213 Points : 3 315 Points : 3 315	oui, le passage par "intervalles" est cool, merci d'avoir corrigé juste une critique sur celle qui renvoie les dates, d'abord tu fais un cast par date , ce qui fait que l'info est moyennement passionnante, donc ok on peux changer ça pour un timestamp, mais du coup toi tu renvoies la fin de l'intervalle, alors que je cherchai à indiquer le début de l'intervalle __________________ Philippe Makowski IBPhoenix - Firebird Membre de l'April
	00

02/12/2010, 14h56	#4
calou_33 Membre régulier Inscription : mai 2008 Messages : 260 Détails du profil Informations forums : Inscription : mai 2008 Messages : 260 Points : 89 Points : 89	Bonjour, Non il n'y a pas d'autre colonne. Je me demandais si avec des requetes recursives on ne pouvait pas faire quelque chose mais de ces requetes là je ne connais que le nom
	00

02/12/2010, 15h55	#6
Barbibulle Membre Expert Frédéric Inscription : octobre 2002 Messages : 1 722 Détails du profil Informations personnelles : Nom : Frédéric Âge : 42 Informations forums : Inscription : octobre 2002 Messages : 1 722 Points : 2 025 Points : 2 025	C'est faisable facilement avec une PS.
	00

02/12/2010, 18h12	#9
calou_33 Membre régulier Inscription : mai 2008 Messages : 260 Détails du profil Informations forums : Inscription : mai 2008 Messages : 260 Points : 89 Points : 89	Bonjour Sergio, Cette requete ne me retourne pas le nombre de valeurs identiques qui se suivent mais le nombre total de valeurs identiques. Merci
	00

03/12/2010, 03h32	#12
calou_33 Membre régulier Inscription : mai 2008 Messages : 260 Détails du profil Informations forums : Inscription : mai 2008 Messages : 260 Points : 89 Points : 89	Merci pour les PS je vais tester ca Freud pas de pb
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email