none
Select de dados em datas diferentes RRS feed

  • Pergunta

  • Pessoal tenho uma tabela com a seguinte estrutura:

    CREATE TABLE IF NOT EXISTS `leituras` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `horario` datetime DEFAULT CURRENT_TIMESTAMP,
      `leitura` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)

    Ela me gera a seguinte visualização:

    Preciso fazer um select que me retorne como resultado dois campos calculados da seguinte forma:

    consumo_dia = ultima_leitura_do_dia - primeira_leitura_do_dia

    Isso em consigo fazer.

    Mas o seguinte campo não achei uma forma de fazer no meso select.

    consumo_noite = primeira_leitura_do_dia - ultima_leitura_de_um_dia_anterior


    Como seria o select para tal? Não conseguir criar nada.


    segunda-feira, 9 de novembro de 2020 17:00

Respostas

  • Não entendi muito bem o calculo do consumo médio. Segue uma sugestão inicial acrescentando uma CTE para não repetir os calculos:

    WITH 
      CTE_Agrup AS
      (
        SELECT
        	filial,
        	CAST(horario AS DATE) AS horario,
        	MIN(leitura) AS primeira_leitura,
            MAX(leitura) AS ultima_leitura
        FROM leituras
        GROUP BY
        	filial,
        	CAST(horario AS DATE)
      ),
    
      CTE_Calc as
      (
        SELECT
            *,
            ultima_leitura - primeira_leitura AS consumo_dia,
            LEAD(primeira_leitura, 1) OVER(PARTITION BY filial ORDER BY horario) - ultima_leitura AS consumo_noite
        FROM CTE_Agrup
      )
    
    select
        *,
        consumo_dia + consumo_noite as consumo_total,
        (consumo_dia + consumo_noite) / 2 as consumo_medio
    from CTE_Calc

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 10 de novembro de 2020 18:07

Todas as Respostas

  • Boa tarde,

    Qual versão do SQL Server você está utilizando?


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 9 de novembro de 2020 17:56
  • Boa tarde,

    Qual versão do SQL Server você está utilizando?


    Assinatura: http://www.imoveisemexposicao.com.br

    Tenho a opção do 2017 e 2019 mas será realizado na versão 2017.
    segunda-feira, 9 de novembro de 2020 18:07
  • Sgue uma sugestão para testes utilizando a função Lag para obter um valor da linha anterior:

    with CTE_Agrup as
    (
      select
        filial,
        cast(horario as date) as horario,
        min(leitura) as primeira_leitura,
        max(leitura) as ultima_leitura
      from leituras
      group by
        filial,
        cast(horario as date)
    )
    
    select 
      *,
      ultima_leitura - primeira_leitura as consumo_dia,
      primeira_leitura - 
      lag(ultima_leitura, 1) 
        over(partition by filial order by horario) as consumo_noite
    from CTE_Agrup

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 9 de novembro de 2020 18:27
  • @gapimex usei a sua sugestão e deu certo, foi necessário apenas mudar a função e a ordem dos campo no cálculo.

    Troquei o código abaixo:

    primeira_leitura - lag(ultima_leitura, 1) over(partition by filial order by horario) as consumo_noite

    Por:

    LEAD(primeira_leitura, 1) OVER(PARTITION BY filial ORDER BY horario) - ultima_leitura AS consumo_noite

    Ficando por completo da seguinte forma:

    WITH CTE_Agrup AS (
        SELECT
        	filial,
        	CAST(horario AS DATE) AS horario,
        	MIN(leitura) AS primeira_leitura,
            MAX(leitura) AS ultima_leitura
        FROM leituras
        GROUP BY
        	filial,
        	CAST(horario AS DATE)
    )
    SELECT
    	*,
        ultima_leitura - primeira_leitura AS consumo_dia,
        LEAD(primeira_leitura, 1) OVER(PARTITION BY filial ORDER BY horario) - ultima_leitura AS consumo_noite
    FROM CTE_Agrup

    Deu certinho, porém preciso adicionar mais dois campos calculados no select:

    consumo_dia + consumo_noite AS consumo_total

    AVG(consumo_dia + consumo_noite) AS consumo_medio

    Mas em todo lugar que uso as instruções não dá certo.

    Pode dar uma orientação nesse sentido?

    terça-feira, 10 de novembro de 2020 17:17
  • Não entendi muito bem o calculo do consumo médio. Segue uma sugestão inicial acrescentando uma CTE para não repetir os calculos:

    WITH 
      CTE_Agrup AS
      (
        SELECT
        	filial,
        	CAST(horario AS DATE) AS horario,
        	MIN(leitura) AS primeira_leitura,
            MAX(leitura) AS ultima_leitura
        FROM leituras
        GROUP BY
        	filial,
        	CAST(horario AS DATE)
      ),
    
      CTE_Calc as
      (
        SELECT
            *,
            ultima_leitura - primeira_leitura AS consumo_dia,
            LEAD(primeira_leitura, 1) OVER(PARTITION BY filial ORDER BY horario) - ultima_leitura AS consumo_noite
        FROM CTE_Agrup
      )
    
    select
        *,
        consumo_dia + consumo_noite as consumo_total,
        (consumo_dia + consumo_noite) / 2 as consumo_medio
    from CTE_Calc

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 10 de novembro de 2020 18:07
  • Deu certo, mas pensei que teria uma forma de incluir o código abaixo:

    select
        *,
        consumo_dia + consumo_noite as consumo_total,
        (consumo_dia + consumo_noite) / 2 as consumo_medio
    from CTE_Calc

    Que foi o que tentei fazer, porém sem sucesso, na primeira versão do código que vc mandou.

    Teria como?

    quarta-feira, 11 de novembro de 2020 03:00
  • Não é possível referenciar uma coluna pelo seu Alias dentro do mesmo Select que cria esse Alias, então para fazer sem adicionar essa CTE_Calc, seria necessário repetir o calculo, colocando por exemplo ultima_leitura - primeira_leitura onde está consumo_dia, e etc.

    Experimente fazer uns testes, acredito que você não terá prejuízo com essa CTE adicional e a consulta ficará mais legível e fácil de manter.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 11 de novembro de 2020 12:30
  • @gapimex se eu desejar acrescentar mais um cálculo para saber por exemplo quantos litros de água foram gastos por cabeça, praticamente os dados estão no select até o momento, seria preciso acrescentar o seguinte código na primeira parte:

    WITH 
      CTE_Agrup AS
      (
        SELECT
        	filial,
        	CAST(horario AS DATE) AS horario,
        	MIN(leitura) AS primeira_leitura,
            MAX(leitura) AS ultima_leitura,
            (a.bovinos + a.suinos + a.matrizes) AS cabecas,
        FROM leituras
        INNER JOIN abates AS a ON DATE_FORMAT(l.horario, 
        '%d/%m/%Y') = DATE_FORMAT(a.data, '%d/%m/%Y')
        GROUP BY
        	filial,
        	CAST(horario AS DATE)
      ),

    Basta incluir mais uma CTE ao final tipo:

    SELECT *, cabecas / consumo_total FROM CTE_Leituras

    Ainda estou estudando o conceito da recurssividade, mas chego lá.

    • Sugerido como Resposta ARTEN-DECÁDA domingo, 15 de novembro de 2020 12:43
    domingo, 15 de novembro de 2020 11:43
  • Pelo que entendi você não vai precisar adicionar mais uma CTE, você está apenas adicionando 1 coluna na primeira CTE, e isso pode ser feito tranquilamente, o único detalhe é que acho que você vai ter que adicionar as colunas utilizadas no calculo dessa coluna "cabecas" no Group By.

    Sobre recursividade, não estamos utilizando nesse caso. Em uma CTE recursiva as linhas da própria CTE são consultadas dentro dela mesmo utilizando o operador Union. Na consulta que utilizamos até aqui foram utilizadas 2 CTEs simples em sequencia.

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 16 de novembro de 2020 12:52