[résolu] Moyenne sur plusieurs colonnes enum

Moyenne sur plusieurs colonnes enum [résolu] - SQL/NoSQL - Programmation

Marsh Posté le 19-02-2007 à 17:13:56    

Bonjours à tous.
 
Je dispose d'une table myslq avec les champs classement_1, classement_2, classsment_3, classement_4. Ces champs sont de type ENUM et peuvent prendre les valeurs 0,1,2,3,4,5,6,7 ou 'NC' (pour non classé).
 
Je souhaiterai faire la moyenne pour chaque enregistrement de ces colonnes, mais en ne tenant pas compte des NC (c'est à dire en ne les incluant pas dans la moyenne).
 
Par exemple:
id: 1 classement_1: NC classement_2: 3 classement_3: 7 classement_4: 2
 
renverra (3+7+2)/3 = 4
 
mais celui ci:
id: 2 classement_1: 5 classement_2: 3 classement_3: 7 classement_4: 2
 
renverra (5+3+7+2)/4 = 4,25
 
J'ai essayé sans grand succès de combiner des AVG, GROUP BY et sous requetes ... sans grand succès, et google ne m'aide pas beaucoup non plus!
 
Bref, auriez vous une idée de comment on peut faire ca en mysql?
 
Merci beaucoup!
 
Clem


Message édité par sazeod le 19-02-2007 à 19:53:31
Reply

Marsh Posté le 19-02-2007 à 17:13:56   

Reply

Marsh Posté le 19-02-2007 à 17:19:30    

déjà, AVG et autres marchent sur des LIGNES et non des colonnes.
modifie donc la structure de ta table afin d'avoir 7 lignes et non 7 colonnes
 
un NULL est ignoré par AVG lors du calcul. Ainsi il te suffit de replacer NC par NULL dans la requête, et le tour est joué.

Reply

Marsh Posté le 19-02-2007 à 17:25:27    

Ok, merci de ta réponse MagicBuzz.
 
Mais n'y a t'il vraiment pas d'autre choix que de devoir modifier la structure de ma table?
 
(c'est une structure commune a plusieurs developpeurs qui font parti de mon groupe de travail et ca m'embeterai vraiment de leur imposer de changer de modèle).
 
D'autre part, je remplace comment NC par NULL dans la requete?

Reply

Marsh Posté le 19-02-2007 à 17:52:39    

replace(champ, 'NC', null)
 
sinon, tu peux passer par une vue qui permet de faire la mise en lignes de tes colonnes si tu ne peux pas toucher à la structure.

Reply

Marsh Posté le 19-02-2007 à 18:53:27    

Merci, ca débroussaille déjà un peu le problème!!
 
Reste qu'a trouver comment je fais "une vue qui permet de faire la mise en lignes de tes colonnes".
 
Tu aurai un exemple/une ébauche de requete a me proposer?

Reply

Marsh Posté le 19-02-2007 à 19:04:00    

ben... y'a pas 36 solution : 7 union de suite qui prennent chacun une colonne... et qui rajoutent un champ "en dur" correspondant au numéro de colonne.
 
genre :
 


tatable
--------
id
col1
col2
col3
col4
col5
col6
col7
 
create view tavue
(
   id as number,
   col as number,
   val as number
)
as
select id, 1, cast(replace(col1, 'NC', null) as number)
union all
select id, 2, cast(replace(col2, 'NC', null) as number)
union all
select id, 3, cast(replace(col3, 'NC', null) as number)
union all
select id, 4, cast(replace(col4, 'NC', null) as number)
union all
select id, 5, cast(replace(col5, 'NC', null) as number)
union all
select id, 6, cast(replace(col6, 'NC', null) as number)
union all
select id, 7, cast(replace(col7, 'NC', null) as number);
 
du coup la structure sera :
 
tavue
------
id
col
val
 
et tu pourras faire :
 
select id, avg(val) moy
from tavue
group by id


Message édité par MagicBuzz le 19-02-2007 à 19:07:08
Reply

Marsh Posté le 19-02-2007 à 19:09:19    

sinon si le decode existe en mysql un truc pas joli du genre:
 

Code :
  1. select
  2. id,
  3. (decode(classement_1,'NC',0,classement_1)
  4. +decode(classement_2,'NC',0,classement_2)
  5. +decode(classement_3,'NC',0,classement_3)
  6. +decode(classement_4,'NC',0,classement_4))
  7. /
  8. (decode(classement_1,'NC',0,1)
  9. +decode(classement_2,'NC',0,1)
  10. +decode(classement_3,'NC',0,1)
  11. +decode(classement_4,'NC',0,1))
  12. from
  13. broumbroum


 
devrait le faire, je te laisse le soin de prendre en charge l'éventuelle division par 0
 
c'est moche mais bon on est pas toujours la pour faire de l'alexandrin

Message cité 1 fois
Reply

Marsh Posté le 19-02-2007 à 19:28:20    

Le code de test complet et corrigé (SQL Server Express 2005) :
 


CREATE TABLE dbo.tatable
 (
 id numeric(18, 0) NOT NULL,
 col1 varchar(2) NOT NULL CHECK ((len(col1) = 1 and col1 between '1' and '7') or col1 = 'NC'),
 col2 varchar(2) NOT NULL CHECK ((len(col2) = 1 and col2 between '1' and '7') or col2 = 'NC'),
 col3 varchar(2) NOT NULL CHECK ((len(col3) = 1 and col3 between '1' and '7') or col3 = 'NC'),
 col4 varchar(2) NOT NULL CHECK ((len(col4) = 1 and col4 between '1' and '7') or col4 = 'NC'),
 col5 varchar(2) NOT NULL CHECK ((len(col5) = 1 and col5 between '1' and '7') or col5 = 'NC'),
 col6 varchar(2) NOT NULL CHECK ((len(col6) = 1 and col6 between '1' and '7') or col6 = 'NC'),
 col7 varchar(2) NOT NULL CHECK ((len(col7) = 1 and col7 between '1' and '7') or col7 = 'NC')
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.tatable ADD CONSTRAINT
 PK_tatable PRIMARY KEY CLUSTERED  
 (
 id
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
GO
 
insert into tatable (id, col1, col2, col3, col4, col5, col6, col7) values (1, '5', '5', '5', '5', '5', '5', '5');
insert into tatable (id, col1, col2, col3, col4, col5, col6, col7) values (2, '1', '2', '3', '4', '5', '6', '7');
insert into tatable (id, col1, col2, col3, col4, col5, col6, col7) values (3, '3', '3', '3', 'NC', '5', '5', '5');
insert into tatable (id, col1, col2, col3, col4, col5, col6, col7) values (4, 'NC', 'NC', 'NC', 'NC', 'NC', 'NC', 'NC');
go
 
create view tavue  
as  
select id, 1 col, case col1 when 'NC' then null else cast(col1 as numeric) end val from tatable
union all  
select id, 2 col, case col2 when 'NC' then null else cast(col2 as numeric) end val from tatable  
union all  
select id, 3 col, case col3 when 'NC' then null else cast(col3 as numeric) end val from tatable
union all  
select id, 4 col, case col4 when 'NC' then null else cast(col4 as numeric) end val from tatable
union all  
select id, 5 col, case col5 when 'NC' then null else cast(col5 as numeric) end val from tatable
union all  
select id, 6 col, case col6 when 'NC' then null else cast(col6 as numeric) end val from tatable
union all  
select id, 7 col, case col7 when 'NC' then null else cast(col7 as numeric) end val from tatable;
go
 
select id, avg(val) moy  
from tavue  
group by id;
go


 
Résultat :


 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
id                                      moy
--------------------------------------- ---------------------------------------
1                                       5.000000
2                                       4.000000
3                                       4.000000
4                                       NULL
Avertissement : la valeur NULL est éliminée par un agrégat ou par une autre opération SET.
 
(4 row(s) affected)

Reply

Marsh Posté le 19-02-2007 à 19:29:28    

casimimir a écrit :

sinon si le decode existe en mysql un truc pas joli du genre: (DECODE)


decode existe sous MySQL, mais ça sert à changer l'encodage d'une chaîne de caractère.
il faut utiliser CASE à la place, comme dans mon exemple SQL Server.

Reply

Marsh Posté le 19-02-2007 à 19:30:03    

t'ain, y'avais que 4 colonnes, et dire que je me suis fait chier à en faire 7 :o

Reply

Marsh Posté le 19-02-2007 à 19:30:03   

Reply

Marsh Posté le 19-02-2007 à 19:34:56    

ho en oracle le decode c'est un beau fourre tout il arrive toujours a caster et retrouver ses jeunes ^^
 
j'avais la flemme de me faire un case et avec mysql je sais jamais ce qui est supporté et je me disais qu'il y avait peut-etre une syntaxe bionique

Reply

Marsh Posté le 19-02-2007 à 19:44:03    

bah non parceque decode() est déjà utilisé par une fonction inutile de mysql ;)
 
mais sinon, le case est pas mal aussi, et même s'il est plus lourd, bien plus lisible car il est indentable proprment

Reply

Marsh Posté le 19-02-2007 à 19:53:10    

Ok nickel, je viens de tester tout ca, ca marche impec! Merci beaucoup!

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed