SQL : count records per 5 minute intervals (including zeros)

Question

I have a responses table, which has a timestamp field (created_at), an id, and a foreign key (context_id)

For a given context, I'd like to see how many responses occurred over 5 minute intervals.

It is a very similar situation to the following question :

Sql Server: Count records (including zero) per ten-minute intervals

Except that I am not using SQL Server, I'm using MySQL, but would like to see a more generic solution.

There will be many 5 minute intervals in which there are 0 responses, which I'd like included in the results.

Answer 1

If you start with a table of "buckets"

bucket_start          bucket_end
--
2011-01-01 00:00:00   2011-01-01 00:05:00
2011-01-01 00:05:00   2011-01-01 00:10:00
2011-01-01 00:10:00   2011-01-01 00:15:00
2011-01-01 00:15:00   2011-01-01 00:20:00

then you can use an outer join to get the missing zeroes.

select bucket_start, bucket_end, count(context_id)
from buckets b
left join responses r 
       on (r.response_time >= b.bucket_start and 
           r.response_time <  b.bucket_end)
group by b.bucket_start, b.bucket_end

There are many ways to generate a table (or view) of buckets. But the most general solution has to avoid automatic series generators (like PostgreSQL's generate_series()) and common table expressions, because MySQL doesn't support them. (Although you might be able to write your own function to mimic generate_series().)

So the most generic solution is just a base table. You'll also probably get the best performance from a base table, since you can index the columns.

You're looking at about 105,000 rows per calendar year for a table like that.