Consulta SQL: Rellenar los valores faltantes con 0

Tengo una tabla con brechas en los datos en ciertos momentos (ver que no hay datos entre 37 y 46). Necesito llenar esas brechas con 0 para una mejor visualización en el frontend.

Las veces son enteros secuenciales (marcas de tiempo UNIX inicialmente). He agregado mis datos en cubos de tiempo de 5 minutos.
La consulta frontend pasará una hora de inicio y fin y agregará los datos en cubos más grandes. Por ejemplo:

SELECT
(tiempo_de_cinco_minutos / 6) AS fecha,
SUM(mis_datos) AS mis_datos
FROM tabla_de_mis_datos_cada_cinco_minutos
WHERE
tiempo_de_cinco_minutos ENTRE (1640000000 / 300) Y (1640086400 / 300)
GROUP BY fecha
ORDER BY fecha ASC;

Me gustaría poder obtener un resultado:

fecha | mis_datos
——–+—————–
911130 | 10
911131 | 11
911132 | 9
911133 | 6
911134 | 5
911135 | 5
911136 | 10
911137 | 8
911138 | 0
911139 | 0
911140 | 0
911141 | 0
911142 | 0
911143 | 0
911144 | 0
911145 | 0
911146 | 4
911147 | 5
911148 | 9
911149 | 14
911150 | 8

Como nota, esta consulta se está ejecutando en AWS Redshift.

davy.ai

August 6, 2023 at 2:38 pm

Para rellenar los espacios vacíos con 0, podemos crear una tabla temporal que tenga todos los intervalos de tiempo que necesitamos, y luego hacer un left join con nuestra tabla de datos original para rellenar los valores. Aquí tienes un ejemplo de consulta:

-- Crear tabla temporal con intervalos de tiempo
CREATE TEMP TABLE time_buckets AS (
  SELECT generate_series(min(five_min_time), max(five_min_time), 300) AS five_min_time
  FROM mydata_table_five_min
);

-- Rellenar espacios vacíos con 0
SELECT
  (tb.five_min_time / 6) AS fecha,
  COALESCE(SUM(md.mydata), 0) AS misdatos
FROM time_buckets tb
LEFT JOIN mydata_table_five_min md ON tb.five_min_time = md.five_min_time
WHERE tb.five_min_time BETWEEN (1640000000 / 300) AND (1640086400 / 300)
GROUP BY fecha
ORDER BY fecha ASC;

En esta consulta, primero creamos una tabla temporal llamada time_buckets utilizando la función generate_series para crear una fila para cada intervalo de tiempo que necesitamos. Luego hacemos un left join de esta tabla con nuestra tabla de datos original mydata_table_five_min, utilizando COALESCE para reemplazar los valores nulos con 0. Finalmente, agrupamos por la fecha agregada y ordenamos por fecha ascendente. Esto debería darnos el resultado deseado con los espacios rellenados con 0.

Answer

davy.ai

August 6, 2023 at 2:38 pm
Para rellenar los espacios vacíos con 0, podemos crear una tabla temporal que tenga todos los intervalos de tiempo que necesitamos, y luego hacer un left join con nuestra tabla de datos original para rellenar los valores. Aquí tienes un ejemplo de consulta:
```
-- Crear tabla temporal con intervalos de tiempo
CREATE TEMP TABLE time_buckets AS (
  SELECT generate_series(min(five_min_time), max(five_min_time), 300) AS five_min_time
  FROM mydata_table_five_min
);

-- Rellenar espacios vacíos con 0
SELECT
  (tb.five_min_time / 6) AS fecha,
  COALESCE(SUM(md.mydata), 0) AS misdatos
FROM time_buckets tb
LEFT JOIN mydata_table_five_min md ON tb.five_min_time = md.five_min_time
WHERE tb.five_min_time BETWEEN (1640000000 / 300) AND (1640086400 / 300)
GROUP BY fecha
ORDER BY fecha ASC;
```
En esta consulta, primero creamos una tabla temporal llamada time_buckets utilizando la función generate_series para crear una fila para cada intervalo de tiempo que necesitamos. Luego hacemos un left join de esta tabla con nuestra tabla de datos original mydata_table_five_min, utilizando COALESCE para reemplazar los valores nulos con 0. Finalmente, agrupamos por la fecha agregada y ordenamos por fecha ascendente. Esto debería darnos el resultado deseado con los espacios rellenados con 0.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Consulta SQL: Rellenar los valores faltantes con 0

Answer

davy.ai