Discussion :

[xogene]

Bonjour à tous,

Je souhaiterais avoir vos avis sur la manière de résoudre le problème suivant :

Dans le cadre d'une évaluation statistique, j'ai une table reprenant des essais réalisés sur plusieurs produits.

Dès que j'ai 3 essais sur un produit, je calcule la moyenne des 3, et cette valeur sert de référence à un test. Cette moyenne est mobile, chaque fois qu'un nouvel essai est disponible, une nouvelle moyenne des 3 derniers essais est calculée.

La valeur obtenue est alors confrontée à la valeur de référence. Si le test est faux, la valeur de référence reste inchangée. Si le test est vrai, une nouvelle valeur de référence est calculée sur base de la moyenne des 3 derniers résultats. On a donc :

Produit	NSmpl	valeur	Moy	Cible	test
A	1	28	/	/	/
A	2	36	/	/	/
A	3	31	31,67	31,67	/
A	4	12	26,33	31,67	faux
A	5	15	19,33	19,33	vrai - rééval
A	6	25	17,33	19,33	faux
B	1	...	/	/	/
B	2	...	/	/	/

Là où je bloque, c'est dans la manière de gérer cette valeur de référence. Jusqu'ici, j'utilise une fonction avec variable pour stocker la référence. Seulement, cette variable est réinitialisée à chaque ligne, et du coup je perds les changement éventuels...

Voici ma fonction :

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
FUNCTION [dbo].[Fn_Sigma] 
(
	@NSmpl smallint,
	@Produit smallint,
	@Moy decimal(3,1)
)
RETURNS decimal(3,1)
AS
BEGIN
	DECLARE @Result decimal(3,1)
	DECLARE @Cible decimal(3,1)
 
 
	IF @NSmpl < 3
	SET @Result = null
 
	IF @NSmpl = 3
	SET @Result = (SELECT AVG(Valeur) FROM RawData WHERE (NSmpl <= 3 and NSmpl > 0) AND  Produit = @Produit)
 
	IF @NSmpl > 3
	BEGIN
		SET @Cible = (SELECT AVG(Valeur) FROM RawData WHERE (NSmpl <= 3 and NSmpl > 0) AND  Produit = @Produit)
		SET @Result = CASE	WHEN @Moy < 0.8 * @Cible THEN (SELECT AVG(Valeur) FROM RawData WHERE (NSmpl <= @NSmpl and NSmpl > @NSmpl - 3) AND  Produit = @Produit) 
							ELSE @Cible END
	END
 
	RETURN @Result
END

Il faudrait que je puisse stocker la valeur @Cible en dehors de la fonction, mais je ne sais pas comment faire...

Merci d'avance, et bonne après-midi !

[SQLpro]

Il faut que vous utilisiez les fonctions de fenêtrage avec l'option RANGE / ROWS dans la clause OVER.

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

A +

[Donpi]

Si c'est précisément 3 il y a aussi la fonction LAG()
Mais j'avoue, c'est pas très élégant

Code :

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

1
2
Select (<LaValeur>+ Lag(<LaValeur>,1) OVER ( ORDER BY <Les tri>) + Lag(<LaValeur>,2) OVER ( ORDER BY <Les tri>))/3 [Moyenne des 3 dernière valeurs]
From <LaTable>

Si il n'y a pas 3 valeurs, la moyenne est nulle

[xogene]

@SQLPro : je suis sur SQL 2008, il ne prend pas en charge les ROWS RANGE. Mais merci pour le lien vers votre article, j'ai commencé à lire, ça me servira pour du dev perso sur SQL2014.

@Donpi : le problème n'est pas tant de calculer la nouvelle cible, mais bien de la stocker et de pouvoir l'utiliser dans un test dont on ne sait pas quand il se produira (je pourrais très bien avoir 20 résultats qui restent dans la tolérance avant de devoir réévaluer la cible).

[LapinClair]

Pourquoi ne pas passer par une table temporaire globale, ##MaVariable

[SQLpro]

@SQLPro : je suis sur SQL 2008, il ne prend pas en charge les ROWS RANGE. Mais merci pour le lien vers votre article, j'ai commencé à lire, ça me servira pour du dev perso sur SQL2014.

@Donpi : le problème n'est pas tant de calculer la nouvelle cible, mais bien de la stocker et de pouvoir l'utiliser dans un test dont on ne sait pas quand il se produira (je pourrais très bien avoir 20 résultats qui restent dans la tolérance avant de devoir réévaluer la cible).

Dans ce cas utilisez un RW_NUMBER avec ordre DESC pour numéroter les lignes de vos lot et prenez les trois derniers avec RN <= 3.

A +

[xogene]

Merci pour vos réponses.

@SQLPro : Mon problème n'est pas de calculer les moyennes des N derniers ou la cible sur base des N derniers. Mon problème est de stocker une valeur cible (pas de la calculer), de pouvoir la rappeler lors du test, et, le cas échéant, la recalculer (pas de soucis là non plus) pour ensuite la réutiliser plus tard.

C'est donc une variable. Tant que le test est faux, la cible ne change pas, elle a été calculée une fois au début et c'est bon. C'est une fois que le test est vrai que la cible doit être réévaluée, et stockée puisque c'est cette nouvelle valeur cible qui sert lors du test suivant.

@LapinClair : c'est une piste intéressante, j'espère juste avoir une solution qui m'évite les tables temp.