此腳本提供表中時間戳的每小時計數。
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;
在如下表中
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
如何在非當前時間插入零。所以它顯示了完整的24小時地圖。很像這樣
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 0
uj5u.com熱心網友回復：
使用generate_series以獲取給定的一天，一個CTE的所有小時，然后left join將其與您的查詢：
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.hr
uj5u.com熱心網友回復：
使用您現有的查詢并將其與完整的 hours 串列外部連接gs。使用coalesce丟失的（空）值t.c將它們設定為0。
with t as
(
.. your query here ..
)
select gs.h, coalesce(t.c, 0) c
from generate_series
(
timestamp '2021-09-19T00:00:00',
timestamp '2021-09-19T24:00:00',
interval '1 hour'
) as gs(h)
left outer join t on t.h = hs.h;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/327541.html
標籤：sql PostgreSQL 日期部分
上一篇：查找一個數字是否一直在增加/減少