Seleccionando datos de series temporales e interpolando los valores faltantes como nulos.

Tengo una tabla de (tstz, valor) en la cual se insertan datos en momentos aleatorios en el tiempo, generalmente cada segundo. Quiero seleccionar y promediar los datos de la serie temporal, lo cual puedo hacer con:

SELECT to_timestamp(round((extract(epoch from tstz)) / 10) * 10) AS ts, AVG(valor) FROM tabla GROUP BY ts ORDER BY ts

La función to_timestamp(round((extract(epoch from tstz)) / 10) * 10) redondea el timestamptz al intervalo más cercano proporcionado (es decir, 10 segundos en este ejemplo). Entonces, asumiendo 10 valores por segundo, se agregan 10 valores en un promedio. Sin embargo, si hay un período de 10 segundos (o cualquier período igual al período en el que estamos promediando), entonces el timestamp se “pierde”, quiero que el valor en su lugar sea nulo.

Estoy intentando hacer esto con generate_series(), de manera que tenga una lista precisa de timestamps, lo cual puedo lograr con éxito. Sin embargo, no sé cómo hacer que esto sea compatible con la consulta anterior y agregar valores nulos donde no hay valores para promediar.

davy.ai

August 2, 2023 at 4:58 pm

Para agregar valores nulos donde no haya valores para promediar, puedes usar un left join entre la serie generada y la tabla que contiene los datos de la serie temporal. Esto asegurará que todos los timestamps de la serie estén incluidos, incluso si no tienen valores correspondientes en la tabla de datos. Aquí tienes un ejemplo de consulta que logra esto:

SELECT 
    s.ts,
    AVG(t.value)
FROM 
    generate_series(
        (SELECT MIN(tstz) FROM table), 
        (SELECT MAX(tstz) FROM table), 
        interval '10 seconds'
    ) AS s(ts)
    LEFT JOIN table t ON 
        to_timestamp(round((extract(epoch from t.tstz)) / 10) * 10) = s.ts
GROUP BY s.ts
ORDER BY s.ts

En esta consulta, primero generamos una serie de timestamps usando generate_series(), comenzando desde el timestamp mínimo en la tabla de datos y finalizando en el timestamp máximo, con un intervalo de 10 segundos. Aliasamos esta serie como s(ts).

Luego hacemos un left join entre esta serie y la tabla de datos table, uniéndolas mediante los timestamps redondeados como antes. Esto asegura que todos los timestamps de la serie estén incluidos, con valores nulos para cualquier dato faltante.

Finalmente, agrupamos por los timestamps de la serie y calculamos el promedio de los valores correspondientes en la tabla de datos, como antes. Esto nos dará el resultado deseado, con valores nulos donde no haya valores para promediar.

Answer

davy.ai

August 2, 2023 at 4:58 pm
Para agregar valores nulos donde no haya valores para promediar, puedes usar un left join entre la serie generada y la tabla que contiene los datos de la serie temporal. Esto asegurará que todos los timestamps de la serie estén incluidos, incluso si no tienen valores correspondientes en la tabla de datos. Aquí tienes un ejemplo de consulta que logra esto:
```
SELECT 
    s.ts,
    AVG(t.value)
FROM 
    generate_series(
        (SELECT MIN(tstz) FROM table), 
        (SELECT MAX(tstz) FROM table), 
        interval '10 seconds'
    ) AS s(ts)
    LEFT JOIN table t ON 
        to_timestamp(round((extract(epoch from t.tstz)) / 10) * 10) = s.ts
GROUP BY s.ts
ORDER BY s.ts
```
En esta consulta, primero generamos una serie de timestamps usando generate_series(), comenzando desde el timestamp mínimo en la tabla de datos y finalizando en el timestamp máximo, con un intervalo de 10 segundos. Aliasamos esta serie como s(ts).

Luego hacemos un left join entre esta serie y la tabla de datos table, uniéndolas mediante los timestamps redondeados como antes. Esto asegura que todos los timestamps de la serie estén incluidos, con valores nulos para cualquier dato faltante.

Finalmente, agrupamos por los timestamps de la serie y calculamos el promedio de los valores correspondientes en la tabla de datos, como antes. Esto nos dará el resultado deseado, con valores nulos donde no haya valores para promediar.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Seleccionando datos de series temporales e interpolando los valores faltantes como nulos.

Answer

davy.ai