Sum Sur valeur Inferieur.(Expert)

[Troyan]

Bonjour à tous,

je rencontre une difficulté sur une somme.

Voila un exemple je possède une table avec un id, une valeur, un compteur

il faudrait que je puisse faire la somme
groupé sur cet ID, mais des valeur inférieur ou égale au compteur.


comme suit.
id val cpt
1 10 2
1 15 3
1 20 1


Cherche à faire ceci.
id val cpt result
1 20 1 20
1 10 2 30
1 15 3 45


En fait pouvoir faire une somme sur l'id mais des valeurs inférieur ou égal au ptit compteur (cpt).

je n'ai réussi qu'en faisant une table liée mais du coup les temps s'envole et j'aime pas trop cette solution.

je suis sql 2008 y'a t'il une fonction du style rownumber mais qui me les additionne ?



Merci par avance.
Ch.

[Yanika_bzh]

Peut etre qu'avec une auto jointure, vous pourrez recuperer ce qu'il vous faut, du genre

Code :

1
2
3
4
5
6
7
8
9
SELECT 
	B.id, B.Val, B.cpt, Sum(A.val)
FROM
	MaTable A
INNER JOIN
	MaTable B
ON
	A.cpt<=B.cpt
GROUP BY B.id, B.val, B.cpt

A tester et adapter

Bon courage

[StringBuilder]

je pense qu'il faut aussi rajouter un a.id = b.id dans le where.

[Troyan]

bonjour,

c'est la solution que j'utilise aujourd'hui mais elle est très loin d’être optimale coté performance.
Sachant que j'en ai ~290000 ça me prends plus d'une heure.
je cherche donc a savoir si la méthode est bonne ?
et/ou si il n'existe pas de fonction qui pourrait faire le travail bien plus rapidement.

[Yanika_bzh]

plus d'une heure ? avec des indexes sur le compteur ?

[Troyan]

Envoyé par Yanika_bzh

plus d'une heure ? avec des indexes sur le compteur ?

oui, en meme temps mon traitement est un peu plus complexe.
il y a 2 identifiant et le processus fait 2 sommes qu'il divise.

mais pour la compréhension du problème c'est exactement ça.

bon j'avoue aussi que 290000 avec cette méthode ça doit faire beaucoup de requête.

donc je cherche une autre piste.

[Yanika_bzh]

il serait interessant cependant d'analyser le plan d'execution.

[iberserk]

il serait interessant cependant d'analyser le plan d'execution.

+1

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
IF EXISTS(SELECT * FROM sys.TABLES WHERE NAME='TEST')
	DROP TABLE dbo.TEST
GO
CREATE TABLE dbo.TEST
(id integer,
val integer,
cpt integer,
PRIMARY KEY CLUSTERED (id,val))
 
INSERT INTO dbo.TEST(id,val,cpt)
SELECT id,val,cpt
 FROM(
SELECT DISTINCT T.object_id id,TT.ID AS val,TT.ID2 AS cpt
FROM sys.all_objects T
	CROSS JOIN (SELECT ROW_NUMBER()OVER(ORDER BY NEWID()) AS ID,ROW_NUMBER()OVER(ORDER BY NEWID()) AS ID2
	            FROM sys.TABLES) AS TT)A
 
 
SELECT 
	B.id, B.Val, B.cpt, Sum(A.val)
FROM
	TEST A
INNER JOIN
	TEST B
ON
	A.cpt<B.cpt
WHERE A.id=B.id
GROUP BY B.id, B.val, B.cpt

Ce code me génère une table de 420000 enregistrements (ce chiffre variera chez vous) mais la requete sans aucun index (autre que l'index CLUSTERED de la clé primaire certes...) ne dure que 27 secondes.


Pouvez vous poster, comme le demande si justement Yanika_bzh le plan d’exécution ainsi que les DDL de votre table?

[Troyan]

Ok je vais essayer de vous donner ça !

DDL de la table en question

Code :

1
2
3
CREATE TABLE #ZZZ (COL VARCHAR(50),VAL FLOAT, POND FLOAT, CPT_VAR INT, SUM_POND FLOAT, ID_GRP UNIQUEIDENTIFIER, DCOMIRIS VARCHAR(50), ZERO smallint , Q4 smallint)
 
CREATE INDEX IX_ind ON #DDD (id_grp, col, cpt_var)

Alors phénomène intéressante la boucle de 1h30 c'est du à des valeurs trop large. toutefois c'est juste pas normal quand même.
donc voici la requête.

Code :

1
2
3
4
5
6
7
8
9
10
       SELECT *,
				(
				SELECT sum(pond) / min(sum_pond) * 100 
				FROM #zzz 
				WHERE  
					id_grp = z.id_grp 
				AND col = z.col
				AND cpt_var <= z.cpt_var) AS quart_pont
			INTO #yyy
			FROM #zzz as z