Problema ao agrupar em Horas

Question

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

Answer 1

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]

Answer 2

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

Answer 3

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

Answer 4

..., 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

Answer 5

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]

Answer 6

Então só que no caso, ele ainda me mostra valores Null na primeira linha da coluna minimoant e FI01..

---

Yohrannes

Answer 7

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

Answer 8

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]

Answer 9

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

Answer 10

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