如何计算参与者会话间隔停留时长？SQL表新增列实现需求

现有表结构与数据 #

现有存储用户行程会话的participants表，结构及数据如下：

CREATE TABLE participants
(
    id INT, 
    session_id INT, 
    distance DOUBLE PRECISION, 
    duration DOUBLE PRECISION, 
    start_time INT, 
    end_time INT
);

INSERT INTO participants (id, session_id, distance, duration, start_time, end_time)
VALUES (10, 1, 1452.16, 941.989, 115866, 116808),    
       (10, 3, 2812.62, 1843.658, 116810, 118653),
       (10, 9, 91.6784, 33.677, 118689, 118722),
       (12, 1, 180.556, 79.839, 118802, 118881),
       (12, 3, 355.361, 91.186, 118910, 119001),
       (12, 5, 82.0248, 35.989, 119013, 119049),
       (12, 7, 5.33002, 6.997, 119055, 119062),
       (13, 1, 10583.8, 494.003, 279157, 279651),
       (13, 3, 2.22556, 5.02, 279654, 279659),
       (13, 5, 67.821, 60.039, 279665, 279725);

-- 查询前5条数据
SELECT * FROM participants LIMIT 5;

查询结果：

id  session_id  distance    duration    start_time  end_time
10     1        1452.16     941.989      115866     116808
10     3        2812.62    1843.658      116810     118653
10     9        91.6784      33.677      118689     118722
12     1        180.556      79.839      118802     118881
12     3        355.361      91.186      118910     119001

需求说明 #

需要创建包含原表所有列及新增stay_time列的participants_B表。stay_time用于存储参与者当前会话开始与上一会话结束的间隔时长，即当前会话start_time - 上一会话end_time，若为用户首个会话则stay_time为0。

预期结果表 #

id  session_id  distance    duration    start_time  end_time stay_time
10      1       1452.16      941.989     115866      116808      0
10      3       2812.62     1843.658     116810      118653      2
10      9       91.6784       33.677     118689      118722     36
12      1       180.556       79.839     118802      118881      0
12      3       355.361       91.186     118910      119001     29
12      5       82.0248       35.989     119013      119049     12
12      7       5.33002        6.997     119055      119062      6
13      1       10583.8      494.003     279157      279651      0
13      3       2.22556         5.02     279654      279659      3
13      5        67.821       60.039     279665      279725      6

解决方案SQL #

使用窗口函数LAG()实现按用户分组获取上一会话的结束时间，计算间隔后创建新表：

CREATE TABLE participants_B AS
SELECT 
    id,
    session_id,
    distance,
    duration,
    start_time,
    end_time,
    COALESCE(start_time - LAG(end_time) OVER (PARTITION BY id ORDER BY start_time), 0) AS stay_time
FROM participants
ORDER BY id, start_time;

逻辑说明 #

• PARTITION BY id：按用户id分组，确保只计算同一用户的会话间隔
• ORDER BY start_time：按会话开始时间排序，保证获取的是上一个时间顺序的会话
• LAG(end_time)：获取分组内上一行的end_time值，首个会话无此行数据时返回NULL
• COALESCE(..., 0)：将NULL值替换为0，符合首个会话停留时长为0的需求

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