Categories
aggregate-functions loops postgresql sql window-functions

Group and count events per time intervals, plus running total

I’m a fairly new Postgres user, I’m sure there’s an answer to this already but I can’t find it.

I need to analyze some data in an activity log table, grouping the the results by time period.

A simple version of the problem would be a table with three fields:

    Column    |           Type           |              Modifiers
--------------+--------------------------+-------------------------------------
period_start | timestamp with time zone | not null
user_id | text | not null
action | text | not null

The action string I want to capture could be ‘create_entry’ (yes, I know that sucks as good db design but I’m stuck with it)

The output I’m looking for is a report showing count of ‘create_entry’ actions by year and month. Something like:

 Year | Month | Entries
------+-------+----------
2013 | 12 | 14345
2014 | 1 | 9876
2014 | 2 | 10234

My instinct is to attack this with sub-queries but looking around there seems to be a suggestion that loops might be the way to go.

Either way, I’m out of my depth and looking for a push in the right direction.

EDIT

A supplementary question based on a request that came in while I was typing this.. A variation on the above that would show a cumulative total month by month (although I’d like to think I could figure that out by myself based on the approach to the above – I’m just adding this in case it’s relevant to the approach).