none
Problema ao agrupar em Horas RRS feed

  • Pergunta

  • Olá, estou querendo mudar esta Script, pois ela está apresentando valores nulos.

    USE BDMI_EIXOLESTE;
    
    with
        CTE_Min as
        (
            SELECT
                DATEPART(HOUR, E3TimeStamp) as Hora,
                MIN(FI01_01_tot) as Minimo
            FROM Tab_TotHora_UTR_01
            WHERE
                CAST(E3TimeStamp as date) = '2020-04-23'
            GROUP BY
                DATEPART(HOUR, E3TimeStamp)
        ),
    
        CTE_Lag as
        (
            SELECT
                Hora,
                Minimo,
                LAG (Minimo) OVER (ORDER BY Hora) as MinimoAnt
            FROM CTE_Min
        )
    
    SELECT
        Hora,
        Minimo,
        MinimoAnt,
       Minimo - MinimoAnt as FI01_1_TOT
    FROM CTE_Lag

    No caso eu precisaria pegar o valor das 23 horas do dia anterior, pois o primeiro horário do dia que é a meia noite, está mostrando nulo. No caso eu preciso somente o valor das 23 horas do dia anterior para poder fazer o cálculo, e me mostrar os valores do dia inteiro.

    Yohrannes


    segunda-feira, 4 de maio de 2020 20:15

Respostas

  • Yohrannes,

    Em relação ao Null, você pode aplicar este restrição como uma condição no seu Where ou até mesmo no Group By usando a cláusula Having.

    Talvez seja o caso de você adicionar um Group na CTE_LAG, veja se estes possíveis alterações te ajudam:

    CTE_Lag as ( SELECT Hora, Minimo, LAG (Minimo) OVER (ORDER BY Hora) as MinimoAnt FROM CTE_Min ) SELECT Hora, Minimo, MinimoAnt, Minimo - MinimoAnt as Dif FROM CTE_Lag
    Where MinimoAnt Is Not Null -- Adicionando o Where com a condição para eliminar os valores Nulos.

    SELECT * FROM Teste_Yo ORDER BY E3TimeStamp


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 6 de maio de 2020 20:49

Todas as Respostas

  • Yohrannes,

    A função LAG() é uma função não determinística, neste caso, nem sempre ela irá trazer o mesmo conjunto de dados ou propriamente o dado ao qual você deseja.

    Talvez uma alternativa seja combinar a função Lag() com a função IsNull(), no qual se o resultado do MinimoAnt for Null você trocaria por 23.

    Algo similar a este exemplo:

    Select Hora, Minimo, 
                 IsNull(MinimoAnt,23), 
                 Minimo - IsNull(MinimoAnt,23) As T.....
    From ....


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de maio de 2020 03:21
  • Bom dia,

    Yohrannes, acho que uma alternativa seria acrescentar o dia anterior na primeira CTE. Segue uma sugestão para testes:

    with
        CTE_Min as
        (
            SELECT
                CAST(E3TimeStamp as date) as Data, 
                DATEPART(HOUR, E3TimeStamp) as Hora,
                MIN(FI01_01_tot) as Minimo
            FROM Tab_TotHora_UTR_01
            WHERE
                CAST(E3TimeStamp as date) between '2020-04-22' and '2020-04-23'
            GROUP BY
                CAST(E3TimeStamp as date),
                DATEPART(HOUR, E3TimeStamp)
        ),
    
        CTE_Lag as
        (
            SELECT
                Hora,
                Minimo,
                LAG (Minimo) OVER (ORDER BY Data, Hora) as MinimoAnt
            FROM CTE_Min
            WHERE
                Data = '2020-04-23'
        )
    
    SELECT
        Hora,
        Minimo,
        MinimoAnt,
        Minimo - MinimoAnt as FI01_1_TOT
    FROM CTE_Lag

    Espero que ajude


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

    terça-feira, 5 de maio de 2020 13:26
  • Então no caso eu preciso pegar o valor mesmo, ele agrupa pra mim e vai me mostrando os as horas 0,1,2,3,4,5.....  e o minimo,minimo anterior, só que nas 0 horas ele não consegue pegar o mínimo da hora anterior, nessa imagem no caso, no local da coluna MinimoAnt aonde está o valor null, deveria estar o valor da última hora do dia '2020-04-22' no caso, para poder exibir, e consequentemente fazer o cálculo na FI01_1_TOT, esse valor existe e está lá, só que como eu coloquei para pegar somente do dia '2020-04-23' não está me mostrando, o problema é que se eu pedir pra ele pegar dos dois dias, ele vai continuar agrupando, e me mostrar como se fosse um dia só.

    Yohrannes

    terça-feira, 5 de maio de 2020 17:13
  • ..., o problema é que se eu pedir pra ele pegar dos dois dias, ele vai continuar agrupando, e me mostrar como se fosse um dia só.
    Isso não vai ocorrer com a alteração que sugeri porque acrescentei a coluna Data no Select e no Group By, então os dias e respectivos horários serão agrupados separadamente.

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

    terça-feira, 5 de maio de 2020 17:49
  • Então no caso eu preciso pegar o valor mesmo, ele agrupa pra mim e vai me mostrando os as horas 0,1,2,3,4,5.....  e o minimo,minimo anterior, só que nas 0 horas ele não consegue pegar o mínimo da hora anterior, nessa imagem no caso, no local da coluna MinimoAnt aonde está o valor null, deveria estar o valor da última hora do dia '2020-04-22' no caso, para poder exibir, e consequentemente fazer o cálculo na FI01_1_TOT, esse valor existe e está lá, só que como eu coloquei para pegar somente do dia '2020-04-23' não está me mostrando, o problema é que se eu pedir pra ele pegar dos dois dias, ele vai continuar agrupando, e me mostrar como se fosse um dia só.

    Yohrannes

    Yohrannes,

    Mas se os valores estão sendo agrupados ele não pode repetir os valores da forma que você esta dizendo.

    Poderia nos retornar uma amostra deste resultado que apresenta esta duplicação de valores?



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de maio de 2020 20:52
  • Então só que no caso, ele ainda me mostra valores Null na primeira linha da coluna minimoant e FI01..

    Yohrannes

    quarta-feira, 6 de maio de 2020 19:28
  • Então eu fiz de uma forma que acredito que dá pra entender melhor.

    Esse é o Script.

    USE Teste;
    
    with
        CTE_Min as
        (
            SELECT
                DATEPART(HOUR, E3TimeStamp) as Hora,
                MIN(FI01_01_tot) as Minimo
            FROM Teste_Yo
            WHERE
                CAST(E3TimeStamp as date) = '2020-04-29'
            GROUP BY
                DATEPART(HOUR, E3TimeStamp)
        ),
    
        CTE_Lag as
        (
            SELECT
                Hora,
                Minimo,
                LAG (Minimo) OVER (ORDER BY Hora) as MinimoAnt
            FROM CTE_Min
        )
    
    SELECT
        Hora,
        Minimo,
        MinimoAnt,
       Minimo - MinimoAnt as Dif
    FROM CTE_Lag
    
    SELECT * FROM Teste_Yo ORDER BY E3TimeStamp

    Nessa imagem mostra o que eu quero.

    Eu gostaria que ao invés dele pegar os valores somente do dia que eu descrevi, pegasse também o último valor da última hora registrado no dia anterior entende?, e não me mostrasse este null no início.

    Esse é o script de onde eu tentei ao invés de pegar somente um dia, pegar entre dois dias, usando o between.

    USE Teste;
    
    with
        CTE_Min as
        (
            SELECT
                DATEPART(HOUR, E3TimeStamp) as Hora,
                MIN(FI01_01_tot) as Minimo
            FROM Teste_Yo
            WHERE
                CAST(E3TimeStamp as date) BETWEEN '2020-04-28' AND '2020-04-29'
            GROUP BY
                DATEPART(HOUR, E3TimeStamp)
        ),
    
        CTE_Lag as
        (
            SELECT
                Hora,
                Minimo,
                LAG (Minimo) OVER (ORDER BY Hora) as MinimoAnt
            FROM CTE_Min
        )
    
    SELECT
        Hora,
        Minimo,
        MinimoAnt,
       Minimo - MinimoAnt as Dif
    FROM CTE_Lag
    
    SELECT * FROM Teste_Yo ORDER BY E3TimeStamp

    E é isso que ele me mostra.

    Está pegando os valores dos dois dias e juntando, e tambem me mostrando o null. Sendo que eu quero que ele simplesmente não me mostre esse valor null, sem juntar os valores dos dois dias, mas pegar o valor do dia anterior somente para completar.

    Yohrannes




    quarta-feira, 6 de maio de 2020 19:36
  • Yohrannes,

    Em relação ao Null, você pode aplicar este restrição como uma condição no seu Where ou até mesmo no Group By usando a cláusula Having.

    Talvez seja o caso de você adicionar um Group na CTE_LAG, veja se estes possíveis alterações te ajudam:

    CTE_Lag as ( SELECT Hora, Minimo, LAG (Minimo) OVER (ORDER BY Hora) as MinimoAnt FROM CTE_Min ) SELECT Hora, Minimo, MinimoAnt, Minimo - MinimoAnt as Dif FROM CTE_Lag
    Where MinimoAnt Is Not Null -- Adicionando o Where com a condição para eliminar os valores Nulos.

    SELECT * FROM Teste_Yo ORDER BY E3TimeStamp


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 6 de maio de 2020 20:49
  • Yohrannes, você pode por favor fazer um teste com a query da forma como sugeri, sem essas alterações que você fez, para depois poder demonstrar com o resultado os problemas ocorridos?

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

    quinta-feira, 7 de maio de 2020 12:22
  • Yohrannes, segue a query que sugeri anteriormente com pequenos ajustes:

    with
        CTE_Min as
        (
            SELECT
                CAST(E3TimeStamp as date) as Data, 
                DATEPART(HOUR, E3TimeStamp) as Hora,
                MIN(FI01_01_tot) as Minimo
            FROM Tab_TotHora_UTR_01
            WHERE
                CAST(E3TimeStamp as date) between '2020-04-28' and '2020-04-29'
            GROUP BY
                CAST(E3TimeStamp as date),
                DATEPART(HOUR, E3TimeStamp)
        ),
    
        CTE_Lag as
        (
            SELECT
                Data,
                Hora,
                Minimo,
                LAG (Minimo) OVER (ORDER BY Data, Hora) as MinimoAnt
            FROM CTE_Min
        )
    
    SELECT
        Hora,
        Minimo,
        MinimoAnt,
        Minimo - MinimoAnt as FI01_1_TOT
    FROM CTE_Lag
    WHERE
        Data = '2020-04-29'
    

    E segue o link da página onde fiz o teste da query: https://sqlfiddle.com/#!18/b6a12/1

    Espero que ajude


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

    • Sugerido como Resposta Avatar SQL quinta-feira, 4 de junho de 2020 13:20
    segunda-feira, 11 de maio de 2020 13:12