Find periods for a given... action set?

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: Find periods for a given... action set?
Date: 2009-06-12 12:54:29
Message-ID: 4A325005.1000908@megafon.hr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table where there are actinos for some user logged. It's part
of the MPI system of some sort. For every user, action type and time of
the action is logged. There are many action types but the ones which are
of interest to me are BEGIN and END. I need to find the durations for
all the periods between BEGINs and ENDs, for each user.

Here is what the table looks like:

CREATE TABLE actions (

user_id integer,

action_mark character varying,

action_time timestamp

)

There are no PK constraints because those columns are excerpt from a
action_log table, there is a message_id column which is a PK; user_id
and action_mark are FKs to the users and actions tables. Now that I look
at it, in the above table PK would be (user_id, action_time), but there
are no PKs as for now :)

Some example data:

INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00');

INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22');

INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01');

INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00');

INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02');

INSERT INTO actions VALUES (1, 'END', '2009-02-02 13:21:01');

INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01');

INSERT INTO actions VALUES (2, 'END', '2009-02-02 16:11:21');

INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31');

INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12');

INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21');

INSERT INTO actions VALUES (2, 'END', '2009-02-02 19:00:01');

INSERT INTO actions VALUES (1, 'END', '2009-02-02 19:10:01');

INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01');

Now, for each user, i need to find all periods 'enclosed' with BEGIN/END
action_type pairs. If I have a BEGIN and no END, than there is no
period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there
are only two periods.
Also, if there are consecutive BEGINS, only the last one counts:
BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods.

The results I would like to get look like this:

user_id action_duration

1 01:21:01

1 00:57:40

2 04:09:20

2 00:48:49

User_id 3 has just the BEGIN - there is no period there, because I don't
have and endpoint. Similarly, user_id 1 has BEGIN as the last action -
just two periods for user_id 1, because last BEGIN denotes 'period in
progress'.

Also, user_id 1 has following actions happened, time ordered: BEGIN,
END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN
canceled second BEGIN and all the actions between second and third BEGIN.

Now, using some imperative Python, Perl, C, whatever, it's not that
complicated to get what I want, but I would realy like to have it solved
within plain SQL :)

So, if anyone has any suggestions, I would greatly appreciate them.

Mike

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Achilleas Mantzios 2009-06-12 13:42:57 Re: Find periods for a given... action set?
Previous Message Jasen Betts 2009-06-12 12:33:28 Re: setting the where clause