Ir para conteúdo

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

Alanderson A Dalmaso

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

Recommended Posts

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

 

Capturar.PNG

Compartilhar este post


Link para o post
Compartilhar em outros sites

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

Compartilhar este post


Link para o post
Compartilhar em outros sites

Eu faria um SUM na linha

SELECT DISTINCT Dimensao, dt1, Qtde, QtdeRef

Compartilhar este post


Link para o post
Compartilhar em outros sites

  • Conteúdo Similar

    • Por ramonjba
      Bom dia! Pessoal, estou muito precisando da ajuda dos Srs. Tenho uma tabela, onde a cada minuto, meu script insere dados referente a consulta SNMP de determinado equipamento. Tenho uma pagina, onde os dados que a consulta coletou diferente do dado atual, é mostrado na tabela. O que acontece é o seguinte, estou utilizando o GROUP BY para selecionar apenas os dados diferentes da coluna 'canalEquipamento', mas, o problema é que, se eu estiver com o Canal registrado na tabela em 5540, realizar a alteração para o 5500, e depois voltar para o 5540, minha consulta não retorna o dado duplicado. Não posso simplesmente mostrar todas as consultas na tabela, pois como eu disse, a inserção de dados na tabela é realizada a cada minuto, dessa forma, iria ter vários dados repetidos e uma consulta enorme. Então, o que eu preciso fazer, é que: a consulta retorne os dados , que o último não seja repetido, por exemplo: Tenho do registro 1 até 50, a coluna Canal está registrada em 5500, no registro 51 até o 500, o canal mudou para 5540, e do 501 até o 700, o canal voltou para o 5500. O que eu precisaria que a consulta retornasse, seria por exemplo:

      Consulta 1 - Canal 5500
      Consulta 51 - Canal 5540
      Consulta 501 - Canal 5500
       
      Segue a imagem do exemplo do problema atual, de como a query está retornando no momento (ela está listando apenas o primeiro registro do canal, se repetir o canal, ele não aparece mais).
      QUERY: SELECT ip_equipamento, canalEquipamento, lastSNMP FROM equipamentoSNMP WHERE idEquipamento='317' GROUP BY canalEquipamento ORDER BY idConsulta DESC
       

    • Por tamabenetti
      AJUDAAAA
      Pessoal eu preciso da quantidade total de itens diferentes vendidos por cliente para cada vendedor.
       
      Eu tenho o seguinte:
      Vendedor |                     CLIENTE                                  |  PRODUTO                                 |  data_VENDA
      JOAO          |                    MERCADO  SORRISO            |                   PRESUNTO              |  01/07/2019
      JOAO          |                   PADARIA SONHO                     |                    PRESUNTO             | 03/07/2019
      JOAO          |                    SUPERCENTER                       |                      PRESUNTO             | 05/07/2019
      JOAO          |                    SUPERCENTER                       |                      PRESUNTO             |  10/07/2019
      LUIZ          |                    CONFEITARIA A                         |                     AÇUCAR                   |  15/07/2019
       
      PRECISO DESSE RESULTADO:
      Vendedor | Total clientes vendidos | total produtos diferentes vendidos POR CLIENTE
      JOAO          |                  3                        |                    3 *
      LUIZ             |                 1                       |                      1
       
      *O total é 3 porque eu preciso considerar a venda do produto por cliente, e não somente por vendedor.
       
      Quando eu utilizo o group by ele considera apenas o total de produtos vendidos por VENDEDOR, 
      e não considera a venda para clientes diferentes.
      SELECT distinct vendedor, count(distinct cliente), count(distinct produto) 
      FROM FATO_VENDAS a
      GROUP BY vendedor
       
      Por favor me ajudem!!!
    • Por Jonatas Provido
      Pessoal estou mexendo em um relatório porem quando ele me retorna ele me traz ainda em duplicidade.
      veja na imagem 
      se aguem puder me dar uma luz. 
       
      SELECT DISTINCT ITE.CODPROD,
             ITE.SEQUENCIA,
             PRO.DESCRPROD,
             ITE.AD_COMPLEMENTO,
             PAP.CODPROPARC,
             ITE.CONTROLE,
             ITE.USOPROD,
              (CASE
                WHEN VOA.CODPROD IS NULL THEN ITE.QTDNEG
                WHEN VOA.DIVIDEMULTIPLICA = 'D' THEN ITE.QTDNEG * VOA.QUANTIDADE
                ELSE ITE.QTDNEG / VOA.QUANTIDADE
              END) AS QTDNEG,
              (CASE
                WHEN VOA.CODPROD IS NULL THEN ITE.VLRUNIT
                WHEN VOA.DIVIDEMULTIPLICA = 'D' THEN ITE.VLRUNIT / VOA.QUANTIDADE
                ELSE ITE.VLRUNIT * VOA.QUANTIDADE
              END) AS VLRUNIT,
            ITE.CODVOL,
            ITE.VLRDESC,
            ITE.QTDNEG * ITE.VLRUNIT AS TOTALITEM,
            ITE.VLRTOT - ITE.VLRDESC AS TOTLIQ,
            (CASE WHEN ITE.DTINICIO IS NULL THEN
            CAB.DTPREVENT ELSE ITE.DTINICIO END) AS DTPREV,
            USU.NOMEUSU,
            ITE.VLRIPI,
            (ITE.VLRIPI / (ITE.VLRTOT - ITE.VLRDESC)) * 100 AS PERCIPI
      FROM TGFITE ITE
           INNER JOIN TGFCAB CAB ON (CAB.NUNOTA = ITE.NUNOTA)
           INNER JOIN TGFPRO PRO ON(ITE.CODPROD = PRO.CODPROD)
           LEFT JOIN TSIUSU USU ON (USU.CODUSU = ITE.AD_CODUSU)
           LEFT JOIN TGFPAP PAP
           ON ITE.CODPROD = PAP.CODPROD
           AND CAB.CODPARC = PAP.CODPARC
           AND ITE.CODVOL = PAP.UNIDADEPARC
           LEFT JOIN TGFVOA VOA ON(
           VOA.CODPROD = ITE.CODPROD AND
           VOA.CODVOL = ITE.CODVOL AND
             ((ITE.CONTROLE IS NULL AND VOA.CONTROLE = ' ')
             OR (ITE.CONTROLE IS NOT NULL AND ITE.CONTROLE = VOA.CONTROLE))
                             )
            WHERE  CAB.NUNOTA = 55854 AND  ITE.SEQUENCIA > 0
      ORDER BY ITE.SEQUENCIA

    • Por brunoogm
      Pessoal tenho uma tabela no php cujo resultado do select vindo do bd vem no seguinte formato:
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Bacon Cheddar                          1          60.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Chicken Club Sandwich            1          45.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Philly Cheese Steak                   1          50.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Smoke House                             1          55.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Stream Miller                              1          35.00
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
      E eu gostaria de fazer um select para que nao repita os dados iguais e que a tabela viesse assim:
       
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                      Bacon Cheddar                          1           60.00
                                                                                                                                                      Chicken Club Sandwich           1            45.00
      26    46        15/05/2019 13:57:25         Dinheiro                  Pendente                           Philly Cheese Steak                   1            50.00
                                                                                                                                                      Smoke House                             1            55.00
                                                                                                                                                      Stream Miller                              1            35.00
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
      Alguem pode me ajudar, qual seria a melhor forma de fazer ?

      Essa tabela é gerada com um while no php entao se alguem saber uma outra maneira de montar isso aceito dicas.
       
      php: 
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      echo "<tr role='row' class='odd'>";
                                                      echo "<td class='sorting_1'>" .$resultado['CodigoComanda']. "</td>";
                                                      echo "<td>" .$resultado['Nome']."</td>";
                                                      echo "<td>" .$resultado['Quantidade']."</td>";
                                                      echo "<td>" .$resultado['Preco']."</td>";
                                                      echo "<td>" .$resultado['Metodos']."</td>";
                                                      echo "<td>" .$resultado['DataPedido']."</td>";
                                                      echo "<td>" .$resultado['Situacao']."</td>";
      echo "</tr>";
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
      vlw pessoal
       
    • Por brunoogm
      Pessoal tenho uma tabela no php cujo resultado do select vindo do bd vem no seguinte formato:
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Bacon Cheddar                          160.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Chicken Club Sandwich            145.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Philly Cheese Steak                   150.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Smoke House                             155.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Stream Miller                              135.00
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
      E eu gostaria de fazer um select para que nao repita os dados iguais e que a tabela viesse assim:
       
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Bacon Cheddar                          160.00
                                                                                                                                                      Chicken Club Sandwich            145.00
                                                                                                                                                      Philly Cheese Steak                   150.00
       Smoke House                             155.00
      26       46        15/05/2019 13:57:25         Dinheiro                  Pendente                        Stream Miller                              135.00
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.