[2008R2] Incrémentation sur condition

Incrémentation sur condition

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.

Citation:

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

Citation:

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.

Utilisez une fonction de fenêtrage pour faire :

Code:

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

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

A +

Bonjour,

Merci pour cette réponse.

Voici un jeu d'essai

Code:

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:

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

j'ai le message

Citation:

Syntaxe incorrecte vers le mot clé 'ORDER'.

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

Code:

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

Citation:

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

Quelle version de SQL Server ?

A +

Autant pour moi,

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

Je suis sous 2008R2 SP2.

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:

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:

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:

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+

Bonjour,

Merci pour la solution, ainsi que les explications.

Citation:

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 +

Citation:

Envoyé par SQLpro

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 +

Oui, tout à fait.
Je voudrais juste préciser que pour les fonctions d'agrégation (SUM, AVG, etc..) la clause ORDER BY permet d'obtenir des agrégats cumulés traduisant l'évolution de agrégat (Exemple l'évolution des ventes au cours des mois etc.).
Sans cette spécificité ou fonctionnalité (agrégat cumulé), très intéressante, très utile, et qui était très attendue par les développeurs, nouveauté rajoutée depuis la version SQL Server 2012, la clause ORDER BY, pour calculer un agrégat comme un SUM, n'aurait aucun sens puisque l'addition est une loi de composition interne commutative et associative.

A+

Citation:

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 +

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