Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 993FE633348 for ; Tue, 23 Jun 2009 03:56:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 04824-10 for ; Tue, 23 Jun 2009 03:56:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web27907.mail.ukl.yahoo.com (web27907.mail.ukl.yahoo.com [217.146.182.57]) by mail.postgresql.org (Postfix) with SMTP id 002576332E4 for ; Tue, 23 Jun 2009 03:56:19 -0300 (ADT) Received: (qmail 47578 invoked by uid 60001); 23 Jun 2009 06:56:17 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1245740177; bh=sM0CynRsoCxqEm7YEyFLfTPPXxNDnZWMplwwiXZtuIA=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=aBKb26Pvib+w8ntCDOyRyO4DX/P4Z3xNHkU7Qpee6hSWykrVQcvYR9W6O44JIbXPFAK6AeFlr5l3VDr0sQYTz5AJshHFvoxLWqcxMuQH2sjNplOTosKAYeoALqz1RMCyjxln1oXSft+A380CCyPOQDoDr2r0EbhzKfN0E24t/tY= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=POvFhVNammGr/IqF2Zp7UM0K7PLJqjDlr3HP1QYIJQgp64Wn1In01f/JWdSHNc6EGlWMS9zSWTxFYmR6ptKAejnZnNX1z0HMUzHfzEQYFGT9wHdmGGyz43eZn9qYYDT++qUlx9uOA5DEAJqME3N7zqZH7QpTeP0ldCBWfeqLbAw=; Message-ID: <692849.45202.qm@web27907.mail.ukl.yahoo.com> X-YMail-OSG: AZ5nhukVM1kgPjR6871qNR2JjlJzvOuwHAo8Ne3TwW2qIgaJbBTZVhUDyRSX2wSGWnb7K6_hznOxic6rzyS9fwGBUuAEKddTDmVKLvnJImkQC_61nwIrNFE.EfCnzmLsaCE1b6urEyiqWd9_99FAB2s4PpfK5IMpGcwaIDAyHUssRfVcOV8G8O_t4HR.s5_DHKMS7QwHy.c.aPevcnGaj827owJpGqjLlTl4kOWKnrZHq5MLAody.mtyRskOwFIS6B792fk2t9cPMRrMVpj87cXKOMTK0l5XZfyucVQK620WRdpMmZPB2pX66j0pon7wG4lWdPJhqcxQgiQGsX7WouI5HsvP1M4- Received: from [196.43.83.227] by web27907.mail.ukl.yahoo.com via HTTP; Tue, 23 Jun 2009 06:56:17 GMT X-Mailer: YahooMailClassic/5.4.17 YahooMailWebService/0.7.289.15 Date: Tue, 23 Jun 2009 06:56:17 +0000 (GMT) From: James Kitambara Subject: Re: Find periods for a given... action set? To: Mario Splivalo Cc: Achilleas Mantzios , pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-560638391-1245740177=:45202" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.189 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=2.188 X-Spam-Level: ** X-Archive-Number: 200906/90 X-Sequence-Number: 32695 --0-560638391-1245740177=:45202 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello Mario Splivalo, I found some solution to your question but it is not exact one. =A0 ***************************************************************************= *************** SELECT a.user_id, a.action_time- (select a2.action_time from actions a2 where a2.user_id =3D =A0=A0=A0 (select a3.user_id from actions a3 where a3.user_id=3Da.user_id a= nd a3.action_mark=3D'BEGIN' and=20 =A0=A0=A0=A0=A0=A0=A0 a3.action_time wrote: From: Mario Splivalo Subject: [SQL] Find periods for a given... action set? To: pgsql-sql@postgresql.org Date: Friday, 12 June, 2009, 1:54 PM I have a table where there are actinos for some user logged. It's part of t= he MPI system of some sort. For every user, action type and time of the act= ion is logged. There are many action types but the ones which are of intere= st to me are BEGIN and END. I need to find the durations for all the period= s between BEGINs and ENDs, for each user. Here is what the table looks like: CREATE TABLE actions (=20 =A0=A0=A0user_id integer,=20 =A0=A0=A0action_mark character varying,=20 =A0=A0=A0action_time timestamp=20 ) 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=A0 PK would be (user_id, action_time), but there are no PKs as= for now :) Some example data:=20 INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00');=20 INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22');=20 INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01');=20 INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00');=20 INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02');=20 INSERT INTO actions VALUES (1, 'END',=A0=A0=A0'2009-02-02 13:21:01');=20 INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01');=20 INSERT INTO actions VALUES (2, 'END',=A0=A0=A0'2009-02-02 16:11:21');=20 INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31');=20 INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12');=20 INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21');=20 INSERT INTO actions VALUES (2, 'END',=A0=A0=A0'2009-02-02 19:00:01');=20 INSERT INTO actions VALUES (1, 'END',=A0=A0=A0'2009-02-02 19:10:01');=20 INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01');=20 Now, for each user, i need to find all periods 'enclosed' with BEGIN/END ac= tion_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 pe= riods. 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=A0 =A0 =A0 =A0=A0=A0action_duration=20 1=A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A001:21:01=20 1=A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A000:57:40=20 2=A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A004:09:20=20 2=A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A000:48:49 User_id 3 has just the BEGIN - there is no period there, because I don't ha= ve and endpoint. Similarly, user_id 1 has BEGIN as the last action - just t= wo periods for user_id 1, because last BEGIN denotes 'period in progress'. Also, user_id 1 has following actions happened, time ordered: BEGIN, END, B= EGIN, 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 complic= ated to get what I want, but I would realy like to have it solved within pl= ain SQL :) So, if anyone has any suggestions, I would greatly appreciate them. =A0=A0=A0 Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql =0A=0A=0A --0-560638391-1245740177=:45202 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

Hello Mario Splivalo,
I found some solution to your question but it is not exact one.
 
**********************************************************************= ********************
SELECT a.user_id, a.action_time-
(select a2.action_time from action= s a2 where a2.user_id =3D
    (select a3.user_id from act= ions a3 where a3.user_id=3Da.user_id and a3.action_mark=3D'BEGIN' and
&= nbsp;       a3.action_time<a.action_time o= rder by a3.action_time DESC limit 1
      =   ) limit 1
) as "actiona _duration" from actions a where a.action_= mark=3D'END' order by a.user_id,a.action_time DESC;
 
**********************************************************************= *************************
 
Maybe you can use it as the BASE.
It only refer to the first BEGIN for each user.
If you can do some modification on that you can reach to the required = solution
Best Regards
James Kitambara
Database Administrator.

 
<= /DIV>
 
-----------------------------= ----ORGINAL MESSAGE---------------------------------------

--- On Fri, 12/6/09, Mario Splivalo <mario.splivalo@megafon.hr> = wrote:

From: Mario Splivalo <mario.splivalo@megafon.h= r>
Subject: [SQL] Find periods for a given... action set?
To: pgsq= l-sql@postgresql.org
Date: Friday, 12 June, 2009, 1:54 PM

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 t= ype and time of the action is logged. There are many action types but the o= nes which are of interest to me are BEGIN and END. I need to find the durat= ions for all the periods between BEGINs and ENDs, for each user.

Her= e is what the table looks like:

CREATE TABLE actions (
 &nb= sp; user_id integer,
   action_mark character varyi= ng,
   action_time timestamp
)

There are no P= K constraints because those columns are excerpt from a action_log table, th= ere is a message_id column which is a PK; user_id and action_mark are FKs t= o the users and actions tables. Now that I look at it, in the above table&n= bsp; 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', '200= 9-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-0= 2 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:1= 3: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');
IN= SERT 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 'enclos= ed' with BEGIN/END action_type pairs. If I have a BEGIN and no END, than th= ere 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 tw= o periods.

The results I would like to get look like this:

us= er_id         action_duration
1  &nb= sp;            01:21:01
1  &nbs= p;            00:57:40
2   = ;            04:09:20
2   =            00:48:49

User_id 3 ha= s just the BEGIN - there is no period there, because I don't have and endpo= int. 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, E= ND, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN canceled se= cond 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 S= QL :)

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

    Mike

-- Sent via pgsql-sql mailing l= ist (pgsql-sql@postgre= sql.org)
To make changes to your subscription:
http://www.postgresq= l.org/mailpref/pgsql-sql

= =0A=0A=0A=0A --0-560638391-1245740177=:45202--