Considere una tabla con 2 columnas:
create table foo ( ts timestamp, precipitation numeric, primary key (ts) );con los siguientes datos:
| t | precipitación |
|---|---|
| 2021-06-01 12:00:00 | 1 |
| 2021-06-01 13:00:00 | 0 |
| 2021-06-01 14:00:00 | 2 |
| 2021-06-01 15:00:00 | 3 |
Me gustaría usar un agregado continuo de TimescaleDB para calcular una suma acumulativa de tres horas de estos datos que se calcula una vez por hora. Usando los datos de ejemplo anteriores, mi agregado continuo contendría
| t | cum_precipitation |
|---|---|
| 2021-06-01 12:00:00 | 1 |
| 2021-06-01 13:00:00 | 1 |
| 2021-06-01 14:00:00 | 3 |
| 2021-06-01 15:00:00 | 5 |
No puedo ver una manera de hacer esto con la sintaxis admitida para agregados continuos. ¿Me estoy perdiendo de algo? Esencialmente, me gustaría que el intervalo de tiempo sea las x horas anteriores, pero el cálculo se realice cada hora.
¡Buena pregunta!
Puede hacer esto calculando un agregado continuo normal y luego haciendo una función de ventana sobre él. Por lo tanto, calcule una sum() para cada hora y luego haga una sum() como funcionaría una función de ventana.
Cuando ingrese a agregados más complejos como promedio o desviación estándar o aproximación de percentiles o similares, recomendaría cambiar a algunos de los agregados de dos pasos que introdujimos en TimescaleDB Toolkit. Específicamente, miraría los agregados estadísticos que presentamos recientemente. También pueden hacer este tipo de suma acumulativa. (Solo funcionarán con DOBLE PRECISIÓN o cosas que se pueden lanzar a eso, es decir, FLOAT , le recomiendo encarecidamente que no use NUMERIC y en su lugar cambie a dobles o flotantes, no parece que realmente necesite cálculos de precisión infinita aquí).
Puede echar un vistazo con algunas consultas que escribí en esta presentación , pero podría ser algo como:
CREATE MATERIALIZED VIEW response_times_five_min WITH (timescaledb.continuous) AS SELECT api_id, time_bucket('1 hour'::interval, ts) as bucket, stats_agg(response_time) FROM response_times GROUP BY 1, 2; SELECT bucket, average(rolling(stats_agg) OVER last3), sum(rolling(stats_agg) OVER last3) FROM response_times_five_min WHERE api_id = 32 WINDOW last3 as (ORDER BY bucket RANGE '3 hours' PRECEDING);