Discussion :

[yop solo]

Bonjour a tous,

J'ai une procstoc a realiser pour sortir un tableau de mediane par groupe

genre la liste d'enregistrement est le resultat d'une assez grosse requete:
ID | groupe | valeur
1 A 10
2 A 20
3 B 20
4 A 10
5 C 10
6 A 20
7 B 30

et je dois sortir :
A | 15
B | 25
C | 10

je bute bettement sur l'insertion du calcul au millieu de ma mega requete avec 6 jointures


qui pourrais me donner une idée de l'enchainement?
une premiere requete pour avoir ma table intermediaire sur laquelle je fait mes calculs de mediane ou le calcul dans le where de la mega requete?

[aieeeuuuuu]

Bonjour,

vous pouvez en effet reprendre le résultat de votre requête initiale en expression de table commune ou en pseudo table, pour travailler dessus comme une table.

[yop solo]

Bonjour et merci de votre reponse

je suis effectivement parti la dessus en creeant une #tmp (partant du principe que la table resultante de la premiere requete est utilisée quatre fois dans le calcul de la mediane

J'ai trouvé cette requette en fouillant et c'est apparement la plus optimisée qui soit:

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 AVG(VAL)
FROM (SELECT MAX(STT_VALEUR) AS VAL
FROM (SELECT TOP(SELECT CASE
WHEN N % 1 = 0 THEN (N /2) + 1
ELSE N / 2
END AS TOPN
FROM (SELECT COUNT(*) AS N
FROM dbo.T_STATISTIQUES_STT) AS T)
STT_VALEUR
FROM dbo.T_STATISTIQUES_STT
ORDER BY STT_VALEUR) AS T1
UNION
SELECT MIN(STT_VALEUR) AS VAL
FROM (SELECT TOP(SELECT CASE
WHEN N % 1 = 0 THEN (N /2) + 1
ELSE N / 2
END AS TOPN
FROM (SELECT COUNT(*) AS N
FROM dbo.T_STATISTIQUES_STT) AS T)
STT_VALEUR
FROM dbo.T_STATISTIQUES_STT
ORDER BY STT_VALEUR DESC) AS T2
) AS T

mais je me heurte a un pb de base en remplacant dbo.T_STATISTIQUES_STT par ma #tmp.
J'ai en effet plusieurs groupes different et non pas une simple liste sur laquelle effectuer ma mediane. Ma #tmp a une troisieme colonne de code_groupe (en fait, une quatrieme egalement, chaque groupe est une combinaison des deux clé)

Pouvez vous m'aider la dessus svp, ca n'est pas forcement compliqué, mais je ne trouve pas

[SQLpro]

A lire sur le sujet : http://sqlpro.developpez.com/optimisation/mediane/

A +

[aieeeuuuuu]

vous n’êtes pas obligés de passer par une table temporaire.

Vous pouvez utiliser une cte.

Quelque chose comme :

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
 
WITH cte AS (
    [votre requête ici, avec alias de colonnes pour groupe et valeur]
)
,
tmp AS (
	select 
		groupe, 
		valeur, 
		row_number() over (partition by groupe order by valeur) as rn,
		count(*) over (partition by groupe) as ct
	from  cte
)
select groupe, avg(valeur) as mediane
from tmp
where	rn BETWEEN ct/2 + ct%2  AND ct/2 + 1
group by groupe

[yop solo]

@SQLPro: merci pour le lien, je l'avais deja parcouru, pas mal du tout, c'est de la d'ailleurs que j'en ai tiré la requete

@aieeeuuuuu : grand merci mais juste
J'ai une erreur d'execution sur le WITH:

Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

et sur la virgule apres ma requete:

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ','.

[yop solo]

Je ne vois pas le probleme....

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
37
38
39
 DECLARE @FISRTDATE date
DECLARE @LASTDATE date
SET @LASTDATE = CAST(GETDATE() AS DATE)
SET @FISRTDATE = DATEADD(DAY, -150, @LASTDATE)
 
 
WITH cte (CODE_OP , CODE_SERIE, VALEUR)
AS (
SELECT TITRE.CODE_OP , MAS.CODE_SERIE, R_ACTION.VALEUR 
FROM R_ACTION
JOIN TITRE  
on R_ACTION.CODE_TITRE = TITRE.CODE_TITRE
JOIN DIF as T
on R_ACTION.CODE_DIF = T.CODE_DIF
JOIN CLASSES as C
on T.CODE_CLASSE = C.CODE_CLASSE
JOIN MAS
on C.ID_MAS = MAS.ID_MAS
 
WHERE R_ACTION.DV_DATE <= @LASTDATE 
AND R_ACTION.VALEUR <> 0
AND R_ACTION.DV_DATE >= @FISRTDATE 
AND (R_ACTION.CODE_ETAT = 1 OR R_ACTION.CODE_ETAT = 3)
AND R_ACTION.CODE_TYPE_DV = 2
 
GROUP BY TITRE.CODE_OP, MAS.CODE_SERIE
)
,
tmp AS (
	SELECT 
		CODE_OP, CODE_SERIE, VALEUR, 
		row_number() over (partition BY CODE_OP, CODE_SERIE ORDER BY VALEUR) AS rn,
		count(*) over (partition BY CODE_OP, CODE_SERIE) AS ct
	FROM  cte
)
SELECT CODE_OP, CODE_SERIE, avg(VALEUR) AS mediane 
FROM tmp 
WHERE rn BETWEEN ct/2 + ct%2  AND ct/2 + 1 
GROUP BY CODE_OP, CODE_SERIE

....

[yop solo]

trouvé!

bon c'etait sioux!
manquais un ";" devant la clause WITH

It's work!

Grand merci a aieuuuuuu