-1

I would like to know how I could make a sum of (date row 0 - date row 1) + (date row 2 - date row 3) and so on... group by userId.

Please refer to the screenshot below, I want to sum the time between every APP_OPENED and APP_QUIT event for EACH different idUser that logs into the app.

enter image description here

Result must be something like :

idUser - diffTime

2cf3... - 25 seconds

504... - 50 secondes

1
create table events (event varchar(30), content json, event_date timestamp);
insert into events values
('APP_OPENED',          '{"connected":true,"idUser":"1"}', '2017-04-26 12:00:58.176'),
('APP_QUIT',            '{"connected":true,"idUser":"1"}', '2017-04-26 12:01:08.151'),
('APP_OPENED',          '{"connected":true,"idUser":"2"}', '2017-04-26 12:01:11.595'),
('APP_QUIT',            '{"connected":true,"idUser":"2"}', '2017-04-26 12:01:28.144'),
('APP_OPENED',          '{"connected":true,"idUser":"3"}', '2017-04-26 12:01:34.51'),
('APP_QUIT',            '{"connected":true,"idUser":"3"}', '2017-04-26 12:01:40.974'),
('APP_OPENED',          '{"connected":true,"idUser":"4"}', '2017-04-26 12:14:30.689'),
('APP_USER_LOGGING_IN', '{"userId":"5"}',                  '2017-04-26 12:16:41.905'),
('APP_QUIT',            '{"connected":true,"idUser":"5"}', '2017-04-26 12:16:49.287'),
('APP_OPENED',          '{"connected":true,"idUser":"6"}', '2017-04-26 12:16:51.115'),
('APP_QUIT',            '{"connected":true,"idUser":"6"}', '2017-04-26 12:17:26.866');

According to you question I've considered that each action if formed by two events:

(APP_OPENED & APP_QUIT) or (APP_USER_LOGGING_IN & APP_QUIT)

The first step is to flag one reset point for every APP_OPENED or APP_USER_LOGGING_IN, that is the event that starts one event, and then assign a different group to every couple of events.

--= Set reset points
with setReset as 
(
    select   event, 
             case when content->>'idUser' is null then content->>'userId' else content->>'idUser' end userId,
             event_date,
             case when event <> 'APP_QUIT' then 1 end reset_point
    from     events
    order by event_date
)
    --= Generates groups
    , setGrp as
    (
        select   event, userId, event_date, 
                 count(reset_point) over (order by event_date) grp
        from     setReset
        order by event_date
    )
    select * from setGrp;
event               | userid | event_date              | grp
:------------------ | :----- | :---------------------- | --:
APP_OPENED          | 1      | 2017-04-26 12:00:58.176 |   1
APP_QUIT            | 1      | 2017-04-26 12:01:08.151 |   1
APP_OPENED          | 2      | 2017-04-26 12:01:11.595 |   2
APP_QUIT            | 2      | 2017-04-26 12:01:28.144 |   2
APP_OPENED          | 3      | 2017-04-26 12:01:34.51  |   3
APP_QUIT            | 3      | 2017-04-26 12:01:40.974 |   3
APP_OPENED          | 4      | 2017-04-26 12:14:30.689 |   4
APP_USER_LOGGING_IN | 5      | 2017-04-26 12:16:41.905 |   5
APP_QUIT            | 5      | 2017-04-26 12:16:49.287 |   5
APP_OPENED          | 6      | 2017-04-26 12:16:51.115 |   6
APP_QUIT            | 6      | 2017-04-26 12:17:26.866 |   6

Finally you can get time difference of each group:

--= Set reset points
with setReset as 
(
    select   event, 
             case when content->>'idUser' is null then content->>'userId' else content->>'idUser' end userId,
             event_date,
             case when event <> 'APP_QUIT' then 1 end reset_point
    from     events
    order by event_date
)
    --= Generates groups
    , setGrp as
    (
        select   event, userId, event_date, 
                 count(reset_point) over (order by event_date) grp
        from     setReset
        order by event_date
    )
    ---= Calculate datediff of each group
    select userid, extract(epoch from(max(event_date) - min(event_date))) 
    from   setGrp
    group by grp, userId
    having   count(grp) > 1
    order by grp;
userid | date_part
:----- | :--------
1      | 9.975    
2      | 16.549   
3      | 6.464    
5      | 7.382    
6      | 35.751   

dbfiddle here

1

UPDATE this doesn't work, you can try some of the code and make it work, but as it is now it is just substracting one row time from the previous row time even if previous was a QUIT event, use above instead. This works but is extremely slow , so there must be a better way, maybe you can figure out from here:

schema:

CREATE TABLE MyTable
    (event varchar(20), content varchar(85), mydate timestamp WITHOUT TIME ZONE)
;

INSERT INTO Mytable
    (event, content, mydate)
VALUES
    ('APP_OPENED', '{"connected":true,"idUser":"2cf312345678",}','2017-04-26 12:00:58.176'),
    ('APP_QUIT', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:01:08.151'),
    ('APP_OPENED', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:01:11.595'),
    ('APP_QUIT', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:01:28.144'),
    ('APP_OPENED', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:01:34.51'),
    ('APP_QUIT', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:01:40.974'),
    ('APP_OPENED', '{"connected":true,"idUser":"2cf312345678"}','2017-04-26 12:14:30.689'),
    ('APP_USER_LOGGING_IN', '{"connected":true,"idUser":"504312345678"}','2017-04-26 12:16:41.905'),
    ('APP_QUIT', '{"connected":true,"idUser":"504312345678"}','2017-04-26 12:16:49.287'),
    ('APP_OPENED', '{"connected":true,"idUser":"504312345678"}','2017-04-26 12:16:51.115'),
    ('APP_OPENED', '{"connected":true,"idUser":"504312345678"}','2017-04-26 12:17:26.866'),
    ('APP_QUIT', '{"connected":true,"idUser":"504312345678"}','2017-04-26 12:01:40.974');

=================================

select idUser, sum(difftime) from
(
SELECT split_part(content, '"', 6) IdUser,

(
        (
            DATE_PART('day', mydate::TIMESTAMP - lag(mydate) OVER (
                    ORDER BY mydate
                        ,split_part(content, '"', 6)
                    )::TIMESTAMP) * 24 + DATE_PART('hour', mydate::TIMESTAMP - lag(mydate) OVER (
                    ORDER BY mydate
                        ,split_part(content, '"', 6)
                    )::TIMESTAMP)
            ) * 60 + DATE_PART('minute', mydate::TIMESTAMP - lag(mydate) OVER (
                ORDER BY mydate
                    ,split_part(content, '"', 6)
                )::TIMESTAMP)
        ) * 60 + DATE_PART('second', mydate::TIMESTAMP - lag(mydate) OVER (
            ORDER BY mydate
                ,split_part(content, '"', 6)
            )::TIMESTAMP)  diffTime
FROM mytable
WHERE event IN (
        'APP_OPENED'
        ,'APP_QUIT'
        )

ORDER BY split_part(content, '"', 6)

) results
group by idUser

tested in sqlfiddle with postgreSQL 9.3 engine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.