MySQL嵌套聚合查询实现用户会话分组及累计时长统计方法

这个需求完全可以直接通过 MySQL 语句实现，不需要额外使用 JavaScript 等其他语言遍历处理，实现逻辑和你设想的会话分组思路一致。

MySQL 8.0+ 实现方案（推荐） #

MySQL 8.0 支持窗口函数，写法更简洁易读：

WITH session_mark AS (
  -- 第一步：给每条数据打会话分组标记
  SELECT 
    *,
    SUM(CASE WHEN activity = 'start' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY log_time) AS session_group
  FROM activities
),
session_calc AS (
  -- 第二步：按会话分组计算每个会话的时长
  SELECT 
    user_id,
    session_group,
    -- 无task的会话max和min时间相同，差值为0，符合规则
    TIMESTAMPDIFF(MINUTE, MIN(log_time), MAX(log_time)) AS session_duration
  FROM session_mark
  GROUP BY user_id, session_group
)
-- 第三步：按用户汇总总时长
SELECT 
  user_id,
  SUM(session_duration) AS total_time
FROM session_calc
GROUP BY user_id;

MySQL 5.x 兼容实现方案 #

如果使用不支持窗口函数的低版本 MySQL，可以用用户变量实现相同逻辑：

SELECT 
  user_id,
  SUM(session_duration) AS total_time
FROM (
  SELECT 
    user_id,
    session_group,
    TIMESTAMPDIFF(MINUTE, MIN(log_time), MAX(log_time)) AS session_duration
  FROM (
    SELECT 
      *,
      @grp := IF(@current_uid = user_id, @grp + (activity = 'start'), 1) AS session_group,
      @current_uid := user_id
    FROM activities
    CROSS JOIN (SELECT @current_uid := 0, @grp := 0) AS init_vars
    ORDER BY user_id, log_time
  ) AS t1
  GROUP BY user_id, session_group
) AS t2
GROUP BY user_id;

输出结果 #

运行上述SQL得到的结果和你预期完全一致：

注意：你提供的测试INSERT语句中，id=9的记录activity值误写为task，和你示例数据中的start不符，需要先修改该条记录的activity为start，才能得到上述预期结果。

内容的提问来源于stack exchange，提问作者Kaya Toast