Postgres: Group by hour and append the missing hours to the result with 0 counts

user9ari 0
Viewed 46

I have the following query, which returns the values correctly.

SELECT date_trunc('hour', created_at)                                     as date,
       CAST(COUNT(case when subscription = true then id end) AS INTEGER)  as subscriptions,
       CAST(COUNT(case when subscription = false then id end) AS INTEGER) as unsubscriptions
FROM requests
GROUP BY date
ORDER BY date

Output:

date subscriptions unsubscriptions
2021-01-14 23:00:00.000000 +00:00 1 2
2021-01-14 20:00:00.000000 +00:00 10 1
2021-01-14 18:00:00.000000 +00:00 0 1
... ... ...

The problem is that i need the missing hours of the day even if there are not any entries in the table, with 0 count.

Expected output:

date subscriptions unsubscriptions
2021-01-14 23:00:00.000000 +00:00 1 2
2021-01-14 22:00:00.000000 +00:00 0 0
2021-01-14 21:00:00.000000 +00:00 0 0
2021-01-14 20:00:00.000000 +00:00 10 1
2021-01-14 19:00:00.000000 +00:00 0 0
2021-01-14 18:00:00.000000 +00:00 0 1
... ... ...

Table Structure:

id subscription created_at
int bool timestamp
sql postgresql
1 Answers

You will have to create a timeseries for the timeseries you want to report and LEFT JOIN your table with it. You can use generate_series to create that.

--Sample table
WITH YOUR_TABLE AS (
  SELECT NOW() AS created_at, TRUE AS subscription
  UNION SELECT NOW() - interval '3 hour', FALSE
  UNION SELECT NOW() - interval '5 hour', TRUE
  UNION SELECT NOW() - interval '5 hour', FALSE
  UNION SELECT NOW() - interval '8 hour', FALSE
),
--Generate time series based on min and max created_at
TIME_SERIES AS (
  SELECT GENERATE_SERIES AS hour
  FROM GENERATE_SERIES
        ( (SELECT MIN(date_trunc('hour', created_at)) FROM YOUR_TABLE)
        , (SELECT MAX(date_trunc('hour', created_at)) FROM YOUR_TABLE)
        , '1 hour'::interval))

--Left join your table to the generated time series  
SELECT 
   TIME_SERIES.hour, 
   SUM(CASE WHEN subscription = true THEN 1 ELSE 0 END)  as subscriptions,
   SUM(CASE WHEN subscription = false THEN 1 ELSE 0 END) as unsubscriptions
FROM TIME_SERIES
LEFT JOIN YOUR_TABLE
ON TIME_SERIES.hour = date_trunc('hour', created_at) 
GROUP BY TIME_SERIES.hour
ORDER BY TIME_SERIES.hour

dbfiddle here

VvdL0
Related