Moyenne la plus haute [Résolu]

[bouket]

Bonjour,

j'ai deux tables sql:

temperature(year,month,day,temperature)
sick(year, month,day,nsick)

Il faut que je trouve la moyenne des 'temperature' pour le mois de chaque année qui a la plus haute moyenne de 'nsick'

j'ai essayé pas mal de chose, mais je n'arrive pas à séparer le mois le plus haut de chaque année...

Code :

1
2
3
SELECT AVG(nsick) FROM nsick GROUP BY year, month ORDER BY AVG(nsick)
 
SELECT AVG(temperature) FROM temperature WHERE (month, year)=???

je bloque à essayer de réunir tous ça

merci d'avance pour votre aide,
bouket

[punkoff]

bonjour, quel est votre SGBD ?

[bouket]

Mysql

[punkoff]

si vous n'avez pas trop de donnée ceci devrait fonctionner...

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT year, month, avg(temperature)
FROM t_temperature a
WHERE EXISTS (SELECT NULL FROM (SELECT year, month, avg(nsick) AS avg_nsick
				FROM t_sick c
				WHERE c.year = a.year
				GROUP BY year, month
				ORDER BY year, avg_nsick DESC
				LIMIT 1) AS b WHERE a.year = b.year AND a.month = b.month)
GROUP BY year, month

mais je ne suis pas satisfait de cette solution... sans fonction de fenêtrage c'est un casse tête :p

[bouket]

Merci pour la réponse,

j'ai modifié légèrement le code pour qu'il soit reconnu par mysql (il n'aimait pas notamment l'absence de "temperature a" dans la clause WHERE...
et j'ai levé l’ambiguïté comme il me le demandait pour 'year' et 'month'

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT a.year, a.month, avg(temperature)
FROM temperature a
WHERE EXISTS (SELECT NULL FROM (SELECT c.year, c.month, avg(nsick) AS avg_nsick
				FROM sick c, temperature a
				WHERE c.year = a.year
				GROUP BY year, month
				ORDER BY year, avg_nsick DESC
				LIMIT 1) AS b WHERE a.year = b.year AND a.month = b.month)
GROUP BY year, month

Cependant, ce code ne renvoie que la première année, le mois et la moyenne de la température de ce mois, avec bien le mois de cette année là qui à le plus de nsick en moyenne.
il occulte les années suivantes...
la base de données possède beaucoup d'entrées.

Sur Oracle, serait-ce plus facile ?

Cdlt,
Adrien

ps: merci

[punkoff]

C'est bizarre car j'ai teste avant de poster le resultat (sur postgresql ceci ddit) alors sur oracle ca sera effectivement beaucoup plus simple et performant. La cest une requete scalaire qui va tomber dans les choux si vous avez de la volumetrie.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH tmp. AS (
SELECT year, month, avg(nsick) AS avg_sick
, row_number() over(partition BY year ORDER BY avg(nsick) DESC) AS rnk
FROM t_sick
GROUP BY year, month)
 
 
SELECT b.year, b.month, avg(temperature), max(avg_sick)
FROM tmp a
INNER JOIN t_temperature b ON a.year = b.year AND a.month =b.month
WHERE a.rnk = 1

cette requete ne fera qu'un seul scannage de la t_sick et sera bcp plus performante que son ammologue mysql

[bouket]

bonsoir,

finalement j'ai opté pour ce code même s'il se fait en plusieurs étapes :

Code :

1
2
3
4
5
6
7
8
9
CREATE TABLE TMP AS (SELECT AVG(nsick) AS moy_sick, sick.year, sick.month , AVG(temperature) AS moy_temperature
FROM sick INNER JOIN temperature
ON sick.month=temperature.month AND sick.year=temperature.year
GROUP BY month, year
ORDER BY AVG(nsick) DESC);
 
SELECT year, month, moy_temperature, max(moy_sick) FROM TMP GROUP BY year;
 
DROP TABLE TMP;

que l'on peut même compacter en une étape :

Code :

1
2
3
4
5
6
SELECT year, month, moy_temperature, max(moy_sick) FROM (SELECT AVG(nsick) AS moy_sick, sick.year, sick.month , AVG(temperature) AS moy_temperature
FROM sick INNER JOIN temperature
ON sick.month=temperature.month AND sick.year=temperature.year
GROUP BY month, year
ORDER BY AVG(nsick) DESC) AS moy_table
GROUP BY year;

marche sous mysql

merci pour votre aide,
bouket

[skuatamad]

La requête est fausse, le dernier GROUP BY devrait s'écrire (ce qui ne convient évidemment pas):

Code :

GROUP BY year, month, moy_temperature

A lire sur le GROUP BY et Mysql

Est ce que cette requête convient :

Code :

1
2
3
4
5
6
7
8
SELECT sick.year, sick.month , AVG(nsick) AS moy_sick, AVG(temperature) AS moy_temperature
  FROM sick 
 INNER JOIN temperature
    ON sick.month=temperature.month AND sick.year=temperature.year
 GROUP BY year, month
HAVING AVG(nsick) >= ALL (SELECT avg(nsick) 
                            FROM sick 
                           GROUP BY year, month)

[punkoff]

Vous ne faites pas la relation entre la moyenne maximum de l'année en cours dans le having.
Du coup non je ne penses pas que celà convienne.

[skuatamad]

Oui Punkoff, j'était en train de modifier de la sous-requête pour tentative de prise en compte du "par année" (mais je ne sais pas si c'est bon) :

Code :

1
2
3
4
5
6
7
8
9
SELECT sick.year, sick.month , AVG(nsick) AS moy_sick, AVG(temperature) AS moy_temperature
  FROM sick 
 INNER JOIN temperature
    ON sick.month=temperature.month AND sick.year=temperature.year
 GROUP BY year, month
HAVING AVG(nsick) >= ALL (SELECT avg(nsick) 
                            FROM sick s2
                           WHERE s2.year = sick.year
                           GROUP BY month)

[bouket]

votre solution fonctionne aussi bien que la mienne...
grace au ORDER BY ... DESC, il prend les bonnes valeurs (car il prend les premieres par défaut...) en tout cas chez moi oui ! mais, c'est vrai que c'est un peu instable...
pas chez vous ?

[skuatamad]

Je n'ai pas testé ma requête, je ne sais donc pas si elle fonctionne correctement.

Par contre comme mentionné dans le lien : 5. Eviter le problème

Qu'en est-il d'un comportement comme celui d'Oracle ?
...
ORA-00979: not a GROUP BY expression
...
Donc, bien que le message d'erreur puisse sembler surprenant, il est toujours préférable que de retourner des données sans aucun sens, en silence.

Donc les colonnes month et moy_temperature sont selectionnées aléatoirement...
Peut être qu'avec simplement un MAX (et l'ORDER BY desc) MySql s'en sort mieux, mais la requête reste fausse et à tout moment elle pourra renvoyer un résultat faux.

C'est une syntaxe à éviter sauf quand on sait exactement ce que l'on fait et que l'on est certain que les colonnes selectionnées non présentes dans le GROUP BY sont constantes sur le regroupement ce qui n'est pas le cas ici.

[bouket]

je comprends ! il semblerait que mysql prennent la première valeur qui lui passe sous la main, mais sur oracle il ne fera rien si j'ai bien compris car sinon, il doit choisir au hasard;


merci pour les éclaircissements !!

CODE FONCTIONNANT :

Code :

1
2
3
4
5
6
7
8
9
SELECT sick.year, sick.month , AVG(nsick) AS moy_sick, AVG(temperature) AS moy_temperature
  FROM sick 
 INNER JOIN temperature
    ON sick.month=temperature.month AND sick.year=temperature.year
 GROUP BY year, month
HAVING AVG(nsick) >= ALL (SELECT avg(nsick) 
                            FROM sick s2
                           WHERE s2.year = sick.year
                           GROUP BY month)