Incrémentation sur condition

**mail.spam** · 21/05/2015, 15h23

Bonjour,

Je cherche et je n'arrive pas à trouver comment faire un +1 suivant un condition.

J'ai une vue qui me retourne différentes colonnes
Dans une de ses colonnes j'ai les valeurs suivantes.

Identifiant	Ref	Valeur
1	a	1
2	a	11
3	a	11
4	a	11
5	a	11
6	a	21
7	a	1
8	a	11
9	a	11
10	a	11
11	a	11
12	a	11
13	a	11
14	a	11
15	a	11
16	a	21
17	a	1
18	a	11
19	a	11
20	a	21
21	B	1
22	B	11
23	B	11
24	B	11
25	B	11
26	B	21
27	B	1
28	B	11
29	B	11
30	B	11
31	B	11
32	B	21

Voici mon problème.

Je veux ajouter un compteur dans ma vue qui me fait plus 1 à chaque fois que ma Valeur vaut 1 grouper par Ref

Ce qui ferait un truc du genre

Identifiant	Ref	Valeur	Compteur
1	a	1	1
2	a	11	1
3	a	11	1
4	a	11	1
5	a	11	1
6	a	21	1
7	a	1	2
8	a	11	2
9	a	11	2
10	a	11	2
11	a	11	2
12	a	11	2
13	a	11	2
14	a	11	2
15	a	11	2
16	a	21	2
17	a	1	3
18	a	11	3
19	a	11	3
20	a	21	3
21	B	1	1
22	B	11	1
23	B	11	1
24	B	11	1
25	B	11	1
26	B	21	1
27	B	1	2
28	B	11	2
29	B	11	2
30	B	11	2
31	B	11	2
32	B	21	2

Merci d'avance pour votre aide.

**SQLpro** · 21/05/2015, 16h09

Utilisez une fonction de fenêtrage pour faire :

Code :

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

COUNT(*) OVER(PARTITION BY Ref, valeur ORDER BY Identifiant)

A lire : http://sqlpro.developpez.com/article...clause-window/

A +

**mail.spam** · 21/05/2015, 16h27

Bonjour,

Merci pour cette réponse.

Voici un jeu d'essai

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
DECLARE @Table as Table    (Identifiant int
    , Ref nvarchar(10)
    ,Valeur int)
 
INSERT INTO @Table VALUES(1,'a',1)
INSERT INTO @Table VALUES(2,'a',11)
INSERT INTO @Table VALUES(3,'a',11)
INSERT INTO @Table VALUES(4,'a',11)
INSERT INTO @Table VALUES(5,'a',11)
INSERT INTO @Table VALUES(6,'a',21)
INSERT INTO @Table VALUES(7,'a',1)
INSERT INTO @Table VALUES(8,'a',11)
INSERT INTO @Table VALUES(9,'a',11)
INSERT INTO @Table VALUES(10,'a',11)
INSERT INTO @Table VALUES(11,'a',11)
INSERT INTO @Table VALUES(12,'a',11)
INSERT INTO @Table VALUES(13,'a',11)
INSERT INTO @Table VALUES(14,'a',11)
INSERT INTO @Table VALUES(15,'a',11)
INSERT INTO @Table VALUES(16,'a',21)
INSERT INTO @Table VALUES(17,'a',1)
INSERT INTO @Table VALUES(18,'a',11)
INSERT INTO @Table VALUES(19,'a',11)
INSERT INTO @Table VALUES(20,'a',21)
INSERT INTO @Table VALUES(21,'B',1)
INSERT INTO @Table VALUES(22,'B',11)
INSERT INTO @Table VALUES(23,'B',11)
INSERT INTO @Table VALUES(24,'B',11)
INSERT INTO @Table VALUES(25,'B',11)
INSERT INTO @Table VALUES(26,'B',21)
INSERT INTO @Table VALUES(27,'B',1)
INSERT INTO @Table VALUES(28,'B',11)
INSERT INTO @Table VALUES(29,'B',11)
INSERT INTO @Table VALUES(30,'B',11)
INSERT INTO @Table VALUES(31,'B',11)
INSERT INTO @Table VALUES(32,'B',21)

Lorsque je fait cette requête

Code :

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

1
2
3
select * 
,COUNT(*) OVER(PARTITION BY Ref, valeur ORDER BY Identifiant)
from @table

j'ai le message

Syntaxe incorrecte vers le mot clé 'ORDER'.

Si j'enlève la partie ORBER BY
voici mon résultat

Code :

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

1
2
3
4
select * 
,COUNT(*) OVER(PARTITION BY Ref, valeur)
from @table
ORDER BY Identifiant

Identifiant	Ref	Valeur	(Aucun nom de colonne)
1	a	1	3
2	a	11	14
3	a	11	14
4	a	11	14
5	a	11	14
6	a	21	3
7	a	1	3
8	a	11	14
9	a	11	14
10	a	11	14
11	a	11	14
12	a	11	14
13	a	11	14
14	a	11	14
15	a	11	14
16	a	21	3
17	a	1	3
18	a	11	14
19	a	11	14
20	a	21	3
21	B	1	2
22	B	11	8
23	B	11	8
24	B	11	8
25	B	11	8
26	B	21	2
27	B	1	2
28	B	11	8
29	B	11	8
30	B	11	8
31	B	11	8
32	B	21	2

**SQLpro** · 21/05/2015, 17h13

Quelle version de SQL Server ?

A +

**mail.spam** · 21/05/2015, 17h26

Autant pour moi,

J'ai ajouter le préfixe à la discussion.

Je suis sous 2008R2 SP2.

**hmira** · 21/05/2015, 23h24

Le traitement que vous voulez faire est un peu particulier ! Au premier abord, il s'apparente à un traitement procédurale avec la ré-initialisation des compteurs etc. Il est toute fois possible d'exprimer directement la requête en langage ensembliste comme cela est exposé ci-après :

Code SQL :

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
; WITH
CTE_Table
    AS
    ( SELECT Identifiant, Ref, Valeur,
    ROW_NUMBER() OVER (ORDER BY Identifiant) AS RowNumber
    FROM @Table
    ),
CTE_CPT
    AS
    ( SELECT Identifiant, Ref, Valeur,
        ROW_NUMBER() OVER (PARTITION BY Ref ORDER BY Identifiant) AS Compteur
        FROM @Table
        WHERE Valeur = 1
    )
SELECT T.Identifiant, T.Ref, T.Valeur, C.Compteur
FROM  CTE_Table T
INNER jOIN CTE_CPT  C
  ON C.Ref = T.Ref
  AND C.Identifiant = ( SELECT  MAX(T2.Identifiant)
                        FROM CTE_Table as T2
                        WHERE T2.Ref = T.Ref
                        AND   T2.Valeur = 1
                        AND   T2.Identifiant <=  T.Identifiant
                        )
ORDER BY T.Identifiant

Résultat de la requête :

Code SQL :

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
Identifiant	Ref	Valeur	Compteur
1	a	1	1
2	a	11	1
3	a	11	1
4	a	11	1
5	a	11	1
6	a	21	1
7	a	1	2
8	a	11	2
9	a	11	2
10	a	11	2
11	a	11	2
12	a	11	2
13	a	11	2
14	a	11	2
15	a	11	2
16	a	21	2
17	a	1	3
18	a	11	3
19	a	11	3
20	a	21	3
21	B	1	1
22	B	11	1
23	B	11	1
24	B	11	1
25	B	11	1
26	B	21	1
27	B	1	2
28	B	11	2
29	B	11	2
30	B	11	2
31	B	11	2
32	B	21	2

Le résultat obtenu correspond exactement au résultat attendu.

Quelques explications
1 - La CTE_CPT représente la CTE des compteurs. Cette "vue" génère le résultat (les 5 lignes ci-dessous ) suivant :

Code :

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

1
2
3
4
5
6
Identifiant Ref Valeur  Compteur
1   a   1   1
7   a   1   2
17  a   1   3
21  B   1   1
27  B   1   2

2 - Une jointure entre la table principale @Table (c.à.d CTE_Table) et la CTE des compteurs (CTE_CPT) permet de déterminer pour chacune des lignes la valeur du compteur. Le lien entre la table principale @Table et la CTE des compteurs est un peu subtile avec l'expression MAX(T2.Identifiant) etc.. dans la clause de jointure, mais toutefois, cela peut se comprendre aisément.

3 - Et le tour est joué !

A+

**SQLpro** · 22/05/2015, 13h09

Envoyé par mail.spam

Autant pour moi,

J'ai ajouter le préfixe à la discussion.

Je suis sous 2008R2 SP2.

Normal, la partie ORDER BY dans la clause OVER pour le calcul des agrégats fenetrés n'est disponible que depuis la version 2012.

A +

**SQLpro** · 26/05/2015, 15h15

Envoyé par mail.spam

Autant pour moi,

J'ai ajouter le préfixe à la discussion.

Je suis sous 2008R2 SP2.

C'est donc normal. La partie ORDER BY pour les agrégats fenêtrés n'est pas implémenté dans cette version. Il vous faut la version 2012 ou +

A +

Incrémentation sur condition [2008R2]

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager