百度大数据面试SQL-用户搜索session切分

⚠️ 待修正

一、题目背景

这道题来自百度搜索事业部的用户行为分析岗面试。搜索Session是衡量用户搜索意图持续性的基本单位——一个Session内的多次搜索通常围绕同一个信息需求。Session切分的标准是"两次搜索间隔超过30分钟算新Session"，这是搜索行业的通用做法。

业务场景：搜索质量团队通过Session分析来评估搜索结果满意度。如果用户在一次Session内反复修改搜索词（说明没找到满意结果），说明搜索质量需要改进。Session切分是所有后续分析的前提。

二、题目

现有一张用户搜索日志表 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

步骤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      |
+----------+-------------------+---------------------+------------+

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')。

六、举一反三

1. 统计每个Session的搜索次数和时长：GROUP BY session_id，COUNT搜索次数，DATEDIFF(min, max)算Session持续时长
2. 30分钟改成其他阈值：改成15分钟（900秒）用于APP场景（用户注意力更短），或60分钟用于PC场景
3. 跨天Session：如果想"每天从0点重新计Session"，在外层加 PARTITION BY user_id, date ORDER BY search_time，日期变化自动重置
4. Session内搜索意图变化：计算相邻搜索词的语义相似度，标记"意图切换"事件

七、知识点总结

考点	说明
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真题