如何通过SQL高效计算用户每次登录程序的停留时长？

这个场景很常见，你之前用CASE WHEN拆分列的思路没问题，但确实没法直接关联对应的登录和登出记录，我给你推荐两种更高效的方案，其中窗口函数的方法应该是最优解：

方法一：使用LEAD()窗口函数（推荐） #

窗口函数可以直接将同一个用户的下一条事件记录关联到当前行，完美匹配登录-登出的对应关系。核心思路是：按用户分组、时间排序，给每个登录事件匹配紧随其后的登出事件时间，然后计算时间差。

示例SQL：

SELECT
    userID,
    eventtimeutc AS login_time,
    next_event_time AS logout_time,
    -- 计算停留时长，这里以分钟为单位，你可以根据需求调整
    TIMESTAMPDIFF(MINUTE, eventtimeutc, next_event_time) AS stay_duration_minutes,
    -- 也可以转换成小时+分钟的友好格式
    CONCAT(
        TIMESTAMPDIFF(HOUR, eventtimeutc, next_event_time),
        '小时',
        TIMESTAMPDIFF(MINUTE, eventtimeutc, next_event_time) % 60,
        '分钟'
    ) AS stay_duration
FROM (
    SELECT
        *,
        -- 获取同一用户下一条事件的时间
        LEAD(eventtimeutc) OVER (PARTITION BY userID ORDER BY eventtimeutc) AS next_event_time,
        -- 获取同一用户下一条事件的类型，用来验证是否是登出
        LEAD(propertyname) OVER (PARTITION BY userID ORDER BY eventtimeutc) AS next_event_type
    FROM your_table_name
) t
-- 只保留登录事件，且下一条事件是登出的有效记录
WHERE propertyname = 'login' AND next_event_type = 'logout';

代码细节解释： #

• PARTITION BY userID：确保我们只在同一个用户的事件池中查找下一条记录，不会跨用户匹配
• ORDER BY eventtimeutc：按时间顺序排列事件，保证匹配的是紧随当前登录之后的第一个登出事件
• LEAD()函数：专门用来获取当前行之后的指定行数据，这里分别取了时间和事件类型，用来过滤无效的登录（比如用户重复登录但没登出的情况）
• 外层筛选条件：避免统计没有对应登出的登录事件（比如用户一直在线未登出的场景）

这个方法不需要做表自连接，执行效率更高，代码也更简洁，是处理这类序列匹配问题的标准方案。

方法二：自连接（兼容旧版SQL） #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL），可以用自连接的方式，找到每个登录事件之后最早的登出事件：

SELECT
    l.userID,
    l.eventtimeutc AS login_time,
    r.eventtimeutc AS logout_time,
    TIMESTAMPDIFF(MINUTE, l.eventtimeutc, r.eventtimeutc) AS stay_duration_minutes
FROM your_table_name l
JOIN your_table_name r
    ON l.userID = r.userID
    AND r.propertyname = 'logout'
    AND r.eventtimeutc > l.eventtimeutc
LEFT JOIN your_table_name r2
    ON l.userID = r2.userID
    AND r2.propertyname = 'logout'
    AND r2.eventtimeutc > l.eventtimeutc
    AND r2.eventtimeutc < r.eventtimeutc
WHERE l.propertyname = 'login'
    AND r2.userID IS NULL;

代码细节解释： #

• 第一个JOIN：找到当前登录事件之后所有的登出事件
• 第二个LEFT JOIN：用来排除中间存在其他登出事件的情况，确保最终匹配的是当前登录之后的第一个登出事件
• 最后r2.userID IS NULL：过滤掉存在更早登出事件的记录，只保留最匹配的那一对登录-登出

不过这种方法的性能不如窗口函数，尤其是当数据量较大时，所以优先推荐窗口函数方案。

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