MySQL Nested aggregation query with selection of specific intermediate items

user_2619658 0
Viewed 111

This is my activities table.

activities
+----+---------+----------+-----------------+
| id | user_id | activity |    log_time     |
+----+---------+----------+-----------------+
|  6 |       1 | start    | 12 Oct, 1000hrs |
|  2 |       1 | task     | 12 Oct, 1010hrs |
|  7 |       1 | task     | 12 Oct, 1040hrs |
|  3 |       1 | start    | 12 Oct, 1600hrs |
|  1 |       1 | task     | 12 Oct, 1610hrs |
|  9 |       1 | start    | 14 Oct, 0800hrs |
| 10 |       1 | start    | 16 Oct, 0900hrs |
|  4 |       1 | task     | 16 Oct, 0910hrs |
|  8 |       2 | start    | 12 Oct, 1000hrs |
|  5 |       2 | task     | 12 Oct, 1020hrs |
+----+---------+----------+-----------------+

and I need the total time spent by the user across all sessions. Each session happens within a day and includes a 'start' and multiple 'tasks' (before the next session is initiated with a 'start'). A session duration = last task - start [the timestamp difference]

output
+---------+------------+------------------------------------------------+
| user_id | total_time |       This is explanation (not a column)       |
+---------+------------+------------------------------------------------+
|       1 |         60 | 12_Oct[40+10] + 14_Oct[0] + 16_Oct[10] = 60min |
|       2 |         20 | 12_Oct[20]  = 20min                            |
+---------+------------+------------------------------------------------+

I am unable to figure out how to get the last task in a session. I have tried the basic aggregation and join queries - but it doesn't work.

As an approach, what I think I really need is to get the last column (below / session_group) somehow, and then I can aggregate and get the difference between max/min timestamp.

+----+---------+----------+-----------------+---------------+
| id | user_id | activity |    log_time     | session_group |
+----+---------+----------+-----------------+---------------+
|  6 |       1 | start    | 12 Oct, 1000hrs |             1 |
|  2 |       1 | task     | 12 Oct, 1010hrs |             1 |
|  7 |       1 | task     | 12 Oct, 1040hrs |             1 |
|  3 |       1 | start    | 12 Oct, 1600hrs |             2 |
|  1 |       1 | task     | 12 Oct, 1610hrs |             2 |
|  9 |       1 | start    | 14 Oct, 0800hrs |             3 |
| 10 |       1 | start    | 16 Oct, 0900hrs |             4 |
|  4 |       1 | task     | 16 Oct, 0910hrs |             4 |
|  8 |       2 | start    | 12 Oct, 1000hrs |             5 |
|  5 |       2 | task     | 12 Oct, 1020hrs |             5 |
+----+---------+----------+-----------------+---------------+

Please let me know if it is even possible to get the desired output via sql (MySQL) and how to go about it ? Or is it necessary to loop through the data via say Javascript ?

Below is the MySQL query for the tables:

create table activities (
  id INT NOT NULL, 
  user_id INT NULL, 
  activity VARCHAR(45), 
  log_time DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY(id))
 ENGINE = InnoDB;    
 
insert into activities
    (id, user_id, activity, log_time) 
values
    (6,1,'start', '2021-10-12 10:00:00'), 
    (2,1,'task' , '2021-10-12 10:10:00'), 
    (7,1,'task' , '2021-10-12 10:40:00'), 
    (3,1,'start', '2021-10-12 16:00:00'), 
    (1,1,'task',  '2021-10-12 16:10:00'), 
    (9,1,'task',  '2021-10-14 08:00:00'), 
    (10,1,'start','2021-10-16 09:00:00'), 
    (4,1,'task',  '2021-10-16 09:10:00'), 
    (8,2,'start', '2021-10-12 10:00:00'), 
    (5,2,'task',  '2021-10-12 10:20:00');
mariadb-10.5 mariadb window-functions sql
4 Answers

This might work. Find all start-task pairs and take the maximum difference in minutes, then sum up the minutes for each user.

select user_id, sum(minutes) minutes
from (
    select a.user_id, a.id, max(timestampdiff(minute, a.log_time, b.log_time)) minutes
    from activities a 
    join activities b on a.user_id = b.user_id and a.log_time < b.log_time 
    where a.activity = 'start'
    and b.activity = 'task'
    and date(a.log_time) = date(b.log_time)
    and not exists (
      select 1
      from activities c 
      where c.user_id = a.user_id
      and a.activity = c.activity
      and c.log_time > a.log_time
      and c.log_time < b.log_time
    )
    group by a.user_id, a.id   
) f
group by user_id

or using window functions

with combo as
(
select user_id, activity, log_time,
 lag(activity) over( partition by user_id  order by log_time) last_activity,
 lag(log_time) over( partition by user_id  order by log_time) last_log_time
from activities
)
select user_id, sum(timestampdiff(minute, last_log_time, log_time))
from combo
where activity = 'task'
and date(log_time) = date(last_log_time)
group by user_id
user_170161440

You can use SUM() window function to assign a number to each session and then aggregate:

SELECT DISTINCT user_id,
       SUM(TIMESTAMPDIFF(MINUTE, MIN(log_time), MAX(log_time))) OVER (PARTITION BY user_id) total_time 
FROM (
  SELECT *, SUM(activity = 'start') OVER (PARTITION BY user_id, DATE(log_time) ORDER BY log_time) grp
  FROM activities
) t
WHERE grp > 0
GROUP BY user_id, DATE(log_time), grp;

See the demo.

user_104988280

Schema and insert statements:

 create table activities (
   id INT NOT NULL, 
   user_id INT NULL, 
   activity VARCHAR(45), 
   log_time DATETIME NOT NULL DEFAULT NOW(),
   PRIMARY KEY(id))
  ENGINE = InnoDB;    
  
 insert into activities
     (id, user_id, activity, log_time) 
 values
     (6,1,'start', '2021-10-12 10:00:00'), 
     (2,1,'task' , '2021-10-12 10:10:00'), 
     (7,1,'task' , '2021-10-12 10:40:00'), 
     (3,1,'start', '2021-10-12 16:00:00'), 
     (1,1,'task',  '2021-10-12 16:10:00'), 
     (9,1,'start',  '2021-10-14 08:00:00'), 
     (10,1,'start','2021-10-16 09:00:00'), 
     (4,1,'task',  '2021-10-16 09:10:00'), 
     (8,2,'start', '2021-10-12 10:00:00'), 
     (5,2,'task',  '2021-10-12 10:20:00');

Query:

 with tasks as
 (
 SELECT
   user_id, partition_condition ,TIMESTAMPDIFF(minute,min(log_time),max(log_time))time_diff
 FROM (
   SELECT
     id, user_id, activity, log_time,
     sum(case when activity='start' then 1 else 0 end) over (partition by user_id order by log_time) as partition_condition
 
   FROM activities
 
 ) as tasks
 group by user_id, partition_condition
 )
 select user_id,sum(time_diff)total_time from tasks
 group by user_id

Output:

user_id total_time
1 60
2 20

db<>fiddle here

user_86516010

You can proceed for each user and day along with LAG() window function in order to compute the minute differences for each row with task activity such as

SELECT user_id, 
       SUM( TIMESTAMPDIFF(MINUTE, COALESCE( lg, log_time ), log_time ) ) AS total_time
  FROM (SELECT LAG(log_time) OVER (PARTITION BY user_id, DATE(log_time) 
                                      ORDER BY log_time) AS lg, 
               a.*
          FROM activities AS a
         ORDER BY log_time) AS aa
  WHERE activity != 'start'      
  GROUP BY user_id

Demo

user_58413060
Related