Cálculo a partir de coluna criada "manualmente" no select

[Vinicius Rangel 208]

é.. titulo cabuloso..
Galera como parte de estudo para banco estou realizando um cálculo para descobrir o nível de serviço de um usuário em cima de um produto.

 

no meu último cálculo eu preciso fazer o seguinte

fazer a conta:

demanda - previsao = A

media de A = B

A - B

 

isso dentro do mysql, e o que tenho até agora é:

 

 

SELECT id_produto, 
(SELECT  (demanda - previsao)) as desvio,
(SELECT AVG(desvio)) as media_desvio
FROM produtos_x_hist_venda WHERE id_produto = X

 

isso até funciona o problema é que media_desvio não faz a media de todos os dados que esta na coluna desvio (criada no proprio select), ele faz a conta para cada linha como se isso

 

(SELECT AVG(desvio)) as media_desvio

tivesse em um foreach falando a nível de PHP.

 

como faço para consegui tirar uma média dessa coluna e guardar esse valor dentro de outra coluna e continuar com o cálculo?

 

gostaria de deixar claro que não procuro código e sim uma solução, em um link em uma teoria etc..

 

vlw galera

 

[Motta 645]

O STDDEV(Desvio Padrão) não resolve ?!

 

ou

 

utilizar o select como tabela virtual

 

SELECT id_produto,desvio,media_desvio
from
(   
SELECT id_produto,  
       (SELECT  (demanda - previsao)) as desvio, 
       (SELECT AVG(desvio)) as media_desvio 
FROM produtos_x_hist_venda WHERE id_produto = X 
) virtual

 

Vc trabalha em cima deste select

[Vinicius Rangel 208]

Então motta acontece que eu tenho que seguir um excell que foi passado e nele fizeram esse cálculo na mão, a função STDDEV deu uma diferença de até 6% em alguns produtos o que me fez recorrer ao mesmo tipo de cálculo, em PHP eu faço rapidinho o problema é que eu to aprendeo SQL, MYSQL que seja e então não posso fugir muito do banco.

 

já em relação ao virtual eu nunca ouvi, mas deve ser essa a solução.

vlw cara

 

@edit

 

Não deu certo, ainda continua trazendo a média por linha.

tentei interagir de algumas maneiras colocando a média fora da subquery e ainda fica na mesma

[Motta 645]

seria isto ?

SELECT id_produto,avg(desvio) media_desvio
from (SELECT id_produto,(demanda - previsao) as desvio
      FROM produtos_x_hist_venda WHERE id_produto = X ) virtual
group by id_produto

o problema de calcular um desvio como (demanda - previsao) é que os valores ora ficam positivos ora negativos e se anulam, por isto os calculos de devio ou trabalham com o quadrado ou com a valor absoluto, na planilha excel foi feito como ?

 

[Vinicius Rangel 208]

foi feita com valores negativos e positivos mesmo.

 

o problema é que seu agrupar por item me retorna 1 linha só

precisava desse valor extendido para todas as linhas mesmo que fosse repetido.

 

estou quase conseguindo aqu

[Motta 645]

use o select da média como uma tabela virtual e faça join com a tabela produtos_x_hist_venda

[Vinicius Rangel 208]

Não consegui fazer com esse virtual não, estou estudando ainda..

vlw pela respostas

[Motta 645]

select PRODUTOS_X_HIST_VENDA.*,MEDIA.MEDIA_DESVIO

from (select ID_PRODUTO,AVG(DESVIO) MEDIA_DESVIO

from (SELECT id_produto,(demanda - previsao) as desvio

from PRODUTOS_X_HIST_VENDA where ID_PRODUTO = X ) VIRTUAL

group by ID_PRODUTO) MEDIA,

PRODUTOS_X_HIST_VENDA

where media.ID_PRODUTO = PRODUTOS_X_HIST_VENDA.ID_PRODUTO

[Vinicius Rangel 208]

no final eu fiz no PHP, não consegui completar o estudo no MYSQL mas sua solução funcionou.

vou pensar em migrar.

 

mas teria uma apostila onde posso aprender mais sobre esse modelo de consulta pois pra falar a vdd não entendi muito bem.

[Motta 645]

calcula os desvios (por produto)

(SELECT id_produto,(demanda - previsao) as desvio              
 from PRODUTOS_X_HIST_VENDA where ID_PRODUTO = X ) VIRTUAL

Calcula a média dos desvios (por produto)

(select ID_PRODUTO,AVG(DESVIO) MEDIA_DESVIO        
 from (SELECT id_produto,(demanda - previsao) as desvio              
       from PRODUTOS_X_HIST_VENDA where ID_PRODUTO = X ) VIRTUAL        
group by ID_PRODUTO) MEDIA

 

faz um join dos produtos com as suas méda dos desvios

 

select PRODUTOS_X_HIST_VENDA.*,MEDIA.MEDIA_DESVIO 
from  (select ID_PRODUTO,AVG(DESVIO) MEDIA_DESVIO        
       from (SELECT id_produto,(demanda - previsao) as desvio              
             from PRODUTOS_X_HIST_VENDA where ID_PRODUTO = X ) VIRTUAL        
       group by ID_PRODUTO) MEDIA,        
       PRODUTOS_X_HIST_VENDA  
where media.ID_PRODUTO = PRODUTOS_X_HIST_VENDA.ID_PRODUTO