Início / dba / Perguntas / 343914
Accepted
Antti Keskinen
Asked: 2024-11-28 23:23:44 +0800 CST

No PostgreSQL, como obter o mínimo horário de um valor usando uma função de janela?

  • 772

Vamos supor que temos os seguintes dados com uma medição de temperatura por segundo. O intervalo de dados é de vários anos, então há um número considerável de linhas. São dados de medição de um dispositivo de campo. A tabela tem outras colunas também, mas elas não são relevantes para a questão.

Tempo Temperatura_graus_em_C
2024-11-01 00:00:00+00 20.1
2024-11-01 00:00:01+00 21.2
2024-11-01 00:00:02+00 21.6
2024-11-01 00:00:03+00 20.2
... ...
2026-12-31 23:59:57+00 25,4
2026-12-31 23:59:58+00 25.2
2026-12-31 23:59:59+00 25,6

Sei que posso usar uma GROUP BYcláusula para obter as médias em janelas de uma hora entre um horário específico de início e término, como esta:

  SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
    FROM "measurements"
   WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY to_char("time", 'YYYY-MM-DD HH')

No entanto, essa sintaxe parece um hack e não se adapta a quadros de janela (ou partições) de 10 segundos ou outros mais complexos, por exemplo.

Além disso, gostaria de saber, especificamente, como isso é feito corretamente e com desempenho usando o OVER, WINDOW, PARTITION BY, e ferramentas semelhantes descritas no manual doRANGE PostgreSQL .ROWS

ATUALIZAÇÃO: Funções de janela não podem ser usadas para atingir esse objetivo no Postgres, pois elas não criam "buckets" ou "frames" da maneira que eu pensava que faziam. GROUP BYé a maneira de conseguir isso.

O objetivo final é encontrar uma solução onde o comprimento da janela e a unidade de medida (minutos, horas, dias), e o horário de início e término do período de observação sejam flexíveis, mas o corpo da consulta em si permaneça o mesmo. Uma consulta parametrizada que seja simples de entender, isto é.

postgresql

2 respostas

  1. Best Answer

    Nesta consulta:

    SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
FROM "measurements"
WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY 1

    ...você está convertendo o timestamp em identificadores de bin que serão idênticos para cada linha no grupo, então eles terminam no mesmo bin. Ao mudar a maneira como isso é computado, você pode usar bins de tempo que não são limitados a 1 minuto, 1 hora ou 1 dia.

    Por exemplo, em vez de to_char() você pode usar:

    FLOOR( EXTRACT( epoch FROM "time" ) / bin_length )

    Isso converte os timestamps postgres para timestamps UNIX (expressos em segundos) e divide pelo bin_length (também em segundos). Então o floor resulta em um inteiro que é o identificador bin para esta linha. Com esta expressão, os bins começarão no timestamp UNIX zero e se repetirão em múltiplos de bin_length.

    Você também pode usar algo como:

    FLOOR( EXTRACT( epoch FROM ( "time" - start_time ) / bin_length )

    Isso subtrai "time" do parâmetro de consulta start_time, então o primeiro bin começará em "start_time" e os bins serão repetidos em múltiplos de bin_length.

    O resultado do GROUP BY conterá números de bin. Então você pode fazer a mesma operação ao contrário para converter de volta para timestamps. Por exemplo:

    SELECT start_time + '1 second'::INTERVAL * bin_id, m_avg FROM (
SELECT 
FLOOR( EXTRACT( epoch FROM ( "time" - start_time ) / bin_length ) AS bin_id,
avg( measurement ) m_avg,
...
GROUP BY bin_id
)

    Ou você pode usar o registro de data e hora na borda esquerda do bin como chave do bin, substituindo to_char() por algo assim:

    start_time + '1 second'::INTERVAL * FLOOR( 
    EXTRACT( epoch FROM ( "time" - start_time ) / bin_length ))
    • 0

  2. Consulta PLPgSQL usando função do Windows:

    SELECT 
    to_char(time, 'YYYY-MM-DD HH'), 

    ROUND(
        AVG(temperature_deg_in_c) OVER (
           PARTITION BY to_char("time", 'YYYY-MM-DD HH')
        ), 
    2) AS AVG_TEMP

FROM measurements

WHERE time BETWEEN '2024-11-01' AND '2024-12-01';

    maneira alternativa com a função do windows:

    SELECT to_char(time, 'YYYY-MM-DD HH')
    ROUND(
        SUM(temperature_deg_in_c) OVER (PARTITION BY to_char("time", 'YYYY-MM-DD HH'))/
        FLOOR(COUNT(temperature_deg_in_c) OVER (PARTITION BY to_char("time", 'YYYY-MM-DD HH')))
    ) AS COUNT_TEMP
    
FROM measurements
WHERE time BETWEEN '2024-11-01' AND '2024-12-01';

    db<>violino

    • -1

relate perguntas