distinct DISTINCT funcionou para os dados, menos para o cálculo do desvio-padrão.

[Alanderson A Dalmaso 0]

Tenho uma tabela de cadastro de equipamento chamada de taob, e outra tabela com cadastro de eventos de manutenção chamada de tcev.

Meu objetivo é listar por categoria de equipamentos (dimensão do indicador) as quantidades de eventos por cada dia num determinado mês, obtendo sua média e desvio-padrão nos dias.

Nem todo dia haverá um evento de manutenção em um determinado equipamento, logo preciso que apareça zero neste dia para fins de cálculo do desvio-padrão.

Como não gostaria de criar uma tabela "física" no banco para não ficar uma bagunça quando mudar os períodos (várias consultas), fiz uma tabela temporária na memória, desta forma:

 

CREATE TEMPORARY TABLE periodo (dia1 date, primary key (dia1)) ENGINE MEMORY;

 

Depois inseri os dados, para ter a lista dia a dia.

 

INSERT INTO periodo (dia1) VALUES ('2017-11-01');
INSERT INTO periodo (dia1) VALUES ('2017-11-02');
INSERT INTO periodo (dia1) VALUES ('2017-11-03');
INSERT INTO periodo (dia1) VALUES ('2017-11-04');
INSERT INTO periodo (dia1) VALUES ('2017-11-05');
INSERT INTO periodo (dia1) VALUES ('2017-11-06');
INSERT INTO periodo (dia1) VALUES ('2017-11-07');
INSERT INTO periodo (dia1) VALUES ('2017-11-08');
INSERT INTO periodo (dia1) VALUES ('2017-11-09');
INSERT INTO periodo (dia1) VALUES ('2017-11-10');
INSERT INTO periodo (dia1) VALUES ('2017-11-11');
INSERT INTO periodo (dia1) VALUES ('2017-11-12');
INSERT INTO periodo (dia1) VALUES ('2017-11-13');
INSERT INTO periodo (dia1) VALUES ('2017-11-14');
INSERT INTO periodo (dia1) VALUES ('2017-11-15');
INSERT INTO periodo (dia1) VALUES ('2017-11-16');
INSERT INTO periodo (dia1) VALUES ('2017-11-17');
INSERT INTO periodo (dia1) VALUES ('2017-11-18');
INSERT INTO periodo (dia1) VALUES ('2017-11-19');
INSERT INTO periodo (dia1) VALUES ('2017-11-20');
INSERT INTO periodo (dia1) VALUES ('2017-11-21');
INSERT INTO periodo (dia1) VALUES ('2017-11-22');
INSERT INTO periodo (dia1) VALUES ('2017-11-23');
INSERT INTO periodo (dia1) VALUES ('2017-11-24');
INSERT INTO periodo (dia1) VALUES ('2017-11-25');
INSERT INTO periodo (dia1) VALUES ('2017-11-26');
INSERT INTO periodo (dia1) VALUES ('2017-11-27');
INSERT INTO periodo (dia1) VALUES ('2017-11-28');
INSERT INTO periodo (dia1) VALUES ('2017-11-29');
INSERT INTO periodo (dia1) VALUES ('2017-11-30');

 

Agora que tenho minha tabela temporária, fiz uma combinação dos dados através do from.

 

(...) from (periodo, taob)

 

Quando se usa esta notação no lugar do inner ou left ou right join, haverá uma combinação dos dados entre as tabela periodo e taob, que é o desejado para ter todos os dias por equipamento.

 

Alinhando essas premissas, segue o problema:

-> Fiz uma união entre esta combinação com os dados dos eventos, e usei um DISTINCT para não ter repetição dos dados, que não funcionou muito bem. Tive que colocar um group by no final para funcionar. Quando fui obter o desvio-padrão e a média percebi que a fórmula estava considerando todos os dados (inclusive os retirados pelo DISTINCT+GROUP BY), eu preciso que calcule somente os visíveis.

 

Segue código:

 

SELECT DISTINCT Dimensao, dt1, SUM(Qtde), STDDEV_SAMP(Qtde) as desvPad, AVG(Qtde) as media
FROM (
    (
        select taob.CXN3 as Dimensao, Date(tcev.CXDE) as dt1, count(tcev.PIEV) as Qtde
        from taob left join tcev on (taob.PIOB = tcev.SIOB)
        where tcev.SIGR=3 and tcev.CXDE between '2017-11-01' and '2017-11-30'
        group by taob.CXN3, Date(tcev.CXDE)
    )
    UNION DISTINCT
    (
        select taob.CXN3 as Dimensao, periodo.dia1 as dt1, 0 as Qtde from (periodo, taob)
        where taob.SIGR=3
    )
    order by Dimensao, dt1, Qtde desc
) as lista
GROUP BY Dimensao

em anexo o resultado da consulta.

 

Verificando os dados no excel (calculando na "mão") o resultado do desvPad (AMOSTRAL) a dimensão DL deveria ser igual a 2,9752 e se eu considerar os zeros que o DISTINCT "eliminou" a conta (na "mão") bate com o resultado do sql com o valor de 2.8045.

 

Alguém consegue me dar uma pista do que pode estar acontecendo?

 

Ou é uma coisa besta ou é um bom problema. Como sempre! rs!

 

Algumas considerações:

taob.CXN3 = Dimensão do equipamento ou indicador.

tcev.CXDE = Data do evento de manutenção.

tcev.PIEV = Chave primária da tabela de eventos de manutenção.

taob.PIOB = Chave primária da tabela de equipamentos ou dimensao do indicador.

tcev.SIGR = Grupo de cliente = 3

 

Att, Alanderson A Dalmaso

 

[Alanderson A Dalmaso 0]

Pessoal,

 

Consegui resolver executando o agrupamento por partes (um dentro do outro).

 

ficou assim:

 

SELECT Dimensao, dt1, Sum(Qtde), STDDEV_SAMP(Qtde) as desvPad, AVG(Qtde) as media, Sum(QtdeRef) as valorX, 
if (STDDEV_SAMP(Qtde)<>0, ((Sum(QtdeRef) - AVG(Qtde)) / (STDDEV_SAMP(Qtde))), 0) as  scoreZ
FROM
(
	SELECT DISTINCT Dimensao, dt1, Qtde, QtdeRef
	FROM (
		(
			select taob.CXN3 as Dimensao, Date(tcev.CXDE) as dt1, count(tcev.PIEV) as Qtde,
				if (Date(tcev.CXDE)=Date(now()), 
					(
						select count(tcev.PIEV) as QtdeRef from (
						taob left join tcev on (taob.PIOB = tcev.SIOB)) 
						where Date(tcev.CXDE)=Date(now()) and taob.CXN3 = Dimensao 
					), 0
				) as QtdeRef
			from taob left join tcev on (taob.PIOB = tcev.SIOB)
			where tcev.SIGR=3 and tcev.CXDE between DATE_ADD(Date(now()), INTERVAL -30 DAY) and now()
			group by taob.CXN3, Date(tcev.CXDE)
		)
		UNION
		(
			select taob.CXN3 as Dimensao, periodo.dia1 as dt1, 0 as Qtde, 0 as QtdeRef from (periodo, taob)
			where taob.SIGR=3
		)
		order by Dimensao, dt1, Qtde desc, QtdeRef desc
	) as lista
	GROUP BY Dimensao, dt1
) as lista2
GROUP BY Dimensao

 

Criei um SELECT externo ao SELECT do DISTINCT e fiz decair o GROUP BY,

de:     GROUP BY Dimensao, dt1             (dois parametros)

para:  GROUP BY Dimensao                    (um parametro)

 

Agora o desvio-padrão foi calculado corretamente.

Observação algumas alterações desejadas foram feitas no código como a questão de data fixa para data móvel.

 

Att, Alanderson A Dalmaso

[Motta 645]

Eu faria um SUM na linha

SELECT DISTINCT Dimensao, dt1, Qtde, QtdeRef