SQL Session切分：用户搜索间隔超过30分钟切为新会话（百度面试题）

一、题目

现有一张用户搜索日志表 t6_search_detail，记录了用户每次搜索的详细时间。请按规则切分Session：同一用户相邻两次搜索间隔超过30分钟算新Session，为每条搜索记录打上Session ID。

搜索日志表 t6_search_detail：

+----------+-------------+----------------------+
| user_id  | query_word  |     search_time      |
+----------+-------------+----------------------+
| u01      | 天气预报        | 2023-03-01 10:00:00  |
| u01      | 北京天气        | 2023-03-01 10:10:00  |
| u01      | 北京故宫        | 2023-03-01 10:25:00  |
| u01      | 股票行情        | 2023-03-01 12:00:00  |
| u01      | A股大盘        | 2023-03-01 12:15:00  |
| u02      | 世界杯         | 2023-03-01 10:00:00  |
| u02      | 世界杯赛程       | 2023-03-01 10:05:00  |
| u02      | 深度学习        | 2023-03-01 11:00:00  |
+----------+-------------+----------------------+

二、思路分析

Session切分是事件序列标记的经典问题，核心是 LAG + SUM OVER 累积分组：

1. LAG 获取前一次时间：LAG(search_time) OVER (PARTITION BY user_id ORDER BY search_time)
2. 判断新Session起点：时间差 > 1800秒（30分钟）→ 标记 is_new_session = 1
3. 累积求和生成Session ID：SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY search_time) 累积求和即得递增的会话编号

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、逐步推导

步骤1：LAG获取上一次搜索时间

select user_id, query_word, search_time,
       lag(search_time, 1) over (partition by user_id order by search_time) as prev_search_time
from t6_search_detail

执行结果

+----------+-------------+----------------------+----------------------+
| user_id  | query_word  |     search_time      |   prev_search_time   |
+----------+-------------+----------------------+----------------------+
| u01      | 天气预报        | 2023-03-01 10:00:00  | NULL                 |
| u01      | 北京天气        | 2023-03-01 10:10:00  | 2023-03-01 10:00:00  |
| u01      | 北京故宫        | 2023-03-01 10:25:00  | 2023-03-01 10:10:00  |
| u01      | 股票行情        | 2023-03-01 12:00:00  | 2023-03-01 10:25:00  |
| u01      | A股大盘        | 2023-03-01 12:15:00  | 2023-03-01 12:00:00  |
| u02      | 世界杯         | 2023-03-01 10:00:00  | NULL                 |
| u02      | 世界杯赛程       | 2023-03-01 10:05:00  | 2023-03-01 10:00:00  |
| u02      | 深度学习        | 2023-03-01 11:00:00  | 2023-03-01 10:05:00  |
+----------+-------------+----------------------+----------------------+
8 rows selected (0.925 seconds)(https://www.dwsql.com)

步骤2：判断是否新Session起点 + 累积生成Session ID

执行SQL

select user_id, query_word, search_time,
       concat(user_id, '_',
              sum(is_new_session) over (partition by user_id order by search_time)) as session_id
from (
    select user_id, query_word, search_time,
           case
               when prev_search_time is null then 1
               when unix_timestamp(search_time) - unix_timestamp(prev_search_time) > 1800 then 1
               else 0
           end as is_new_session
    from (
        select user_id, query_word, search_time,
               lag(search_time, 1) over (partition by user_id order by search_time) as prev_search_time
        from t6_search_detail
    ) t1
) t2

执行结果

+----------+-------------+----------------------+-------------+
| user_id  | query_word  |     search_time      | session_id  |
+----------+-------------+----------------------+-------------+
| u01      | 天气预报        | 2023-03-01 10:00:00  | u01_1       |
| u01      | 北京天气        | 2023-03-01 10:10:00  | u01_1       |
| u01      | 北京故宫        | 2023-03-01 10:25:00  | u01_1       |
| u01      | 股票行情        | 2023-03-01 12:00:00  | u01_2       |
| u01      | A股大盘        | 2023-03-01 12:15:00  | u01_2       |
| u02      | 世界杯         | 2023-03-01 10:00:00  | u02_1       |
| u02      | 世界杯赛程       | 2023-03-01 10:05:00  | u02_1       |
| u02      | 深度学习        | 2023-03-01 11:00:00  | u02_2       |
+----------+-------------+----------------------+-------------+
8 rows selected (1.225 seconds)(https://www.dwsql.com)

u01前3次搜索在10:00-10:25之间，间隔均≤30分钟，属同一Session(u01_1)。10:25到12:00间隔95分钟>30分钟，开启新Session(u01_2)。u02类似：10:05到11:00间隔55分钟，切分为两个Session。

四、常见坑点

坑1：首行NULL的处理

第一行的 LAG 返回 NULL，需要特殊处理。CASE WHEN prev_search_time IS NULL THEN 1 确保每组第一行强制为Session起点，否则第一条记录的 is_new_session = 0 会丢失第一个Session。

坑2：unix_timestamp的格式要求

unix_timestamp('2023-03-01 10:00:00') 默认接受 yyyy-MM-dd HH:mm:ss 格式。如果时间格式不同（如 2023/03/01），需要指定格式：unix_timestamp(time, 'yyyy/MM/dd HH:mm:ss')。

五、知识点总结

考点	说明
LAG 获取前一行	获取同一用户上一次搜索时间，用于计算间隔
unix_timestamp 时间差	转为秒数计算差值，精确到秒
CASE WHEN 断点标记	0/1标记法：1=新Session起点，0=续接前Session
SUM OVER 累积分组	对0/1标记累积求和，生成递增的Session ID

六、建表语句和数据插入

点击展开 DDL & DML

create table t6_search_detail (
    user_id     string COMMENT '用户ID',
    query_word  string COMMENT '查询词',
    search_time string COMMENT '搜索时间'
) COMMENT '用户搜索日志表';

insert into t6_search_detail values
('u01', '天气预报',   '2023-03-01 10:00:00'),
('u01', '北京天气',   '2023-03-01 10:10:00'),
('u01', '北京故宫',   '2023-03-01 10:25:00'),
('u01', '股票行情',   '2023-03-01 12:00:00'),
('u01', 'A股大盘',    '2023-03-01 12:15:00'),
('u02', '世界杯',     '2023-03-01 10:00:00'),
('u02', '世界杯赛程', '2023-03-01 10:05:00'),
('u02', '深度学习',   '2023-03-01 11:00:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题