基于Gap-and-Island问题的会话统计：识别日志间隔超5分钟的会话

需要排查原始会话日志表raw_data中，连续两行日志时间差超过5分钟的会话，关联labels表获取这些会话对应的出行方式mode，最终完成两个目标：

• 列出存在该问题的会话ID及对应出行方式
• 统计存在该问题的会话总数

表结构与测试数据 #

1. labels表（存储会话出行方式） #

CREATE TABLE labels(user_id INT, session_id INT,
  start_time TIMESTAMP,mode TEXT);

INSERT INTO labels (user_id,session_id,start_time,mode)
VALUES  (48,652,'2016-04-01 00:47:00+01','foot'),
(9,656,'2016-04-01 00:03:39+01','car'),(9,657,'2016-04-01 00:26:51+01','car'),
(9,658,'2016-04-01 00:45:19+01','car'),(46,663,'2016-04-01 00:13:12+01','car');

2. raw_data表（原始会话日志） #

CREATE TABLE raw_data(session_id INT,timestamp TIMESTAMP);

INSERT INTO raw_data(session_id,timestamp)          
VALUES (652,'2016-04-01 00:46:11.638+01'),(652,'2016-04-01 00:47:00.566+01'),
       (652,'2016-04-01 00:48:06.383+01'),(656,'2016-04-01 00:14:17.707+01'),
       (656,'2016-04-01 00:15:18.664+01'),(656,'2016-04-01 00:16:19.687+01'),
       (656,'2016-04-01 00:24:20.691+01'),(656,'2016-04-01 00:25:23.681+01'),
       (657,'2016-04-01 00:24:50.842+01'),(657,'2016-04-01 00:26:51.096+01'),
       (657,'2016-04-01 00:37:54.092+01');

解决方案 #

1. 查询存在时间间隔问题的会话及对应出行方式 #

使用窗口函数LAG()获取每个会话中当前日志的上一条日志时间，计算时间差后筛选出符合条件的会话，再关联labels表获取出行方式：

WITH session_gaps AS (
    SELECT 
        session_id,
        timestamp,
        LAG(timestamp) OVER (PARTITION BY session_id ORDER BY timestamp) AS prev_timestamp,
        EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY session_id ORDER BY timestamp))) / 60 AS minutes_diff
    FROM raw_data
)
SELECT DISTINCT sg.session_id, l.mode
FROM session_gaps sg
JOIN labels l ON sg.session_id = l.session_id
WHERE sg.minutes_diff > 5;

2. 统计存在问题的会话总数 #

基于时间差筛选结果，统计唯一会话的数量：

WITH session_gaps AS (
    SELECT 
        session_id,
        EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY session_id ORDER BY timestamp))) / 60 AS minutes_diff
    FROM raw_data
)
SELECT COUNT(DISTINCT session_id) AS problematic_session_count
FROM session_gaps
WHERE minutes_diff > 5;

3. 合并查询（同时获取明细与统计） #

如果需要一次输出统计总数和会话明细，可使用以下语句：

WITH session_gaps AS (
    SELECT 
        session_id,
        EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY session_id ORDER BY timestamp))) / 60 AS minutes_diff
    FROM raw_data
),
problematic_sessions AS (
    SELECT DISTINCT sg.session_id, l.mode
    FROM session_gaps sg
    JOIN labels l ON sg.session_id = l.session_id
    WHERE sg.minutes_diff > 5
)
SELECT 
    (SELECT COUNT(*) FROM problematic_sessions) AS total_count,
    session_id,
    mode
FROM problematic_sessions;

结果示例 #

存在问题的会话明细： #

统计总数： #

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