Query com dados ponderados.

[ahsc 1]

Olá pessoal, boa tarde.

 

Estou com um problema aqui no trabalho. Preciso montar uma consulta para trazer alguns dados para montar um relatório. Entretanto eu preciso fazer um filtro que verifique alguns campos (se forem iguais) a montar uma ponderação com os dados restantes. O link abaixo mostra o exemplo de duas linhas com campos iguais.

 

http://prntscr.com/45ynga

 

Os campos AS, MÊS, VARIEDADE e ESTAGIOPAD tem que ser verificados. Caso sejam iguais, precisaria fazer uma média ponderada dos campos PL%, ATR e FIB com o campo PRODUÇÃO.

 

Aqui está a query

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, C.DESCRICAO AS NOMEREGIAO, A.* FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

[Motta 645]

Qual a dúvida !?

[advaldomesquita 93]

@Motta, creio que seja essa:

 

Entretanto eu preciso fazer um filtro que verifique alguns campos (se forem iguais) a montar uma ponderação com os dados restantes.

 

@ahsc

Nestes, sugiro o uso do CASE

[]´s

[ahsc 1]

Como faço isso? O problema é que pode haver mais de uma ocorrência, ou seja, mais de uma linha com características iguais e à partir delas gerar uma linha com as médias ponderadas.

 

Como se faz isso?

Qual a dúvida !?

 

Então Mota.

 

Minha consulta trás vários resultados e nesses resultados ocorrem que os campos AS, MÊS, VARIEDADE e ESTAGIOPAD são iguais e isso não pode. Eu não posso ter uma mesma variedade no mesmo mês no mesmo estágio de produção iguais. Assim, eu precisaria que, ao encontrar esse tipo de ocorrência, eu faça uma média ponderada dessas linhas iguais para resultar uma e isso nem imagino como se faz em sql.

 

No exemplo http://prntscr.com/45ynga note que existe a condição descrita acima. Assim, dessas duas linhas eu precisaria fazer uma só, ponderando os campos pl, atr e fibra com a soma das áreas constantes nas duas linhas.

 

Vc tem alguma ideia de como isso pode ser feito?

[Motta 645]

-- uma tabela fajuta

SELECT estagiario, venda , nota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual

 

ESTAGIARIO      VENDA       NOTA
---------- ---------- ----------
joao          1234.56          5
joao          6543.21          6
joao          1111.11          7

--a media

SELECT estagiario, avg(venda) mvenda, avg(nota) mnota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

 

ESTAGIARIO     MVENDA      MNOTA
---------- ---------- ----------
joao          2962.96          6

--a media poderada

SELECT estagiario, sum(nota*venda)/sum(nota) mpvendanota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

 

ESTAGIARIO MPVENDANOTA
---------- -----------
joao       2956.101667

A média ponderada tem um peso, a média comum o AVG resolve.

 

Ajudou ?

 

[ahsc 1]

-- uma tabela fajuta

SELECT estagiario, venda , nota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual

ESTAGIARIO      VENDA       NOTA
---------- ---------- ----------
joao          1234.56          5
joao          6543.21          6
joao          1111.11          7

--a media

 

SELECT estagiario, avg(venda) mvenda, avg(nota) mnota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

ESTAGIARIO     MVENDA      MNOTA
---------- ---------- ----------
joao          2962.96          6

--a media poderada

 

SELECT estagiario, sum(nota*venda)/sum(nota) mpvendanota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

ESTAGIARIO MPVENDANOTA
---------- -----------
joao       2956.101667

A média ponderada tem um peso, a média comum o AVG resolve.

 

Ajudou ?

 

 

-- uma tabela fajuta

SELECT estagiario, venda , nota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual

ESTAGIARIO      VENDA       NOTA
---------- ---------- ----------
joao          1234.56          5
joao          6543.21          6
joao          1111.11          7

--a media

 

SELECT estagiario, avg(venda) mvenda, avg(nota) mnota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

ESTAGIARIO     MVENDA      MNOTA
---------- ---------- ----------
joao          2962.96          6

--a media poderada

 

SELECT estagiario, sum(nota*venda)/sum(nota) mpvendanota
FROM
(
SELECT 'joao' estagiario,1234.56 venda, 5 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,6543.21 venda, 6 nota FROM dual
UNION ALL
SELECT 'joao' estagiario,1111.11 venda, 7 nota FROM dual
) virtual
group by estagiario

ESTAGIARIO MPVENDANOTA
---------- -----------
joao       2956.101667

A média ponderada tem um peso, a média comum o AVG resolve.

 

Ajudou ?

 

 

Eu vou testar mas o problema é que eu não tenho como saber exatamente quais são os valores iguais pq a consulta me retorna mais de 33.000 linhas. O problema é agrupar os valores iguais e depois tirar a média ponderada disso. Vou testar sua sugestão e ver se resolve. De qualquer forma, obrigado pela dica. Já é um norte.

[Motta 645]

Uma dica faça um teste com um ou dois casos , assim dá listar o analitico par bater

[ahsc 1]

Uma dica faça um teste com um ou dois casos , assim dá listar o analitico par bater

 

blz. valeu. Vou fazer isso sim

[advaldomesquita 93]

Tem como postar a estrutura das tabelas envolvidas e alguns dados? Eu por exemplo nao tenho acesso a links externos.

 

Ainda acho que o CASE resolve.... :p

[Motta 645]

Como seria o CASE , A.Jr ?

[ahsc 1]

Tem como postar a estrutura das tabelas envolvidas e alguns dados? Eu por exemplo nao tenho acesso a links externos.

 

Ainda acho que o CASE resolve.... :P

Vou disponibilizar dois arquivos xlsx. O primeiro é a estrutura de dados normal. O segundo são os dados que apareceram repetidos e que precisam ser ponderados.

Como eu faço para subir arquivos aqui?

[advaldomesquita 93]

Vou disponibilizar dois arquivos xlsx. O primeiro é a estrutura de dados normal. O segundo são os dados que apareceram repetidos e que precisam ser ponderados.

Como eu faço para subir arquivos aqui?

Aqui nao tem como fazer upload de arquivos nao.

Pode ser exemplos soh... Copy Paste aqui mesmo....

[ahsc 1]

Aqui nao tem como fazer upload de arquivos nao.

Pode ser exemplos soh... Copy Paste aqui mesmo....

 

Tá, blz. Vou por link para estrutura de dados

[ahsc 1]

Aqui nao tem como fazer upload de arquivos nao.

Pode ser exemplos soh... Copy Paste aqui mesmo....

 

A.Jr, esse link mostra como é a estrutura de dados. Só estou exibindo apenas algumas linhas para você ter uma ideia de como é a estrutura de dados. Essa consulta gerou mais de 33.000 linhas.

 

http://prntscr.com/476x8j

 

Agora o resultado da consulta mostrando os dados que estão em "duplicidade" e que preciso ponderar.

 

http://prntscr.com/476xxd

 

Acho que agora dá para visualizar melhor

[advaldomesquita 93]

Agora vem as minhas duvidas:

No post #1 vc diz:

 

Os campos AS, MÊS, VARIEDADE e ESTAGIOPAD tem que ser verificados. Caso sejam iguais, precisaria fazer uma média ponderada dos campos PL%, ATR e FIB com o campo PRODUÇÃO.

Estes campo irão virar um campo único, com a média deles, correto?

No exemplo abaixo, qual o agrupamento de dados e qual os campos que voce precisa que seja feito a media?

 

declare @tabela table 
(USINA VARCHAR(3)
, REGIAO VARCHAR(10)
, SAFRA VARCHAR(5)
, ANO INT
, MES INT
, VARIEDADE VARCHAR(50)
, ESTAGIOPAD VARCHAR(3)
, AREA FLOAT
, PRODUCAO FLOAT
, TCH FLOAT
, POLCANA FLOAT
, ATR FLOAT
, FIBRA FLOAT)
 
INSERT INTO @tabela select 'BTG','GOIAS', '13/14', 2013,5,'RB86 7515', '1Am', 16.0006, 1600.06, 100, 12.18,  120.18, 13.06
INSERT INTO @tabela select 'BTG','GOIAS', '13/14', 2013,5,'RB86 7515', '1Am', 16.0006, 1600.06, 100, 12.18,  120.18, 13.06
 
SELECT * from @tabela
 

@Motta, agora que vi o lance do CASE

Poderia ser chegando os campos que ele precisa para calculo, mas olhando a estrutura dos dados, acho que uma CTE resolveria tambem.

Eu vou esperar ele responder e monto os dois exemplos

 

[]´s

[ahsc 1]

Agora vem as minhas duvidas:

No post #1 você diz:

 

Estes campo irão virar um campo único, com a média deles, correto?

No exemplo abaixo, qual o agrupamento de dados e qual os campos que voce precisa que seja feito a media?

declare @tabela table 
(USINA VARCHAR(3)
, REGIAO VARCHAR(10)
, SAFRA VARCHAR(5)
, ANO INT
, MES INT
, VARIEDADE VARCHAR(50)
, ESTAGIOPAD VARCHAR(3)
, AREA FLOAT
, PRODUCAO FLOAT
, TCH FLOAT
, POLCANA FLOAT
, ATR FLOAT
, FIBRA FLOAT)
 
INSERT INTO @tabela select 'BTG','GOIAS', '13/14', 2013,5,'RB86 7515', '1Am', 16.0006, 1600.06, 100, 12.18,  120.18, 13.06
INSERT INTO @tabela select 'BTG','GOIAS', '13/14', 2013,5,'RB86 7515', '1Am', 16.0006, 1600.06, 100, 12.18,  120.18, 13.06
 
SELECT * from @tabela
 

@Motta, agora que vi o lance do CASE

Poderia ser chegando os campos que ele precisa para calculo, mas olhando a estrutura dos dados, acho que uma CTE resolveria tambem.

Eu vou esperar ele responder e monto os dois exemplos

 

[]´s

 

 

A brincadeira fica dessa forma:

 

Imaginem que eu tenha duas linhas com com safra, mês, variedade e estágio iguais (informação do primeiro link primeiro link)

 

O cálculo que precisarei fazer é o seguinte:

 

somar toda a área (mesma coluna). No caso da planilha, a coluna H

somar toda a produção (mesma coluna). No caso da planilha, a coluna I

E ponderar as colunas K, L e M. O cálculo seria o seguinte

 

=(SOMA((K54*I54)+(K57*I57))/I59)

 

Onde i é a produção, K é o TCH. Como são duas linhas, o K e o I aparecem duas vezes. O valor final da soma tem que ser dividido pelo valor total da produção. Esse procedimento é repetido para as linhas.

 

O resultante dessas duas linhas dará uma terceira linha e essas duas deverão ser excluídas.

[Motta 645]

select SAFRA, MES, VARIEDADE , ESTAGIO ,
       sum(area * producao)  * sum(TCH) / sum(area * producao) tch_ponderado
from TABELA
group by estagiario

??

[ahsc 1]

select SAFRA, MES, VARIEDADE , ESTAGIO ,
       sum(area * producao)  * sum(TCH) / sum(area * producao) tch_ponderado
from TABELA
group by estagiario

??

 

 

 

O maior problema que eu vejo é que não são muitos os dados com problemas. Em mais de 33 mil registros que a consulta gera, apenas 1033 apresentam duplicidade. A questão que eu acabei esquecendo de frisar é que a regra geral para ser feita a ponderação é se safra, variedade e estágiopad forem iguais. Somente nesses casos é que eu precisarei ponderar as linhas que atendem essa situação e após ponderá-la, excluir as linhas duplicadas.

 

Eu não faço a mínima ideia de como se faz isso.

[Motta 645]

Faça um select para os sem duplicidade outro para os com duplicidade e junte por union.

Mas a ponderação de um registro é igual, creio.

[ahsc 1]

E como eu faço esse tipo de select?. O problema é a ponderação mesmo. Não consigo visualizar como eu vou pegar duas linhas e ponderá-las para gerar uma terceira linha e depois que as duas linhas tiverem seu resultado ponderado, elas precisam ser excluídas. Tenho que levar em consideração o seguinte: variedade, safra e estágio tem que estar iguais para que haja ponderação.

 

E essa é a query que uso para trazer todos os dados sem o filtro. Como refiná-la para conseguir resolver o problema?

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, C.DESCRICAO AS NOMEREGIAO, A.* FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO