Re: Find periods for a given... action set? - Mailing list pgsql-sql

Dobro Vece,
smth like:

SELECT a.user_id,a.action_mark,a.action_time,a.action_time-(select a2.action_time from actions a2 where a2.oid=(select
a3.oidfrom actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and     a3.action_time<a.action_time order
bya3.action_time DESC limit 1       )) from actions a where a.action_mark='END' order by a.user_id,a.action_time DESC; 


Στις Friday 12 June 2009 15:54:29 ο/η Mario Splivalo έγραψε:
> 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.
>

Smth

>     Mike
>



--
Achilleas Mantzios