SQL Averages Over Time

Some SQL to show an average count of things each minute for a series of hours

[ Tagged: documentation postgresql sql ]

Published: 2019-09-23 18:16:00

I recently had the need to provide some data on average events occuring each minute, over the course of an hour, for each hour in a day. These events were being to a postgresql database. Here's snippet of SQL I cobbled together to for this:

with minutes as (
    select date_trunc('minute', "createdAt") as "time",
           count(1)                          as "count"
    from "order"
    -- where "createdAt" >= now()::date
    where "createdAt" >= '2019-09-23 00:00:00'
    group by 1
    order by 1 desc
)

select to_char(date_trunc('hour', minutes.time), 'YYYY-MM-dd HH24MI') as "hour",
       round(avg(minutes.count), 2)                                   as "avg things/minute"
from minutes
group by 1
order by 1 desc;

The with block creates a sort of "in memory" table from which further queries can be run against. Here it's providing a total count of things and grouping them by minute. The date_trunc function truncates the timestamp up to minute part, so ignoring seconds, milliseconds etc.

The subsequent select query then finds the average count of things per minute, for each hour.

This will produce results as follows:

hour avg things/minute
2019-09-23 1700 4.5
2019-09-23 1600 6.34
2019-09-23 1500 5.28
2019-09-23 1400 9.48
2019-09-23 1300 7
2019-09-23 1200 6.14
2019-09-23 1100 3.55
2019-09-23 1000 3.58
2019-09-23 0900 5.07
2019-09-23 0800 6.51
2019-09-23 0700 6.68
2019-09-23 0600 6.3
2019-09-23 0500 4.23
2019-09-23 0400 4.29
2019-09-23 0300 3.56
2019-09-23 0200 7.33
2019-09-23 0100 9.03
2019-09-23 0000 6.22