Postgres 9.1 - Line Numbering Groups

Question

I have some data that represent different "actions". These “actions” together constitute an “event”.

The data is as follows:

EventID | UserID | Action | TimeStamp --------------+------------+------------+------------------------- 1 | 111 | Start | 2012-01-01 08:00:00 1 | 111 | Stop | 2012-01-01 08:59:59 1 | 999 | Start | 2012-01-01 09:00:00 1 | 999 | Stop | 2012-01-01 09:59:59 1 | 111 | Start | 2012-01-01 10:00:00 1 | 111 | Stop | 2012-01-01 10:30:00 

As you can see, each “event” consists of one or more “actions” (or, as I think, their “auxiliary events”).

I need to identify each "auxiliary event" and give it an identifier. This is what I am looking for:

  EventID | SubeventID | UserID | Action | TimeStamp --------------+----------------+------------+------------+------------------------- 1 | 1 | 111 | Start | 2012-01-01 08:00:00 1 | 1 | 111 | Stop | 2012-01-01 08:59:59 1 | 2 | 999 | Start | 2012-01-01 09:00:00 1 | 2 | 999 | Stop | 2012-01-01 09:59:59 1 | 3 | 111 | Start | 2012-01-01 10:00:00 1 | 3 | 111 | Stop | 2012-01-01 10:30:00 

I need something that can start counting, but only increase when some column has a specific value (for example, "Action" = "Start").

I am trying to use Window functions for this, but with limited success. I just can't find a solution that I feel will work ... any thoughts?

Answer 1

If you have a field that you can sort, you can use the following query (unchecked):

 SELECT sum(("Action" = 'Start')::int) OVER (PARTITION BY "EventID" ORDER BY "Timestamp" ROWS UNBOUNDED PRECEDING) FROM events 

Note that if the first SubEvent does not start with Start, it will have event ID 0, which may not be what you want.

---

You can also use COUNT() instead of SUM() :

 SELECT EventID , COUNT(CASE WHEN Action = 'Start' THEN 1 END) OVER ( PARTITION BY EventID ORDER BY TimeStamp ROWS UNBOUNDED PRECEDING ) AS SubeventID , UserID , Action FROM tableX AS t ; 

Tests in SQL script: test