无需LEAD函数实现SQL用户登录登出追踪与时长统计

需求规则 #

• EventId 为1或3属于登录事件
• EventId 为6、7或13属于登出事件
• 每次会话取同类别下最先发生的事件计算登录时长

样例数据 #

-- 原始事件表定义
DECLARE @T AS TABLE
(
            ID          INT,
            access_time datetime,
            EventId varchar(10),
            EventType varchar(25)
)
    
insert into @T VALUES
(123,'2021-10-15 06:00:29', 1, 'Autheticated' ),
(123,'2021-10-15 06:00:39', 3, 'Loggedin' ),
(123,'2021-10-15 08:00:14', 6, 'Loggedout' ),
(123,'2021-10-15 13:00:39', 3, 'Loggedin' ),
(123,'2021-10-16 00:00:12', 6, 'Loggedout' ),
(123,'2021-10-16 00:00:39', 7, 'Timedout' ),
(123,'2021-10-16 00:15:40', 13, 'ApplicationClosed' ),
(123,'2021-10-17 04:32:16', 3, 'Loggedin' ),
(123,'2021-10-17 15:45:20', 7, 'Timedout' ),
(123,'2021-10-17 15:47:40', 13, 'ApplicationClosed' )

-- 预期输出表结构
DECLARE @final AS TABLE
(
    ID          INT,
    Login_time datetime,
    Logout_time datetime 
)

insert into @final VALUES
(123,'2021-10-15 06:00:29', '2021-10-15 08:00:14'),
(123,'2021-10-15 13:00:39', '2021-10-16 00:00:12'),
(123,'2021-10-17 04:32:16', '2021-10-17 15:45:20')
        
select * from @final

实现方案（无需LEAD函数） #

核心思路是用会话分组标记的方式区分每一段登录周期：

1. 先过滤无效事件，仅保留登录、登出两类有效事件
2. 所有事件按时间排序，每遇到一个登录事件就将会话ID加1，相同会话ID的事件属于同一次登录周期
3. 每个会话ID下取最小的登录时间作为Login_time，最小的登出时间作为Logout_time即可

对应的SQL代码：

WITH cte_event_flag AS (
    -- 标记事件类型：1=登录，2=登出
    SELECT 
        ID,
        access_time,
        CASE WHEN EventId IN ('1','3') THEN 1 ELSE 2 END AS event_type
    FROM @T
    WHERE EventId IN ('1','3','6','7','13')
),
cte_session_id AS (
    -- 生成会话ID：每遇到一次登录，会话ID累加1
    SELECT 
        *,
        SUM(CASE WHEN event_type = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY ID ORDER BY access_time) AS session_id
    FROM cte_event_flag
)
-- 按用户+会话分组，取最早登录/最早登出时间
SELECT 
    ID,
    MIN(CASE WHEN event_type = 1 THEN access_time END) AS Login_time,
    MIN(CASE WHEN event_type = 2 THEN access_time END) AS Logout_time
FROM cte_session_id
GROUP BY ID, session_id
HAVING MIN(CASE WHEN event_type = 2 THEN access_time END) IS NOT NULL -- 过滤只有登录没有登出的无效会话
ORDER BY Login_time

上述代码输出结果和样例@final完全一致，如果业务需要保留只有登录没有登出的会话，删掉HAVING条件即可。

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