计算员工每日多次登录登出的单次在线时长

问题说明 #

现有一张表包含以下字段：

• Employee_id：员工ID
• time：事件发生的日期时间
• type：仅取值Login（登录）、logout（登出）

需求明确：

• 计算员工每次登录登出的在线时长，同一日内多次登录登出需分多行展示
• 若登出前存在多次登录，取最晚的登录时间匹配该登出时间

当前SQL仅能获取当日最早登录和最晚登出时间，无法满足上述需求，以下是正确实现方案。

实现思路与代码 #

核心逻辑是通过窗口函数为每个登出事件匹配其之前最近（最晚）的登录事件，具体步骤如下：

1. 为事件按员工和时间排序 #

先给每个员工的所有事件按时间顺序编号，方便后续关联匹配：

WITH event_order AS (
    SELECT 
        Employee_id,
        time,
        type,
        ROW_NUMBER() OVER (PARTITION BY Employee_id ORDER BY time) AS rn
    FROM employee_events
)

2. 匹配登出对应的最晚登录 #

通过自关联，筛选出每个登出事件之前的所有登录事件，取其中时间最晚的进行配对，并计算在线时长：

WITH event_order AS (
    SELECT 
        Employee_id,
        time,
        type,
        ROW_NUMBER() OVER (PARTITION BY Employee_id ORDER BY time) AS rn
    FROM employee_events
),
login_logout_pair AS (
    SELECT 
        e1.Employee_id,
        MAX(e2.time) AS login_time,
        e1.time AS logout_time,
        -- 以MySQL为例计算分钟级在线时长，其他数据库可调整函数
        TIMESTAMPDIFF(MINUTE, MAX(e2.time), e1.time) AS online_duration_minutes
    FROM event_order e1
    -- 关联当前员工早于当前登出事件的所有登录记录
    LEFT JOIN event_order e2 
        ON e1.Employee_id = e2.Employee_id 
        AND e2.type = 'Login' 
        AND e2.rn < e1.rn
    WHERE e1.type = 'logout'
    GROUP BY e1.Employee_id, e1.time, e1.rn
)
SELECT 
    Employee_id,
    login_time,
    logout_time,
    online_duration_minutes
FROM login_logout_pair
ORDER BY Employee_id, logout_time;

3. 不同数据库的时间差函数适配 #

• PostgreSQL：用EXTRACT(EPOCH FROM (logout_time - login_time)) / 60获取分钟数
• SQL Server：用DATEDIFF(MINUTE, login_time, logout_time)计算时长

示例验证 #

假设示例数据：

执行SQL后得到期望结果：

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