pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Find periods for a given... action set?
3+ messages / 3 participants
[nested] [flat]

* Find periods for a given... action set?
@ 2009-06-12 12:54  Mario Splivalo <mario.splivalo@megafon.hr>
  0 siblings, 1 reply; 3+ messages in thread

From: Mario Splivalo @ 2009-06-12 12:54 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Find periods for a given... action set?
@ 2009-06-12 13:42  Achilleas Mantzios <achill@matrix.gatewaynet.com>
  parent: Mario Splivalo <mario.splivalo@megafon.hr>
  0 siblings, 0 replies; 3+ messages in thread

From: Achilleas Mantzios @ 2009-06-12 13:42 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Mario Splivalo <mario.splivalo@megafon.hr>

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.oid from actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and 
		a3.action_time<a.action_time order by a3.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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Find periods for a given... action set?
@ 2009-06-23 06:56  James Kitambara <jameskitambara@yahoo.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: James Kitambara @ 2009-06-23 06:56 UTC (permalink / raw)
  To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql


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 actions a2 where a2.user_id =
    (select a3.user_id from actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and 
        a3.action_time<a.action_time order by a3.action_time DESC limit 1
        ) limit 1
) as "actiona _duration" from actions a where a.action_mark='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.

 

 ---------------------------------ORGINAL MESSAGE---------------------------------------

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


From: Mario Splivalo <mario.splivalo@megafon.hr>
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 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

-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



      

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2009-06-23 06:56 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-12 12:54 Find periods for a given... action set? Mario Splivalo <mario.splivalo@megafon.hr>
2009-06-12 13:42 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-06-23 06:56 Re: Find periods for a given... action set? James Kitambara <jameskitambara@yahoo.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox