使用lag()函数匹配post:/login_request与post:/login的对应时间戳

我想要获取post:/login_request的时间戳，以此计算它与post:/login两个步骤之间的耗时。尝试使用lag()函数，但当前查询会错误地将前一条记录的时间戳关联到下一条，比如设备EFG456的第二次post:/login_request会关联到上一次post:/login的时间，而不是保持为空。

原始数据 #

*-----------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     |
*-----------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  |                                                                        
*-----------------------------------------------------------------------------------------* 

当前查询语句 #

select
    device_serial, 
    device_gen, 
    status_code, 
    method, 
    event_time,
    lag(event_time) over(partition by device_serial, status_code order by event_time) as first_step_ts
from test_tbl

当前查询结果 #

*-----------------------------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     | prev_ts         |
*-----------------------------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |                 |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  | 3/3/24 23:10:05 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  | 3/3/24 18:37:25 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  | 3/3/24 18:37:28 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  | 3/3/24 21:58:44 |
*-----------------------------------------------------------------------------------------------------------* 

期望查询结果 #

*-----------------------------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     | prev_ts         |
*-----------------------------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |                 |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  | 3/3/24 23:10:05 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  | 3/3/24 18:37:25 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  | 3/3/24 21:58:44 |
*-----------------------------------------------------------------------------------------------------------* 

解决方案 #

核心思路是为每个设备的每一组login_request和login请求分配唯一的会话ID，确保每对请求被正确分组，再在分组内提取对应的时间戳。

调整后的查询语句：

SELECT
    device_serial,
    device_gen,
    status_code,
    method,
    event_time,
    -- 仅在post:/login记录中提取同组的post:/login_request时间
    CASE WHEN method = 'post:/login' THEN
        MAX(CASE WHEN method = 'post:/login_request' THEN event_time END)
        OVER (PARTITION BY device_serial, session_id)
    END AS prev_ts
FROM (
    SELECT
        *,
        -- 每次遇到post:/login_request就生成新的会话ID
        SUM(CASE WHEN method = 'post:/login_request' THEN 1 ELSE 0 END)
        OVER (PARTITION BY device_serial ORDER BY event_time) AS session_id
    FROM test_tbl
) t
ORDER BY device_serial, event_time;

说明 #

1. 子查询中通过SUM()窗口函数，为每个设备的每一次post:/login_request发起的请求序列分配递增的session_id，这样每一对login_request和login会拥有相同的session_id。
2. 外层查询中，仅对post:/login记录提取同session_id下的post:/login_request的时间戳，post:/login_request记录的prev_ts则保持为空，完全符合期望结果。

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