Re: [GENERAL] time series data

[Clifford Snow]

I have a stream that updates every minute with a trigger that updates
another table with information from the stream. That way I'm constantly
updated with no need to run a script to update before I want a report.

Clifford

On Sun, Oct 1, 2017 at 10:08 AM, Melvin Davidson 
wrote:

>
>
> On Sun, Oct 1, 2017 at 4:17 AM, Khalil Khamlichi <
> khamlichi.kha...@gmail.com> wrote:
>
>> Hi everyone,
>>
>> I have a data stream of a call center application coming in  to postgres
>> in this format :
>>
>> user_name, user_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: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'
>> 'user1', 'paused', '2017-01-01 10:20:00'
>> ...
>> ...
>>
>> so as you see each new insert of an "event" is in fact the start_time of
>> that event and also the end_time of the previous one so should be used to
>> calculate the duration of this previous one.
>>
>> What is the best way to get user_status statistics like total duration,
>> frequency, avg ...etc , does any body have an experience with this sort of
>> data streams ?
>>
>>
>> Thanks in advance.
>>
>
> Just a suggestion, but here is what I would do.
> First, create your tables similar to as follows
>
> CREATE TABLE status
> (
>  call_statusvarchar(10) NOT NULL,
>  CONSTRAINT status_pk PRIMARY KEY (call_status)
> );
>
> INSERT INTO status
> (call_status)
> VALUES
> ('ready'),
> ('talking'),
> ('after_call');
>
> CREATE TABLE user_sessions
> (
>  usernamenameNOT NULL,
>  session_idbigintNOT NULL,
>  call_statusvarchar(10) NOT NULL,
>  call_timetimestamp NOT NULL,
>  CONSTRAINT user_sessions_pk PRIMARY KEY (username,
> session_id,call_status),
>  CONSTRAINT user_sessions_fk_status FOREIGN KEY (call_status)
> REFERENCES status(call_status)
> );
>
> Next, you will need to generate a unique session_id for each
> user, but only for when call_status is 'ready'. So probably
> a table of the form:
>
> CREATE TABLE current_session
> (
> usernamenameNOT NULL,
> session_idserialNOT NULL,
> CONSTRAINT current_session_pk PRIMARY KEY (username)
> );
>
> Then all you need to do is:
> 1. Update current_session and get the new session_id each time a user
> connects (call_status = 'ready'.
>Probably best to use a BEFORE trigger to do this, but you will need to
> code it yourself.
>
> 2. You can then do
>
> SELECT username,
>age ( (SELECT call_time FROM current_session WHERE call_status =
> 'talking'),
>   ( SELECT call_time FROM current_session WHERE call_status =
> 'after_call')
>) as duration
>   FROM user_sessions
>  WHERE username = '*actual_user_name*'
>AND session_id =  *actual_session_id*;
>
> You can use similar queries for avg and frequency.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Re: [GENERAL] Help with Trigger

[Clifford Snow]

Thank you for your suggestion which solved the problem. Much better
solution that what I was trying to accomplish. Much smaller table to query
since it only has one entry per user.

Clifford

On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 12/28/2016 07:06 PM, Clifford Snow wrote:
>
>> I'm trying to write a trigger (my first) to update another table if the
>> user_id is new. But I'm getting a index exception that the user_id
>>
>
> What is the actual error message?
>
> already exists. I'm picking up data from another feed which gives
>> provides me with changes to the main database.
>>
>> what I have is
>>
>> CREATE OR REPLACE FUNCTION add_new_user()
>> RETURNS TRIGGER AS
>> $BODY$
>> DECLARE
>> commits RECORD;
>> BEGIN
>> SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
>>
>
> In the above you are checking whether the changes table has the user_id
> and if does not then creating a new user in the user table below. Not sure
> how they are related, but from the description of the error it would seem
> they are not that tightly coupled. In other words just because the user_id
> does not exist in changes does not ensure it also absent from the table
> user. Off the top of head I would say the below might be a better query:
>
> SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;
>
> Though it would help the debugging process if you showed the complete
> schema for both the changes and user tables.
>
>
> IF NOT FOUND
>> THEN
>> INSERT INTO user (user_name, user_id, change_id,
>> created_date)
>> VALUES(NEW.user_name, NEW.user_id,
>> NEW.change_id, NEW.created_date);
>> END IF;
>> RETURN NEW;
>> END;
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER add_new_user_trigger
>> BEFORE INSERT ON changes
>> FOR EACH ROW
>> EXECUTE PROCEDURE add_new_user();
>>
>> I hoping for some recommendations on how to fix or at where I'm going
>> wrong.
>>
>> Thanks,
>> Clifford
>>
>>
>> --
>> @osm_seattle
>> osm_seattle.snowandsnow.us <http://osm_seattle.snowandsnow.us>
>> OpenStreetMap: Maps with a human touch
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

[GENERAL] Help with Trigger

[Clifford Snow]

I'm trying to write a trigger (my first) to update another table if the
user_id is new. But I'm getting a index exception that the user_id already
exists. I'm picking up data from another feed which gives provides me with
changes to the main database.

what I have is

CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id,
created_date)
VALUES(NEW.user_name, NEW.user_id, NEW.change_id,
NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();

I hoping for some recommendations on how to fix or at where I'm going wrong.

Thanks,
Clifford


-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch