计算用户各访问会话总活动时长的SQL报错问题

首先，你的SQL报错原因是不能在聚合函数（SUM）内部嵌套另一个聚合函数（MIN/MAX），错误信息翻译为：

无法对包含聚合函数或子查询的表达式执行聚合函数。

正确的做法是分两步计算：

1. 先按user_id和visit_id分组，计算每个会话的活动时长（该会话的最早访问时间与最晚活动时间的差值）
2. 再按user_id分组，将每个用户的所有会话时长求和，得到总活动时长

正确SQL查询语句 #

-- 第一步：计算每个用户每个会话的时长
WITH session_durations AS (
    SELECT 
        user_id,
        visit_id,
        DATEDIFF(SECOND, MIN(page_visit_created_at), MAX(page_visit_last_activity)) AS session_duration
    FROM 
        my_table
    GROUP BY 
        user_id, visit_id
)
-- 第二步：求和每个用户的所有会话时长
SELECT 
    user_id,
    SUM(session_duration) AS total_activity_seconds
FROM 
    session_durations
GROUP BY 
    user_id;

示例数据验证 #

针对你提供的示例数据，计算结果如下：

• user_id=1：
  • visit_id=11：2023-06-21 17:04:51.122 - 2023-06-21 16:56:13.950 = 517秒
  • visit_id=16：2023-06-22 13:15:10.111 - 2023-06-22 13:10:11.223 = 299秒
  • 总时长：517 + 299 = 816秒
• user_id=2：
  • visit_id=12：2023-06-21 17:50:23.553 - 2023-06-21 17:36:45.550 = 818秒
• user_id=3：
  • visit_id=14：2023-06-21 18:09:11.111 - 2023-06-21 18:02:13.421 = 418秒
  • visit_id=15：2023-06-21 18:31:11.347 - 2023-06-21 18:23:51.383 = 439秒
  • 总时长：418 + 439 = 857秒

替代方案（不使用CTE） #

如果你的数据库不支持CTE（公共表表达式），可以用子查询实现：

SELECT 
    user_id,
    SUM(session_duration) AS total_activity_seconds
FROM (
    SELECT 
        user_id,
        visit_id,
        DATEDIFF(SECOND, MIN(page_visit_created_at), MAX(page_visit_last_activity)) AS session_duration
    FROM 
        my_table
    GROUP BY 
        user_id, visit_id
) AS sub
GROUP BY 
    user_id;

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