From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Find periods for a given... action set?
Date: Fri, 12 Jun 2009 16:42:57 +0300
User-Agent: KMail/1.9.7
Cc: Mario Splivalo <mario.splivalo@megafon.hr>
References: <4A325005.1000908@megafon.hr>
In-Reply-To: <4A325005.1000908@megafon.hr>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200906121642.57547.achill@matrix.gatewaynet.com>

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=3D
	(select a3.oid from actions a3 where a3.user_id=3Da.user_id and a3.action_=
mark=3D'BEGIN' and=20
		a3.action_time<a.action_time order by a3.action_time DESC limit 1
        )
 ) from actions a where a.action_mark=3D'END' order by a.user_id,a.action_t=
ime DESC;


=CE=A3=CF=84=CE=B9=CF=82 Friday 12 June 2009 15:54:29 =CE=BF/=CE=B7 Mario S=
plivalo =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> I have a table where there are actinos for some user logged. It's part=20
> of the MPI system of some sort. For every user, action type and time of=20
> the action is logged. There are many action types but the ones which are=
=20
> of interest to me are BEGIN and END. I need to find the durations for=20
> all the periods between BEGINs and ENDs, for each user.
>=20
> Here is what the table looks like:
>=20
> CREATE TABLE actions (=20
>=20
>     user_id integer,=20
>=20
>     action_mark character varying,=20
>=20
>     action_time timestamp=20
>=20
> )
>=20
> There are no PK constraints because those columns are excerpt from a=20
> action_log table, there is a message_id column which is a PK; user_id=20
> and action_mark are FKs to the users and actions tables. Now that I look=
=20
> at it, in the above table  PK would be (user_id, action_time), but there=
=20
> are no PKs as for now :)
>=20
> Some example data:=20
>=20
> =20
>=20
> INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00');=20
>=20
> INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22');=20
>=20
> INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01');=20
>=20
> INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00');=20
>=20
> INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02');=20
>=20
> INSERT INTO actions VALUES (1, 'END',   '2009-02-02 13:21:01');=20
>=20
> INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01');=20
>=20
> INSERT INTO actions VALUES (2, 'END',   '2009-02-02 16:11:21');=20
>=20
> INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31');=20
>=20
> INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12');=20
>=20
> INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21');=20
>=20
> INSERT INTO actions VALUES (2, 'END',   '2009-02-02 19:00:01');=20
>=20
> INSERT INTO actions VALUES (1, 'END',   '2009-02-02 19:10:01');=20
>=20
> INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01');=20
>=20
>=20
> Now, for each user, i need to find all periods 'enclosed' with BEGIN/END=
=20
> action_type pairs. If I have a BEGIN and no END, than there is no=20
> period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there=20
> are only two periods.
> Also, if there are consecutive BEGINS, only the last one counts:=20
> BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods.
>=20
> The results I would like to get look like this:
>=20
> user_id         action_duration=20
>=20
> 1               01:21:01=20
>=20
> 1               00:57:40=20
>=20
> 2               04:09:20=20
>=20
> 2               00:48:49
>=20
> User_id 3 has just the BEGIN - there is no period there, because I don't=
=20
> have and endpoint. Similarly, user_id 1 has BEGIN as the last action -=20
> just two periods for user_id 1, because last BEGIN denotes 'period in=20
> progress'.
>=20
> Also, user_id 1 has following actions happened, time ordered: BEGIN,=20
> END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN=20
> canceled second BEGIN and all the actions between second and third BEGIN.
>=20
> Now, using some imperative Python, Perl, C, whatever, it's not that=20
> complicated to get what I want, but I would realy like to have it solved=
=20
> within plain SQL :)
>=20
> So, if anyone has any suggestions, I would greatly appreciate them.
>=20

Smth

> 	Mike
>=20


=2D-=20
Achilleas Mantzios