Este script da el conteo por hora de las marcas de tiempo en la tabla.
SELECT date_trunc('hour', s.fill_instant) h , count(date_trunc('hour', s.fill_instant)) c FROM sms s left join station s2 on s.station_id = s2.station_id where s2.address like '%arizona%' and s.fill_date between '2021-09-19' and '2021-09-19' GROUP BY date_trunc('hour', s.fill_instant) order by date_trunc('hour', s.fill_instant) asc;en la tabla como la siguiente
2021-09-19 00:00:00 3 2021-09-19 02:00:00 20 2021-09-19 03:00:00 6 2021-09-19 13:00:00 7 2021-09-19 14:00:00 11 2021-09-19 15:00:00 6¿Cómo puedo insertar los Ceros en las horas no presentes? por lo que muestra el mapa de 24 horas completas. muy parecido a esto
2021-09-19 00:00:00 3 2021-09-19 01:00:00 0 2021-09-19 02:00:00 20 2021-09-19 03:00:00 6 2021-09-19 04:00:00 0 2021-09-19 05:00:00 0 2021-09-19 06:00:00 0 2021-09-19 07:00:00 0 2021-09-19 08:00:00 0 2021-09-19 09:00:00 0 2021-09-19 10:00:00 0 2021-09-19 11:00:00 0 2021-09-19 12:00:00 0 2021-09-19 13:00:00 7 2021-09-19 14:00:00 11 2021-09-19 15:00:00 6 2021-09-19 16:00:00 0 2021-09-19 17:00:00 0 2021-09-19 18:00:00 0 2021-09-19 19:00:00 0 2021-09-19 20:00:00 0 2021-09-19 21:00:00 0 2021-09-19 22:00:00 0 2021-09-19 23:00:00 0 2021-09-19 24:00:00 0Use generate_series para obtener todas las horas del día dado como CTE y luego left join a él con su consulta:
WITH hours as (Select * from generate_series('2021-09-19 00:00:00'::timestamp, '2021-09-19 23:59:59'::timestamp, INTERVAL '1 hour') as hr) SELECT hours.hr, coalesce(qry.c, 0) as c FROM hours LEFT JOIN(SELECT date_trunc('hour', s.fill_instant) h , count(date_trunc('hour', s.fill_instant)) c FROM sms s left join station s2 on s.station_id = s2.station_id where s2.address like '%arizona%' and s.fill_date between '2021-09-19' and '2021-09-19' GROUP BY date_trunc('hour', s.fill_instant) order by date_trunc('hour', s.fill_instant) asc) qry on qry.h = hours.hrUn patrón más o menos genérico para llenar los huecos en una secuencia sería este:
Use su consulta existente ( t ) y únala externamente con la secuencia densa de horas ( ds ). Use coalesce para establecer valores null de tc como 0.
with t as ( .. your query here .. ) select ds.h, coalesce(tc, 0) c from generate_series ( timestamp '2021-09-19T00:00:00', timestamp '2021-09-19T24:00:00', interval '1 hour' ) as ds(h) left outer join t on th = ds.h;Otra forma de generar todas las horas de un día es usar una consulta recursiva.
Luego, debe hacer un LEFT JOIN entre la subconsulta de hours y su consulta, y en aquellas filas que no coincidan con su consulta (la columna c es nula), debe poner un cero (utilicé una instrucción CASE ).
WITH RECURSIVE hours AS (SELECT '2021-09-19 00:00:00'::timestamp AS hour UNION ALL SELECT hour + interval '1 hour' FROM hours WHERE hour < '2021-09-19 23:00:00') SELECT hours.hour, CASE WHEN sq.c IS NULL THEN 0 ELSE sq.c END AS c FROM hours LEFT JOIN (SELECT date_trunc('hour', s.fill_instant) h, count(date_trunc('hour', s.fill_instant)) c FROM sms s LEFT JOIN station s2 ON s.station_id = s2.station_id WHERE s2.address like '%arizona%' AND s.fill_date = '2021-09-19' GROUP BY date_trunc('hour', s.fill_instant)) AS sq ON hours.hour = sq.h ORDER BY hours.hour;