Calculer une moyenne sur plusieurs colonnes [Résolu]

[stöckli]

Bonjour,

j'ai déjà cherché à gauche à droite, mais je n'ai pas trouvé de réponse à cette question apparemment assez simple...

Mon problème est le suivant: dans une table, je stocke 4 paramètres, S1, S2, S3 et S4. Je souhaite créer une vue qui me calcule la moyenne de ces 4 paramètres pour donner un indice ind_s.

Pour l'instant, je calcule de façon naïve:

Code :

1
2
SELECT (s1 + s2 + s3 + s4) / 4 AS ind_s 
FROM my_table

Le souci est que certains paramètres peuvent parfois être NULL.

Si mes valeurs sont: 10, 20, 30, 40, la moyenne calculée est 25
Si mes valeurs sont: 10, 20, NULL, 40, la moyenne calculée est 17.5. Je souhaiterais qu'elle soit 23.3 ((10+20+40)/3)

Quelqu'un a-t-il une idée?

La requête

Code :

1
2
SELECT avg(s1, s2, s3, s4) AS ind_s 
FROM my_table

Ne fonctionne évidemment pas mais c'est le comportement que je souhaite imiter.

Merci d'avance et meilleures salutations,

Stéphane

[SQLpro]

Votre table est à l’évidence mal modélisée et tout ce qui est mal modélisé conduit immanquablement à des requêtes complexes peu performantes et non évolutives, donc entrainant de la maintenance !

Voic l'épouvantable requête qu'il faut faire suite à l’horreur que constitue votre table :

Code :

1
2
3
4
5
6
SELECT COALESCE(s1, 0) + COALESCE(s2, 0) + COALESCE(s3, 0) + COALESCE(s4))
       / ( CASE WHEN s1 IS NULL THEN 0 ELSE 1 END 
         + CASE WHEN s2 IS NULL THEN 0 ELSE 1 END 
         + CASE WHEN s3 IS NULL THEN 0 ELSE 1 END 
         + CASE WHEN s4 IS NULL THEN 0 ELSE 1 END)   
FROM  my_table

Si vous aviez "sortit" ces paramètres dans une table fille, voici la requête à faire :

Code :

1
2
3
SELECT AVG(S)
FROM   MaTableFille
GROUP  BY CLEF_TABLE_MERE

Et si vous ajoutez un nouveau paramètre S5, la première requête est à refaire, la seconde non !

Cela, s’appelle le respect des formes normales.

A +

[stöckli]

Merci beaucoup, je vais donc revoir la normalisation de mes tables!

meilleures salutations,

Stéphane

[MaitrePylos]

Tu auras rectifier la petite coquille de SqlPro

Code :

1
2
 
COALESCE(s4))

par

Code :

1
2
 
COALESCE(s4,0))

[stöckli]

Oui, j'avais rectifié... mais la piste de la re-modélisation semble tout de même plus évolutive! Je retourne donc à mes études :-)

Au passage, je serais curieux de savoir s'il y a une raison "logique" au fait que l'on ne peut pas, en SQL, facilement calculer une moyenne entre plusieurs colonnes d'une même table, par exemple

Code :

SELECT avg(col1, col2, col3) FROM mytable

J'imagine bien que c'est n'est pas un "oubli" dans l'implémentation du langage, mais qu'il y a bien une raison conceptuelle derrière. Si quelqu'un a une piste de réflexion, ça m'intéresserait de comprendre!

Encore merci à tous 2 pour vos réponses

Stéphane

[estofilo]

Envoyé par stöckli

Code :

Le souci est que certains paramètres peuvent parfois être NULL.

Si mes valeurs sont: 10, 20, 30, 40, la moyenne calculée est 25
Si mes valeurs sont: 10, 20, NULL, 40, la moyenne calculée est 17.5. Je souhaiterais qu'elle soit 23.3 ((10+20+40)/3)

Quelqu'un a-t-il une idée?

Tu peux utiliser une astuce spécifique à postgres consistant à transformer dynamiquement les colonnes en ligne:

Code :

1
2
 
SELECT avg(x) FROM (SELECT unnest(array[s1,s2,s3,s4]) x FROM mytable) s;

unnest() est intégré à partir de la 8.4, pour les versions d'avant il faut définir une fonction d'aggrégat personnelle à la place.