[résolu] Moyenne sur plusieurs colonnes enum

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [résolu] Moyenne sur plusieurs colonnes enum

sazeod

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

Publicité

MagicBuzz

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é.

sazeod

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?

MagicBuzz

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.

sazeod

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?

MagicBuzz

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

casimimir

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

Code :

select
id,
(decode(classement_1,'NC',0,classement_1)
+decode(classement_2,'NC',0,classement_2)
+decode(classement_3,'NC',0,classement_3)
+decode(classement_4,'NC',0,classement_4))
/
(decode(classement_1,'NC',0,1)
+decode(classement_2,'NC',0,1)
+decode(classement_3,'NC',0,1)
+decode(classement_4,'NC',0,1))
from
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

MagicBuzz

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)

MagicBuzz

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.

MagicBuzz

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

Publicité

casimimir

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

MagicBuzz

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

sazeod

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

FORUM HardWare.fr

Programmation

SQL/NoSQL

[résolu] Moyenne sur plusieurs colonnes enum

Sujets relatifs
[RESOLU]Parse error unexpected $end !!	[RESOLU] Probleme pour renommer les fichiers PHP
menu à plusieurs niveaux	Fonction renvoyant pointeur de structure [Résolu]
[résolu] Probleme d'appel d'un objet dans ma classe	Différence IE/Firefox [Résolu]
[Ruby] [Resolu] Nettoyage de caractères accentués	[html] mailto + modification adresse mail [Résolu]
probleme d'overflow avec variables type Double [résolu]	VBA - application.match et Majuscules [Résolu]
Plus de sujets relatifs à : [résolu] Moyenne sur plusieurs colonnes enum

Page générée en 0.073 secondes