Calculo de Médias Ponderada com valores não inteiros

[Leudassdf 6]

Boas pessoal,

Eu tenho o codigo para calcular as médias, de campos nao numéricos). Como podem ver na query abaixo só para o parametro Postura tenho que fazer esta query enorme. E eu precisa de replicar isto para mais 5 parametros. Gostaria de saber se não existe uma forma mais acessivel de fazer isto.

select Nome, case
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 1 and 1.49999999 then "I"
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 1.5 and 2.49999999 then "S"
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 2.5 and 3.49999999 then "B"
else "MB"
end AS 'Postura'
--Se quissesse para outro parametro teria que fazer isto e assim sucessivamente:
,case
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 1 and 1.49999999 then "I"
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 1.5 and 2.49999999 then "S"
when
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)*1+SUM(case  PosturaAtencao when "S" then 1 else 0 end)*2+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)*3+ SUM(case  PosturaAtencao when "MB" then 1 else 0 end)*4)/
(SUM(case  PosturaAtencao when "I" then 1 else 0 end)+SUM(case  PosturaAtencao when "S" then 1 else 0 end)+
SUM(case  PosturaAtencao when "B" then 1 else 0 end)+SUM(case  PosturaAtencao when "MB" then 1 else 0 end))  between 2.5 and 3.49999999 then "B"
else "MB"
end AS 'NOVOPARAM1'
from Avaliacao JOIN Alunos ON (Alunos.NAluno=Avaliacao.Naluno)
where nTurma=1 and nReuniao=1
group by Nome;

Alguem tem sugestões para resolver o meu problema?

Cumprimentos,

Leandro

[HappyHippyHippo 1174]

eu tenho uma sugestão.

além de apresentares o que pensas ser a solução, apresentas o panorama do problema (tabela(s) e a sua estrutura) assim como a fórmula que pretendes executar

[eunito 0]

A nossa ideia consiste no seguinte:

1. obter contagens de I,S,B e MB por cada coluna (Parametro) na seguinte tabela Avaliacao:

CREATE TABLE Avaliacao(
Naluno INT NOT NULL ,
nTurma INT NOT NULL ,
nDisc INT NOT NULL ,
PosturaAtencao VARCHAR(2) NOT NULL ,
InteresseEempenho VARCHAR(2) NOT NULL ,
CapDeAqEaplicConhec VARCHAR(2) NOT NULL ,
CapIniciativa VARCHAR(2) NOT NULL ,
CapComunicacao VARCHAR(2) NOT NULL ,
TrabEqCoopTaref VARCHAR(2) NOT NULL ,
nReuniao INT NOT NULL ,
nProf INT NOT NULL ,
DataAvaliacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
CONSTRAINT chk_PosturaAtencao CHECK (PosturaAtencao IN ('NA', 'I', 'S', 'B','MB')) ,
CONSTRAINT chk_InteresseEempenho CHECK (InteresseEempenho IN ('NA', 'I', 'S', 'B','MB')) ,
CONSTRAINT chk_CapDeAqEaplicConhec CHECK (CapDeAqEaplicConhec IN ('NA', 'I', 'S', 'B','MB')),
CONSTRAINT chk_CapIniciativa CHECK (CapIniciativa IN ('NA', 'I', 'S', 'B','MB')) ,
CONSTRAINT chk_CapComunicacao CHECK (CapComunicacao IN ('NA', 'I', 'S', 'B','MB')) ,
CONSTRAINT chk_TrabEqCoopTaref CHECK (TrabEqCoopTaref IN ('NA', 'I', 'S', 'B','MB')) ,
CONSTRAINT PK_Avaliacao PRIMARY KEY (Naluno,nTurma,nDisc,nReuniao,nProf) ,
CONSTRAINT FK_Avaliacao1 foreign key(Naluno) REFERENCES Alunos(NAluno) ,
CONSTRAINT FK_Avaliacao2 foreign key(nDisc) REFERENCES Disciplina(IDDiscip) ,
CONSTRAINT FK_Avaliacao3 foreign key(nTurma) REFERENCES Turma(IDTurma) ,
CONSTRAINT FK_Avaliacao4 foreign key(nProf) REFERENCES Professor(IDProf) ,
CONSTRAINT FK_Avaliacao5 foreign key(nReuniao) REFERENCES Reuniao(IDReuniao)
);

Através de um procedimento armazenado semelhante ao que se encontra acima conseguimos obter algo como isto:

Este PROC devolve uma tabela do género:

Nome # PA_I # PA_S # PA_B # PA_MB

Aluno 1 # 4 # 2 # 0 # 4

Aluno 2 # 0 # 5 # 1 # 4

...

sendo que PA_I é a contagem dos insufs (I) na coluna da Postura e Atenção, etc... Esta parte está a funcionar.

No final esta tabela tem 24colunas (6 parâmetros x 4 valores de contagem - I,S,B e MB).

2. Para além desta tabela queríamos também calcular a média para cada parametro baseado no seguinte pressuposto:

Insufs têm peso 1,

Sufs têm peso 2,

B têm peso 3 e

MB têm peso 4.

Basicamente para obtermos a média no parâmetro Postura e Atenção (PA) do:

- aluno 1 (por observação da tabela acima) temos de fazer (4*1 + 2*2 + 0*3 +4*4)/(4+2+0+4)

- aluno 2 (por observação da tabela acima) temos de fazer (0*1 + 5*2 + 1*3 +4*4)/(0+5+1+4)

... (para os restantes alunos e para os restantes parâmetros (IE - interesse e empenho, ... são 5 ao todo...)

tabelámos uma gama para a conversão do valor numérico obtido para a média de forma a se converter para um valor qualitativo.

I = [1 ; 1.5[

S = [1.5 ; 2.5[

B = [2.5 ; 3.5[

MB = [3.5 ; 4.5[

E aqui é que estamos com esta dificuldade, que o meu colega referiu...

Alguém tem ideia de como se pode fazer em mySQL o cálculo da média e conversão do valor obtido para o valor qualitativo correspondente?

Obrigado!

Editado 28 de Abril de 2016 por eunito

[HappyHippyHippo 1174]

pelo que apresentas, a melhor solução +e mesmo stored procedures, isto porque, mesmo que se simplifique o código SQL, ficará sempre bastante complicado, com vários subqueries