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+

**mail.spam** · 22/05/2015, 12h09

Bonjour,

Merci pour la solution, ainsi que les explications.

**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 +

**hmira** · 22/05/2015, 14h46

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+

**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

Discussions similaires

Partager

Partager