Re: [GENERAL] time series data - Mailing list pgsql-general

On Mon, Oct 2, 2017 at 10:27 AM, Khalil Khamlichi
<khamlichi.khalil@gmail.com> wrote:
> we have records like this
>
> ccdb1=# select user_name, agent_status, event_time from cc_events ;
>
>  user_name | agent_status |     event_time
> -----------+--------------+---------------------
>  user1     | ready        | 2017-01-01 10:00:00
>  user1     | talking      | 2017-01-01 10:02:00
>  user1     | after_call   | 2017-01-01 10:08:00
>  user1     | ready        | 2017-01-01 10:10:00
>  user1     | talking      | 2017-01-01 10:12:00
>  user1     | after_call   | 2017-01-01 10:15:00
>  user1     | paused       | 2017-01-01 10:17:00
>  user1     | ready        | 2017-01-01 10:25:00
>  user1     | talking      | 2017-01-01 10:26:00
> (9 rows)
>
>
> so user1 was READY at  2017-01-01 10:00:00 then he received a call
> that he attended at 2017-01-01 10:02:00 and so on ...
> so user1 was ready for 2 minutes, then he was talking for 6 minutes
> then he was in after_call (doing after call work)  for 2 minutes and
> this is the kind of information we want to query.
>
> my solution so far that I came with, is in my table I have 1 more
> field : end_time
> so when an event comes in and before the insert I do :
> update cc_events set end_time = current_timestamp  where user_name =
> 'user_of_event' and end_time is null;
>
> then I insert new event leaving the end_time as null so that next
> event will update it and so on.
>
> its working fine, I have the start and end times for each event, its
> not too painful to query (sum(end-start) while grouping by user_name,
> agent_status), but its one more update on the table and also limited
> in what you can query about,
>
> I know this must be a common problem in every software that deals with
> events, so I suppose something is already built-in in postgres to deal
> with it.

Khalil, changing your schema is one solution with certain benefits -
but it's definitely not necessary when you have the power of
PostgreSQL at your fingertips.  You can solve your problem without
changing anything at all.  :)

All you need is a window function:
https://www.postgresql.org/docs/9.6/static/tutorial-window.html

Here's an example which I just now tested on 9.6.3, 9.5.7, 9.4.12 and
9.3.17 (all the latest minors currently supported on RDS).  You can
try these queries on your own system; they should work anywhere.

First, I added a second user to your data set to make sure we were
handling that case correctly.

==========
create table cc_events (user_name varchar(8), agent_status
varchar(20), event_time timestamp);

insert into cc_events values
('user1', 'ready', '2017-01-01 10:00:00'),
('user1', 'talking', '2017-01-01 10:02:00'),
('user2', 'ready', '2017-01-01 10:04:00'),
('user2', 'talking', '2017-01-01 10:05:00'),
('user1', 'after_call', '2017-01-01 10:07:00'),
('user1', 'ready', '2017-01-01 10:08:00'),
('user1', 'talking', '2017-01-01 10:10:00'),
('user1', 'after_call', '2017-01-01 10:15:00'),
('user2', 'after_call', '2017-01-01 10:18:00'),
('user1', 'paused', '2017-01-01 10:20:00'),
('user2', 'paused', '2017-01-01 10:21:00');

select * from cc_events order by user_name, event_time;

==========
here's a basic window function in action:

==========
select user_name, agent_status, event_time, lead(event_time) over (partition by user_name order by event_time)
next_event_time
from cc_events order by event_time;
user_name | agent_status |     event_time      |   next_event_time
-----------+--------------+---------------------+---------------------user1     | ready        | 2017-01-01 10:00:00 |
2017-01-0110:02:00user1     | talking      | 2017-01-01 10:02:00 | 2017-01-01 10:07:00user2     | ready        |
2017-01-0110:04:00 | 2017-01-01 10:05:00user2     | talking      | 2017-01-01 10:05:00 | 2017-01-01 10:18:00user1     |
after_call  | 2017-01-01 10:07:00 | 2017-01-01 10:08:00user1     | ready        | 2017-01-01 10:08:00 | 2017-01-01
10:10:00user1    | talking      | 2017-01-01 10:10:00 | 2017-01-01 10:15:00user1     | after_call   | 2017-01-01
10:15:00| 2017-01-01 10:20:00user2     | after_call   | 2017-01-01 10:18:00 | 2017-01-01 10:21:00user1     | paused
 | 2017-01-01 10:20:00 |user2     | paused       | 2017-01-01 10:21:00 |
 

==========
and now we just add one more column which does the subtraction to
calculate the duration:

==========
select user_name, agent_status, event_time, lead(event_time) over (partition by user_name order by event_time)
next_event_time, (lead(event_time) over (partition by user_name order by event_time))
- event_time as duration
from cc_events order by event_time;

user_name | agent_status |     event_time      |   next_event_time   | duration
-----------+--------------+---------------------+---------------------+----------user1     | ready        | 2017-01-01
10:00:00| 2017-01-01 10:02:00 | 00:02:00user1     | talking      | 2017-01-01 10:02:00 | 2017-01-01 10:07:00 |
00:05:00user2    | ready        | 2017-01-01 10:04:00 | 2017-01-01 10:05:00 | 00:01:00user2     | talking      |
2017-01-0110:05:00 | 2017-01-01 10:18:00 | 00:13:00user1     | after_call   | 2017-01-01 10:07:00 | 2017-01-01 10:08:00
|00:01:00user1     | ready        | 2017-01-01 10:08:00 | 2017-01-01 10:10:00 | 00:02:00user1     | talking      |
2017-01-0110:10:00 | 2017-01-01 10:15:00 | 00:05:00user1     | after_call   | 2017-01-01 10:15:00 | 2017-01-01 10:20:00
|00:05:00user2     | after_call   | 2017-01-01 10:18:00 | 2017-01-01 10:21:00 | 00:03:00user1     | paused       |
2017-01-0110:20:00 |                     |user2     | paused       | 2017-01-01 10:21:00 |                     |
 

==========
it might also be convenient to wrap the window function in a common
table expression

https://www.postgresql.org/docs/9.6/static/queries-with.html

==========
with calculate_next_events as ( select user_name, agent_status, event_time,   lead(event_time) over (partition by
user_nameorder by event_time)
 
next_event_time from cc_events order by event_time
)
select user_name, agent_status, next_event_time-event_time duration
from calculate_next_events order by event_time;

==========
Finally, if you really want to supercharge this and power-up even
more, besides temporal databases you might check out this recent blog
post about implementing a state machine in postgresql... it's really
interesting and closely related to what you're solving.

https://felixge.de/2017/07/27/implementing-state-machines-in-postgresql.html

Hope this is helpful. Great to see that you're working on PostgreSQL -
it's a powerful engine to build with!

-Jeremy

-- 
http://about.me/jeremy_schneider


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