Requete avec moyennes [Résolu]

[colonel.klink]

Bonjour,

Je souhaiterais réaliser dans la même requête :
- une moyenne des trois montants les plus élevés
- une moyenne des trois montants les plus bas
- une moyenne de tous les montants

Pouvez vous m'aider pour imbriquer les fonctions type "TOP", "AVG" ...

Merci par avance.

[SQLpro]

Utilisez pour ce faire les fonction de fenêtrage : http://sqlpro.developpez.com/article...clause-window/

Jeun d'essais :

Code :

1
2
3
CREATE TABLE TM (MONTANT FLOAT)
 
INSERT INTO TM VALUES (123), (456), (789), (101112)

la requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH T AS (
SELECT MONTANT, 
       RANK() OVER(ORDER BY MONTANT) AS DOWN,
       RANK() OVER(ORDER BY MONTANT DESC) AS UP,
       AVG(MONTANT) OVER() AS MOYENNE
FROM   TM),
TD AS (
SELECT AVG(MONTANT) AS MOY_BASSE
FROM   T
WHERE  DOWN >= 3),
TU AS (
SELECT AVG(MONTANT) AS MOY_HAUTE
FROM   T
WHERE  UP >= 3),
TT AS (
SELECT TOP 1 MOYENNE
FROM   T)
SELECT MOYENNE, MOY_BASSE, MOY_HAUTE
FROM   TD CROSS JOIN TU CROSS JOIN TT

Le résultat :

Code :

1
2
3
4
 
MOYENNE                MOY_BASSE              MOY_HAUTE
---------------------- ---------------------- ----------------------
25620                  50950,5                289,5

A +

[colonel.klink]

Bonjour et merci pour cette réponse rapide.

La méthode fonctionne mais les résultats ne sont pas corrects.

Pour reprendre votre exemple, en moyenne haute vous obtenez 289.5 hors la moyenne de (456), (789), (101112) donne 34119.

Je vais essayer de regarder en détail.

[SQLpro]

Je suis dyslexique... J'inverse beaucoup de choses, comme la droite, la gauche, les haute et les bas

Voici la chose rectifiée !

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH T AS (
SELECT MONTANT, 
       RANK() OVER(ORDER BY MONTANT) AS DOWN,
       RANK() OVER(ORDER BY MONTANT DESC) AS UP,
       AVG(MONTANT) OVER() AS MOYENNE
FROM   TM),
TD AS (
SELECT AVG(MONTANT) AS MOY_BASSE
FROM   T
WHERE  DOWN <= 3),
TU AS (
SELECT AVG(MONTANT) AS MOY_HAUTE
FROM   T
WHERE  UP <= 3),
TT AS (
SELECT TOP 1 MOYENNE
FROM   T)
SELECT MOYENNE, MOY_BASSE, MOY_HAUTE
FROM   TD CROSS JOIN TU CROSS JOIN TT

A +

[colonel.klink]

Super, merci beaucoup.

Est-il possible de ramener la valeur "montant" dans cette requête en complément ?

[colonel.klink]

Autant pour moi.

C'est bon j'ai trouvé ce qu'il me fallait.

Merci pour tout.

Bonne soirée

[Waldar]

Une astuce par rapport à la requête de SQLPro pour raccourcir un peu le tout :

Code :

1
2
3
4
5
6
7
8
9
10
11
WITH T (MONTANT, DOWN, UP) AS
(
SELECT MONTANT
     , RANK() OVER(ORDER BY MONTANT  ASC)
     , RANK() OVER(ORDER BY MONTANT DESC)
  FROM TM
)
SELECT AVG(MONTANT)                              AS MOYENNE
     , AVG(CASE WHEN DOWN <= 3 THEN MONTANT END) AS MOY_BASSE
     , AVG(CASE WHEN UP   <= 3 THEN MONTANT END) AS MOY_HAUTE
  FROM T

[colonel.klink]

Bonjour,

Merci.

J'ai néanmoins oublié une contrainte supplémentaire, à savoir, je dois réaliser ces calculs par rapport à un champ qui correspond à la période.

Autrement dit, connaitre les différentes moyennes de chaque période.

Merci

[SQLpro]

Rajoutez un groupage !

A +

[colonel.klink]

Bonjour et merci.

Actuellement j'ai :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH TAB_MOY (MONTANT, DOWN, UP, PERIODE) AS
(
SELECT RATIO.RAT_Valeur
     , RANK() OVER(ORDER BY RATIO.RAT_Valeur  ASC)
     , RANK() OVER(ORDER BY RATIO.RAT_Valeur DESC)
     , RANK() OVER(ORDER BY RAT_Periode ASC)
  FROM RATIO
  WHERE	(RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (RATIO.RAT_Nom = 'Evolution du CA HT')
  GROUP BY RATIO.RAT_Periode, RATIO.RAT_Valeur
)
SELECT CAST (AVG(MONTANT) AS DECIMAL(15,2))      AS Moyenne
     , CAST (AVG(CASE WHEN DOWN <= 3 THEN MONTANT END) AS DECIMAL(15,2)) AS MOY_Basse
     , CAST (AVG(CASE WHEN UP   <= 3 THEN MONTANT END) AS DECIMAL(15,2)) AS MOY_Haute
     , PERIODE 
  FROM TAB_MOY, RATIO
  GROUP BY PERIODE

Seulement, deux problèmes :
- le champ période n'est plus au format AAAA/MM mais sous forme d'entier et les valeurs calculées en moyennes haute et basse ne sont pas bon.

Merci

[colonel.klink]

ok pour les moyennes (mon jeu de test ne possède que deux montants dans le cas testé).

Par contre me reste à afficher ma période correctement.

[colonel.klink]

J'ai trouvé une solution me permettant de ramener toutes les valeurs nécessaires mais j'ai un problème avec le ORDER qui se trouve avant UNION :

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
SELECT 1, RATIO.RAT_Valeur, RATIO.RAT_Periode
FROM         RATIO INNER JOIN CENTRE ON RATIO.CEN_Code_FK = CENTRE.CEN_Code
WHERE     (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (CENTRE.CEN_Ville = 'TOULOUSE') AND (RATIO.RAT_Nom = 'Evolution du CA HT')
UNION
SELECT 2, AVG(RATIO.RAT_Valeur) AS Moyenne, RATIO.RAT_Periode
FROM RATIO
WHERE     (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (RATIO.RAT_Nom = 'Evolution du CA HT')
GROUP BY RATIO.RAT_Periode
UNION 
SELECT TOP 3 3,* FROM
(	SELECT AVG(RAT_Valeur) AS Moy_Basse, RAT_Periode
	FROM RATIO
	WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Evolution du CA HT'
	GROUP BY RAT_Periode
) AS TAB_MOY_BASSE
ORDER BY Moy_Basse DESC
UNION
SELECT TOP 3 4,* FROM
(	SELECT AVG(RAT_Valeur) AS Moy_Haute, RAT_Periode
	FROM RATIO
	WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Evolution du CA HT'
	GROUP BY RAT_Periode
) AS TAB_MOY_HAUTE
ORDER BY Moy_Haute ASC

=> Moy_Basse non valide
=> UNION : syntaxe incorrecte

Merci pour votre aide.

[colonel.klink]

Etant donné que je ne peux pas utiliser le ORDER BY, il faut certainement que j'intègre un RANK ...

[Waldar]

L'order by est exécuté en fin de requête (ou par la présence d'un TOP), or un union constitue bien une et une seule requête.
Il faut donc supprimer celui du milieu, ou rajouter des parenthèses pour mieux séparer les requêtes.

[colonel.klink]

Bonjour,

Je vous remercie.

J'ai finalement réussi à terminer cette requête.
Pour info et si quelqu'un en a besoin, voici ce que ça donne :

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
SELECT 1, RATIO.RAT_Valeur, RATIO.RAT_Periode
FROM         RATIO INNER JOIN CENTRE ON RATIO.CEN_Code_FK = CENTRE.CEN_Code
WHERE     (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (CENTRE.CEN_Ville = 'Ville') AND (RATIO.RAT_Nom = 'Nom')
 
UNION
 
SELECT 2, CAST(AVG(RATIO.RAT_Valeur)AS DECIMAL(15,2)) AS Moyenne , RATIO.RAT_Periode
FROM RATIO
WHERE     (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (RATIO.RAT_Nom = 'Nom')
GROUP BY RATIO.RAT_Periode
 
UNION 
 
SELECT 3, CAST (AVG(RAT_Valeur) AS DECIMAL(15,2)) AS MOY_Basse, RAT_Periode 
FROM	(   SELECT RAT_Valeur, RANK() OVER(PARTITION BY RAT_Periode ORDER BY RATIO.RAT_Valeur ASC) AS Rank_Bas, RAT_Periode
	FROM RATIO	
	WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Nom') AS TAB_MOY_BASSE
WHERE Rank_Bas <=3
GROUP BY RAT_Periode	
 
UNION
 
SELECT 4, CAST (AVG(RAT_Valeur) AS DECIMAL(15,2)) AS MOY_Haute, RAT_Periode 
FROM	(   SELECT RAT_Valeur, RANK() OVER(PARTITION BY RAT_Periode ORDER BY RATIO.RAT_Valeur DESC) AS Rank_Haut, RAT_Periode
	FROM RATIO	
	WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Nom' ) AS TAB_MOY_HAUT
WHERE Rank_Haut <=3
GROUP BY RAT_Periode

Merci