Query com dados ponderados.

ahsc · Julho 30, 2014

É verdade, vamos por partes....

Fui executar o código e deu esse erro

Tirando a primeira linha

if object_definition('') is not null

E executando o código normalmente ele funcionou. Mas só me retornou 215 registros. A consulta em si, tem mais de 33 mil linhas, o código pode trazer o restante da consulta?

advaldomesquita · Julho 30, 2014

É verdade, vamos por partes....

Fui executar o código e deu esse erro

http://prntscr.com/47th2l

Tirando a primeira linha
if object_definition('') is not null
E executando o código normalmente ele funcionou. Mas só me retornou 215 registros. A consulta em si, tem mais de 33 mil linhas, o código pode trazer o restante da consulta?

Eu arrumei a primeira linha no meu post, depois de uma olhada.

Faz um count na temporaria e veja qtos registros tem

select count(*) from #tmp

ahsc · Julho 30, 2014

Eu arrumei a primeira linha no meu post, depois de uma olhada.

Faz um count na temporaria e veja qtos registros tem
select count(*) from #tmp

Na verdade acho que achei o erro. O problema está no Divided by zero. Olha o erro

http://prntscr.com/47tqan

e ele retorna

http://prntscr.com/47tqjb

Ou seja, ele vai até o 215 registro e retorna erro pq encontra algum valor zerado.

advaldomesquita · Julho 30, 2014

Como o @Motta falou, usando um CASE para tratar a divisao por zero:

if object_definition('tempdb..#tmp') is not null
drop table #tmp
 
SELECT B.CODIGO
, B.NOMEFANT
, B.RAZSOC
, B.CODREGIAO
, C.DESCRICAO AS NOMEREGIAO
, A.CODUSINA
, A.SAFRA
, A.ANO
, A.MES
, A.ITEM
, A.CODVARIEDADE AS VARIEDADE
, A.CODESTPAD AS ESTAGIO
, A.AREA
, A.PRODUCAO
, A.TCH
, A.PCC AS POL
, A.ATR
, A.FIBRA
INTO #tmp 
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO
 
select count(*) from #tmp

SELECT sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, case when (sum(producao) > 0) then (((sum(QtdePol)) / (sum(producao))))
else '0' end POL
, case when (sum(producao) > 0) then (((sum(QtdeATR)) / (sum(producao))))
else '0' end ATR
, case when (sum(producao) > 0) then (((sum(QtdeFibra)) / (sum(producao))))
else '0' end ATR
 
--  , ( (sum(QtdePol)) / (sum(producao))) as POL
-- , ( (sum(QtdeATR)) / (sum(producao))) as ATR
-- , ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
FROM 
(
SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
 
FROM (
SELECT a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM #tmp a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
) b
) c
GROUP BY c.safra
, c.variedade
, c.estagio

ahsc · Julho 30, 2014

Como o @Motta falou, usando um CASE para tratar a divisao por zero:

if object_definition('tempdb..#tmp') is not null
drop table #tmp
 
SELECT B.CODIGO
, B.NOMEFANT
, B.RAZSOC
, B.CODREGIAO
, C.DESCRICAO AS NOMEREGIAO
, A.CODUSINA
, A.SAFRA
, A.ANO
, A.MES
, A.ITEM
, A.CODVARIEDADE AS VARIEDADE
, A.CODESTPAD AS ESTAGIO
, A.AREA
, A.PRODUCAO
, A.TCH
, A.PCC AS POL
, A.ATR
, A.FIBRA
INTO #tmp 
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO
 
select count(*) from #tmp

SELECT sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, case when (sum(producao) > 0) then (((sum(QtdePol)) / (sum(producao))))
else '0' end POL
, case when (sum(producao) > 0) then (((sum(QtdeATR)) / (sum(producao))))
else '0' end ATR
, case when (sum(producao) > 0) then (((sum(QtdeFibra)) / (sum(producao))))
else '0' end ATR
 
--  , ( (sum(QtdePol)) / (sum(producao))) as POL
-- , ( (sum(QtdeATR)) / (sum(producao))) as ATR
-- , ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
FROM 
(
SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
 
FROM (
SELECT a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM #tmp a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
) b
) c
GROUP BY c.safra
, c.variedade
, c.estagio

Tá quase.

As linhas afetadas na consulta são: 33488

Quando eu tiro essa linha para executar a query eu obtenho: http://prntscr.com/47tyul

Se eu deixar a primeira linha, dá esse erro: http://prntscr.com/47tz4e

A base de dados está em produção, não sei se isso tem algo relacionado ao erro. Mas serve de informação

advaldomesquita · Julho 30, 2014

Falha minha:

A primeira linha:

if object_id ('tempdb..#tmp') is not null
drop table #tmp

Esta linha serve para checar se existe a tabela #tmp na sua sessão.

ahsc · Julho 30, 2014

Falha minha:

A primeira linha:
if object_id ('tempdb..#tmp') is not null
drop table #tmp
Esta linha serve para checar se existe a tabela #tmp na sua sessão.

Funcionou sem erro. Só tô achando estranho retornar apenas 1788 linhas. São 33488 no total e pelo que vi apenas 1033 estão em duplicidade. Era pra voltar um pouco mais de linha. Vc tem alguma ideia do que seja?

advaldomesquita · Julho 30, 2014

faça este select

select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
from #tmp a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio

Pois em tese, sao estes caras que estao sendo utilizados nos calculos.

ahsc · Julho 30, 2014

Eu substitui essa área: http://prntscr.com/47uxsl

Por esta que você me passou e dá erro pois nessa query

SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra

Ficam faltando os campos b.Sum_Pol, b.Sum_ATR e b.Sum_Fibra

O resultado que preciso dessa query é esse: http://prntscr.com/47v06j

Mas com a query que temos agora está assim: http://prntscr.com/47v0ob

Será que dá pra ficar dessa forma?

[]'s

ahsc · Julho 30, 2014

Tá dureza @A.Jr não consigo resolver isso. Já tentei de várias formas ou dá erro ou só trás as 1788 linhas, vc tem alguma ideia de como eu posso resolver isso?

advaldomesquita · Julho 30, 2014

Assim,

ou o erro esta na #tmp ou esta nos calculos.

O count na #tmp trouxe todo mundo? Ou seja, tem mais de 33 mil linhas lah?

E um count na #tmp agrupando os campos que precisa, trazem quantos registros?

Estou sem acesso aos links que passou, pois estou migrando algumas bases e nao posso acessar.

ahsc · Julho 30, 2014

O primeiro cout trouxe todos (33488). Quando eu tiro o count e rodo a query sem ele, ou seja, com os dados agrupados, eu consigo no máximo 1788 registros.

ahsc · Julho 31, 2014

Eu até tentei mexer nessa consulta para ver se resolvia. Mas eu não consigo adicionar mais campos a ela. A consulta 3

SELECT sum(c.Area) AS ÁREA
, sum(c.producao)  AS PRODUÇÃO
, c.safra AS SAFRA
, c.variedade AS VARIEDADE
, c.estagio AS ESTÁGIO
, case when (sum(producao) > 0) then (((sum(QtdePol)) / (sum(producao))))
else '0' end POL
, case when (sum(producao) > 0) then (((sum(QtdeATR)) / (sum(producao))))
else '0' end ATR
, case when (sum(producao) > 0) then (((sum(QtdeFibra)) / (sum(producao))))
else '0' end FIBRA

É a que "mostra" o resultado da consulta mas eu não consigo adicionar nenhum campo da consulta principal que é

SELECT B.CODIGO
, B.NOMEFANT
, B.RAZSOC
, B.CODREGIAO
, C.DESCRICAO AS NOMEREGIAO
, A.CODUSINA
, A.SAFRA
, A.ANO
, A.MES
, A.ITEM
, A.CODVARIEDADE AS VARIEDADE
, A.CODESTPAD AS ESTAGIO
, A.AREA
, A.PRODUCAO
, A.TCH
, A.PCC AS POL
, A.ATR
, A.FIBRA
INTO #tmp 
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

Como eu faço isso?

advaldomesquita · Agosto 7, 2014

trezendo mil emails depois, chegamos a um consenso sobre a sintaxe.

Segue para quem precisar fazer algum calculo por linha, agregando por determinados campos

SELECT c.USINA
, c.REGIÃO
, c.ANO
, c.MÊS
, SUM(c.ÁREA) as Soma_Area
, SUM(c.PRODUÇÃO)  as Soma_Producao
, c.safra
, c.variedade
, c.ESTÁGIOPAD 
 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdePol)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as POL
 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdeATR)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as ATR
 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdeFibra)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as Fibra

, SUM(c.Sum_TCH) as TCH




from 
(
SELECT b.ÁREA
, b.PRODUÇÃO
, b.Safra
, b.Variedade
, b.ESTÁGIOPAD
, (b.Sum_Pol * b.PRODUÇÃO) as QtdePol
, (b.Sum_ATR * b.PRODUÇÃO) as QtdeATR
, (b.Sum_Fibra * b.PRODUÇÃO) as QtdeFibra
, Sum_TCH
, b.Usina
, b.REGIÃO
, b.ANO
, b.MÊS
-- into #temp
FROM (
select a.Área
, a.PRODUÇÃO
, a.Safra
, a.Variedade
, a.ESTÁGIOPAD
, ISNULL(SUM(a.[POL%CANA]),0) AS Sum_Pol
, ISNULL(SUM(a.ATR),0) AS Sum_ATR
, ISNULL(SUM(a.Fibra),0) AS Sum_Fibra
, ISNULL(SUM(a.TCH),0) as Sum_TCH
, a.USINA
, a.REGIÃO
, a.ANO
, a.MÊS
FROM TABELA_VIEW a
GROUP BY a.ÁREA
, a.PRODUÇÃO
, a.Safra
, a.Variedade
, a.ESTÁGIOPAD
, a.USINA
, a.REGIÃO
, a.MÊS
, a.ANO
) b
) c
GROUP BY c.safra
, c.variedade
, c.ESTÁGIOPAD 
, c.USINA
, c.REGIÃO
, c.ANO
, c.MÊS

[]´s B)

ahsc · Janeiro 30, 2015

Galera, estou com uma dúvida. Montei uma query com a ajuda do moderador A.Jr. A query ficou show de bola mas ao alterar o parâmetro da consulta eu obtive um erro. A consulta é feita por safra, ou seja, 11/12, 12/13, ou seja safras dos ano 11 final no ano 12, safra do ano 12 final no ano 13.

Quando eu utilizo safras começando com valor par, 10/11, 12/13, 14/15..., a query roda perfeitamente mas se eu rodar queries com safra 11/12, 13/14, 15/16 resulta erro: Divide by zero error encountered. Não faço o motivo pelo qual isso está ocorrendo.

Segue query

if OBJECT_ID('tempdb..#tmp') is not null
drop table #tmp

SELECT B.CODIGO
	, B.NOMEFANT
	, B.RAZSOC
	, B.CODREGIAO
	, C.DESCRICAO AS NOMEREGIAO
	, A.CODUSINA
	, A.SAFRA
	, A.ANO
	, A.MES
	, A.ITEM
	, A.CODVARIEDADE AS VARIEDADE
	, A.CODESTPAD AS ESTAGIO
	, A.AREA
	, A.PRODUCAO
	, A.TCH
	, A.PCC AS POL
	, A.ATR
	, A.FIBRA
INTO #tmp 
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '11/12'
	AND A.CODUSINA = B.CODIGO
	AND C.CODIGO = B.CODREGIAO

SELECT z.USINA
	, z.REGIÃO
	, z.RAZSOC
	, z.NOMEFANT
	, z.ANO
	, z.MÊS
	, z.Soma_Area as [ÁREA (HA)]
	, z.Soma_Producao AS [PRODUÇÃO (t)]
	, z.safra as [SAFRA]
	, z.variedade AS [VARIEDADE]
	, z.variedade AS [VARIEDADE1]
	, z.ESTÁGIOPAD as [ESTÁGIO]
	, z.TCH AS [TCH]
  , z.POL as [POL % CANA]
	, z.ATR AS [ATR]
	, z.Fibra AS [FIBRA]
	
from (

		SELECT c.USINA
		, c.REGIÃO
		, c.RAZSOC
		, c.NOMEFANT
		, c.ANO
		, c.MÊS
		, SUM(c.ÁREA) as Soma_Area
		, SUM(c.PRODUÇÃO)  as Soma_Producao
		, c.safra
		, c.variedade
		, c.ESTÁGIOPAD 
		 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdePol)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as POL
		 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdeATR)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as ATR
		 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(QtdeFibra)) / (SUM(c.PRODUÇÃO))) ELSE '0' END as Fibra
		 , CASE WHEN (SUM(c.PRODUÇÃO)) > 0 THEN ( (SUM(c.PRODUÇÃO)) / (SUM(c.ÁREA))) ELSE '0' END as TCH
		FROM 
		(
			SELECT b.ÁREA
			, b.PRODUÇÃO
			, b.Safra
			, b.Variedade
			, b.ESTÁGIOPAD
			, (b.Sum_Pol * b.PRODUÇÃO) as QtdePol
			, (b.Sum_ATR * b.PRODUÇÃO) as QtdeATR
			, (b.Sum_Fibra * b.PRODUÇÃO) as QtdeFibra
			, Sum_TCH
			, b.Usina
			, b.REGIÃO
			, b.ANO
			, b.MÊS
			, b.NOMEFANT
			, b.RAZSOC
			FROM (
					SELECT a.AREA as  Área --a.Área
					, a.PRODUCAO AS PRODUÇÃO -- a.PRODUÇÃO
					, a.Safra
					, a.Variedade
					, a.ESTAGIO as ESTÁGIOPAD -- , a.ESTÁGIOPAD
					, ISNULL(SUM(a.POL),0) AS Sum_Pol --, ISNULL(SUM(a.[POL%CANA]),0) AS Sum_Pol
					, ISNULL(SUM(a.ATR),0) AS Sum_ATR
					, ISNULL(SUM(a.Fibra),0) AS Sum_Fibra
					, ISNULL(SUM(a.TCH),0) as Sum_TCH
					, a.CODUSINA as USINA --a.USINA
					, a.NOMEREGIAO as REGIÃO--, a.REGIÃO
					, a.ANO
					, a.MES as MÊS -- a.MÊS
					, a.NOMEFANT
					, a.RAZSOC
					FROM #tmp a -- DADVAR a
					GROUP BY a.AREA
					, a.PRODUCAO
					, a.Safra
					, a.Variedade
					, a.ESTAGIO
					, a.CODUSINA
					, a.NOMEREGIAO
					, a.MES
					, a.ANO
					,a.NOMEFANT
					, a.RAZSOC
			) b
		) c
		GROUP BY c.safra
			, c.variedade
			, c.ESTÁGIOPAD 
			, c.USINA
			, c.REGIÃO
			, c.ANO
			, c.MÊS
			, c.NOMEFANT
			, c.RAZSOC
) z

where z.POL > 0 and 
	 z.ATR > 0 and 
	 z.Fibra > 0 and 
	 z.TCH > 0
order by z.USINA, z.MÊS asc

Daí o editor sql que uso (TOAD SQL) retorna o erro na linha 28. Alguém tem ideia de como eu posso corrigir o problema?

Valeu

Arquivado

Query com dados ponderados.

Recommended Posts

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

ahsc 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Sistema não funciona corretamente

Fazer busca no Banco de dados usando vários critério

Ler campos com caracteres especiais no xml

Fóruns

Tempo Real

Informação importante