특정 중간 항목을 선택한 MySQL 중첩 집계 쿼리

특정 중간 항목을 선택한 MySQL 중첩 집계 쿼리

이건 내 거야activities테이블.

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 |
+----+---------+----------+-----------------+

모든 세션에서 사용자가 소비한 총 시간이 필요합니다.각 세션은 하루 이내에 이루어지며, '시작'과 여러 '작업'이 포함됩니다(다음 세션이 '시작'으로 시작되기 전).세션 기간 = 마지막 작업 - 시작 [타임스탬프 차이]

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                            |
+---------+------------+------------------------------------------------+

세션에서 마지막 작업을 얻는 방법을 찾을 수 없습니다.기본적인 집약 및 가입 쿼리를 시도했지만 작동하지 않습니다.

접근법으로는 마지막 열(/session_group 아래)을 가져와 max/min 타임스탬프의 차이를 파악할 수 있습니다.

+----+---------+----------+-----------------+---------------+
| 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 |
+----+---------+----------+-----------------+---------------+

sql(MySQL)을 통해 원하는 출력을 얻을 수 있는지, 그 방법을 알려주세요.아니면 say Javascript를 통해 데이터를 루프할 필요가 있습니까?

다음은 테이블에 대한 MySQL 쿼리입니다.

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');

사용할 수 있습니다.SUM()window 함수를 사용하여 각 세션에 번호를 할당하고 집계합니다.

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;

데모를 참조해 주세요.

이거 먹힐지도 몰라.모든 시작-작업 쌍을 찾아 최대 차이를 분 단위로 취한 다음 각 사용자의 시간을 합산합니다.

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

또는 창 기능을 사용합니다.

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

LAG() 창 함수와 함께 사용자 및 요일별로 진행하면 다음과 같은 작업 액티비티를 가진 각 행의 미세한 차이를 계산할 수 있습니다.

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

스키마 및 삽입문:

 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');

쿼리:

 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

출력:

user_id	total_time(합계_시간)
1	60
2	20

db <>여기에 추가

언급URL : https://stackoverflow.com/questions/69787415/mysql-nested-aggregation-query-with-selection-of-specific-intermediate-items