Discussion :

[guilld]

Bonjour,

Etant en SQL 2008R2, je cherche une alternative à "ORDER BY" de la fonction de fenêtrage ci-dessous.
Ceci dans l'objectif de calculer les salaires en cumulé.

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
SELECT salaire, sum(salaire) OVER (ORDER BY salaire) FROM salaireemp;
 
 salaire|  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

Merci pour votre aide.

[aieeeuuuuu]

Bonjour,

Malheureusement, il n'y a pas d'alternative réellement efficace.
Vous pouvez faire une sous requête scalaire corrélée :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT 
	A.salaire,
	(
		SELECT SUM(B.Salaire)
		FROM salaireemp B
		WHERE B.salaire <= A.salaire
	)
FROM salaireemp A

Ou bien une autojointure avec regroupement si vous disposez d'une colonne discriminante pour les doublons sur les salaires (comme un id d'employé par exemple), mais je pense que dans ce cas, les performances ne seront pas réellement meilleures... a tester.
Avez-vous une forte volumétrie ?

dans tous les cas, pensez à indexer sur la colonne salaire.

[guilld]

Je vous donne la requête dans son intégralité. L'exemple précédent était factice, c'était juste pour expliquer l'énoncé.

J'ai donc besoin de calculer la colonne DEL en cumulée et je ne trouve pas de solution. Point de vue volume, c'est environ 15000 lignes au total.

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
select Vga.U
        , Vga.Dateref
        , Vga.Micro
        , Vga.JL
        , Vga.IGA
        , Vga.SGA
        , val.DEL
        , val.REB
	,SUM(val.del) OVER(PARTITION BY Vga.IGA) as Del_TT
 
from vgamme Vga
 
inner join Valloue Val
on val.dateref = Vga.dateref 
and val.IGA = Vga.IGA
And Val.micro = Vga.micro
and val.jl = Vga.Jl
and val.U = Vga.U
and val.grade = Vga.grade
 
where Vga.grade = '1' and val.dateref = (Select Max(Dateref) from Vgamme)
 
order by Micro, IGA, SGA, JL

Résultat:

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
U	Dateref	                        Micro 	JL	IGA	SGA	DEL	REB	Del_TT
5	2015-06-18 00:00:00.000	Q1040	CI	G00	020	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	ML	G00	050	3.5	1.4	17
5	2015-06-18 00:00:00.000	Q1040	FU	G00	080	2	0.6	17
5	2015-06-18 00:00:00.000	Q1040	DP	G00	100	2	0.1	17
5	2015-06-18 00:00:00.000	Q1040	F2	G00	130	2	2.7	17
5	2015-06-18 00:00:00.000	Q1040	TB	G00	170	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	RD	G00	190	1	0.4	17
5	2015-06-18 00:00:00.000	Q1040	TF	G00	210	2	0	17
5	2015-06-18 00:00:00.000	Q1040	SP	G00	230	1	0.3	17
5	2015-06-18 00:00:00.000	Q1040	RF	G00	260	1	2.4	17
5	2015-06-18 00:00:00.000	Q1040	RX	G00	290	1	3.5	17
5	2015-06-18 00:00:00.000	Q1040	RH	G00	310	0	0	17
5	2015-06-18 00:00:00.000	Q1040	EM	G00	330	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	EX	G00	340	0	0	17
5	2015-06-18 00:00:00.000	Q1040	CI	G01	020	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	ML	G01	050	3.5	3	17
5	2015-06-18 00:00:00.000	Q1040	FU	G01	080	2	3	17
5	2015-06-18 00:00:00.000	Q1040	DP	G01	100	2	3	17
5	2015-06-18 00:00:00.000	Q1040	F2	G01	130	2	3	17
5	2015-06-18 00:00:00.000	Q1040	TB	G01	170	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	RD	G01	190	1	3	17
5	2015-06-18 00:00:00.000	Q1040	TF	G01	210	2	0	17
5	2015-06-18 00:00:00.000	Q1040	SP	G01	230	1	3	17
5	2015-06-18 00:00:00.000	Q1040	RF	G01	260	1	3	17
5	2015-06-18 00:00:00.000	Q1040	RX	G01	290	1	4	17
5	2015-06-18 00:00:00.000	Q1040	RH	G01	310	0	0	17
5	2015-06-18 00:00:00.000	Q1040	EM	G01	330	0.5	4	17
5	2015-06-18 00:00:00.000	Q1040	EX	G01	340	0	0	17

[guilld]

Je vois que personne n'a d'idée.. humm.. (?)
Je cherche une solution depuis pas mal de temps et je tourne en rond, alors si qqun aperçoit une solution... Merci.

[aieeeuuuuu]

Je cherche une solution depuis pas mal de temps

Je vous en ai proposé deux ! les avez vous testées ?
sur une si faible volumétrie, avec les bon index, ça peut tout à fait convenir.

Et en cas de problèmes de performances, la deuxième solution proposée pourrait faire l'objet d'une vue indexée, avec de fait des temps de réponses bien meilleures. (si le besoin est récurrents, et que les mises à jour des tables sous-jacentes ne sont pas trop fréquentes)

[guilld]

Effectivement, j'ai testé les 2 solutions et je vous en remercie. Mais le résultat est incohérent.

Voici le code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select  
		A.IGA
		,A.DEL
		,(
			Select sum(B.DEL)
			from #Dw_DelaiTravCumul B
			where b.del <= A.del
		) as x
 
from #Dw_DelaiTravCumul A
order by A.IGA, a.SGA

et voici le résultat :

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
IGA	DEL	x
G00	0.5	17.5
G00	3.5	215
G00	2	191.5
G00	2	191.5
G00	2	191.5
G00	0.5	17.5
G00	1	118.5
G00	2	191.5
G00	1	118.5
G00	1	118.5
G00	1	118.5
G00	0	0
G00	0.5	17.5
G00	0	0
G01	0.5	17.5
G01	3.5	215
G01	2	191.5
G01	2	191.5
G01	2	191.5
G01	0.5	17.5
G01	1	118.5
G01	2	191.5
G01	1	118.5
G01	1	118.5
G01	1	118.5
G01	0	0
G01	0.5	17.5
G01	0	0

La colonne "X" étant celle qui devrait cumuler les sommes de "DEL" en rapport à "IGA", mais là je ne sais pas ce qui se produit!.
Puis, quand "IGA" change de valeur, on doit repartir sur une nouvelle somme...
Je tourne les noms de colonne dans tous les sens mais sans résultat.

[aieeeuuuuu]

alors dans la sous requête, il faut restreindre sur la colonne IGA

[guilld]

Dans la sous requête, en ajoutant à la clause WHERE ceci

Code :

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

and b.IGA = a.IGA

J'obtiens cela :

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
IGA	DEL	x
G00	0.5	1.5
G00	3.5	17
G00	2	13.5
G00	2	13.5
G00	2	13.5
G00	0.5	1.5
G00	1	5.5
G00	2	13.5
G00	1	5.5
G00	1	5.5
G00	1	5.5
G00	0	0
G00	0.5	1.5
G00	0	0
G01	0.5	1.5
G01	3.5	17
G01	2	13.5
G01	2	13.5
G01	2	13.5
G01	0.5	1.5
G01	1	5.5
G01	2	13.5
G01	1	5.5
G01	1	5.5
G01	1	5.5
G01	0	0
G01	0.5	1.5
G01	0	0

Une somme toujours étrange...

[al1_24]

Ta somme cumulée doit elle être ordonnée sur DEL ou sur SGA ?

[guilld]

"DEL" est la somme que je veux cumuler.
"IGA" est la fenetre pour le calcul de la somme.
"SGA" est une sous rubrique.

Donc je dirai que cela doit être ordonné sur "IGA"

[al1_24]

Difficile de trier de manière identique lorsqu'il y a autant de doublons...

[guilld]

Je ne considére pas ca comme des doublons.

On peut considérer que :
"IGA" est comme une famille de produit.
"SGA" est la sous famille.

Extrait de la table d'origine :

Code :

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

1
2
3
4
5
6
7
8
U	Dateref	                Micro 	JL	IGA	SGA	DEL	REB	Del_TT
5	2015-06-18 00:00:00.000	Q1040	CI	G00	020	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	ML	G00	050	3.5	1.4	17
5	2015-06-18 00:00:00.000	Q1040	FU	G00	080	2	0.6	17
5	2015-06-18 00:00:00.000	Q1040	DP	G00	100	2	0.1	17
5	2015-06-18 00:00:00.000	Q1040	F2	G00	130	2	2.7	17
5	2015-06-18 00:00:00.000	Q1040	TB	G00	170	0.5	0	17
5	2015-06-18 00:00:00.000	Q1040	RD	G00	190	1	0.4	17

Donc sur cette base, mon but est de savoir combien me coûte la famille IGA en faisant une somme de "DEL" pour chaque groupe de famille (G00, G01, G02, etc...).

Merci pour votre aide.

[aieeeuuuuu]

Une somme toujours étrange...

Bah non, elle est correcte !
Qu'est-ce qui ne vas pas ?

[al1_24]

Et pourquoi la somme cumulée...

[guilld]

Ce qui ne va pas :

ligne 2: DEL = 0.5, x = 1.5 ! alors que je devrait avoir 0.5
ligne 3: DEL = 3.5, x = 17 ! alors que je devrait avoir 4
ligne 4: DEL = 2 , x = 13.5 ! alors que je devrait avoir 6
Etc..

Le résultat "X" est faux.

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
IGA	DEL	x
G00	0.5	1.5
G00	3.5	17
G00	2	13.5
G00	2	13.5
G00	2	13.5
G00	0.5	1.5
G00	1	5.5
G00	2	13.5
G00	1	5.5
G00	1	5.5
G00	1	5.5
G00	0	0
G00	0.5	1.5
G00	0	0
G01	0.5	1.5
G01	3.5	17
G01	2	13.5
G01	2	13.5
G01	2	13.5
G01	0.5	1.5
G01	1	5.5
G01	2	13.5
G01	1	5.5
G01	1	5.5
G01	1	5.5
G01	0	0
G01	0.5	1.5
G01	0	0

[aieeeuuuuu]

ligne 2: DEL = 0.5, x = 1.5 ! alors que je devrait avoir 0.5

Bah non, vous avez pour G00 trois fois DEL = 0.5 (lignes 2, 7 et 14), ce qui fait bien 1.5...

[al1_24]

0,5 apparait trois fois dans la liste pour G00. Soit un total de 1,5.
Où est l'erreur ?

:edit: grilled

[guilld]

Ben nan, c'est une somme ligne par ligne que j'espère obtenir...
Et puis si je prends votre logique, en G00 j'ai 1 fois 3.5 alors que x = 17 !

Bon, je pars déjeuner et reviens avec la solution car j'ai trouvé ... suspense!

[al1_24]

Le résultat pour 3.5 reste cohérent avec les données : (exemple pour G00)

Valeur	Occ.	Somme	Cumul
0,5	3	1,5	1,5
1	4	4	5,5
2	4	8	13,5
3,5	1	3,5	17

Donc si tu cherches un autre résultat, c'est que tu as mal énoncé ton problème !!

[aieeeuuuuu]

Ben nan, c'est une somme ligne par ligne que j'espère obtenir...

Ligne par ligne, ça suppose un ordre des lignes, et c'est précisément celui-là qu'on cherche à connaitre depuis le début... quel est-il ?

Et puis si je prends votre logique, en G00 j'ai 1 fois 3.5 alors que x = 17 !

Bah non, là aussi c'est correct : 17 = 3*0.5 + 4*1 + 4*2 + 1*3.5

Bon, je pars déjeuner et reviens avec la solution car j'ai trouvé ... suspense!

alors... ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select  
		A.IGA
		,A.DEL
		,(
			Select sum(B.DEL)
			from #Dw_DelaiTravCumul B
			where b.del <= A.del
			and b.IGA = a.IGA
			and b.SGA <= a.SGA
		) as x
 
from #Dw_DelaiTravCumul A
order by A.IGA, a.SGA