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