阿里巴巴大数据面试SQL-用户活跃区间合并

数据仓库晨曦

发布于 2026-07-06 13:28:56

140

一、题目背景

这道题来自阿里巴巴数据平台部的用户行为分析岗面试。淘宝/天猫App每天产生百亿级的用户会话日志，用户在浏览商品时可能频繁切换网络（WiFi→4G）、切后台再返回，导致一条完整的访问被拆成多条重叠或相邻的会话记录。数据团队需要将这些碎片化的会话合并成真实的"用户活跃区间"，才能准确计算用户停留时长、活跃时段分布等核心指标。

业务场景：算法团队需要根据用户真实活跃区间做实时推荐，如果没合并重叠会话，同一次浏览被算成多次访问，推荐频次控制就会失效——用户半小时内收到5条推送，体验极差。区间合并就是解决这个问题的第一步。

二、题目

现有一张用户登录会话表 t4_user_session，记录每次登录的开始和结束时间。请合并同一用户重叠的活跃区间。

用户登录会话表 t4_user_session：

+----------+----------------------+----------------------+
| user_id  |      start_time      |       end_time       |
+----------+----------------------+----------------------+
| u01      | 2023-03-01 08:00:00  | 2023-03-01 09:00:00  |
| u01      | 2023-03-01 08:30:00  | 2023-03-01 10:00:00  |
| u01      | 2023-03-01 11:00:00  | 2023-03-01 12:00:00  |
| u02      | 2023-03-01 09:00:00  | 2023-03-01 10:00:00  |
| u02      | 2023-03-01 09:30:00  | 2023-03-01 10:30:00  |
+----------+----------------------+----------------------+

三、思路分析

这是区间合并（Merge Intervals）的经典问题，核心思路是 累计最大值 + 分组标记：

排序并求累计最大 end_time：按 user_id 分区、start_time 升序，用 MAX(end_time) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 求出截至当前行之前的所有 end_time 的最大值
判断新区间起点：如果当前 start_time <= 上一步的历史最大 end_time，说明与前面区间重叠，属于同一组；否则是新一组的起点
累加分组标识：用 SUM(is_new_group) 生成 group_id
聚合：按 user_id + group_id 分组，取 MIN(start_time) 和 MAX(end_time)

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

四、逐步推导

步骤1：标记新区间起点

按用户分区、按开始时间排序，用窗口函数计算当前行之前（不含当前行）的最大 end_time。如果当前 start_time <= 历史最大 end_time，则与前面重叠，不需要新分组；否则标记为新分组的起点。

执行SQL

select user_id, start_time, end_time,
       casewhen start_time <= max(end_time) over (partitionby user_id orderby start_time rowsbetweenunboundedprecedingand1preceding)
            then0else1endas is_new_group
from t4_user_session

执行结果

+----------+----------------------+----------------------+---------------+
| user_id  |      start_time      |       end_time       | is_new_group  |
+----------+----------------------+----------------------+---------------+
| u01      | 2023-03-01 08:00:00  | 2023-03-01 09:00:00  | 1             |
| u01      | 2023-03-01 08:30:00  | 2023-03-01 10:00:00  | 0             |
| u01      | 2023-03-01 11:00:00  | 2023-03-01 12:00:00  | 1             |
| u02      | 2023-03-01 09:00:00  | 2023-03-01 10:00:00  | 1             |
| u02      | 2023-03-01 09:30:00  | 2023-03-01 10:30:00  | 0             |
+----------+----------------------+----------------------+---------------+
5 rows selected (8.946 seconds)(https://www.dwsql.com)

观察 u01：第一行没有"前面行"，所以 max(end_time) over (... 1 preceding) 为 NULL，start_time <= NULL 为 false，标记为新组（is_new_group=1）。第二行 start_time=08:30 <= 前面最大 end_time=09:00，重叠，标记为0。第三行 start_time=11:00 > 前面最大 end_time=10:00，不重叠，标记为新组。

步骤2：累加分组标识并合并区间

用 SUM(is_new_group) 生成递增的 group_id，然后按 user_id 和 group_id 分组取最小开始时间和最大结束时间。

执行SQL

select user_id, min(start_time) as merged_start, max(end_time) as merged_end
from (
    select user_id, start_time, end_time,
           sum(is_new_group) over (partitionby user_id orderby start_time) asgroup_id
    from (
        select user_id, start_time, end_time,
               casewhen start_time <= max(end_time) over (partitionby user_id orderby start_time rowsbetweenunboundedprecedingand1preceding)
                    then0else1endas is_new_group
        from t4_user_session
    ) t1
) t2
groupby user_id, group_id

执行结果

+----------+----------------------+----------------------+
| user_id  |     merged_start     |      merged_end      |
+----------+----------------------+----------------------+
| u01      | 2023-03-01 08:00:00  | 2023-03-01 10:00:00  |
| u01      | 2023-03-01 11:00:00  | 2023-03-01 12:00:00  |
| u02      | 2023-03-01 09:00:00  | 2023-03-01 10:30:00  |
+----------+----------------------+----------------------+
3 rows selected (1.253 seconds)(https://www.dwsql.com)

u01 的两段重叠区间被合并为 [08:00~10:00] 和 [11:00~12:00] 两段不重叠的活跃区间。u02 的两段合并为 [09:00~10:30]。

五、常见坑点

坑1：窗口帧范围必须用 ROWS BETWEEN，不能用 RANGE

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 和 RANGE BETWEEN ... 在遇到相同 start_time 时行为不同。如果有两条记录 start_time 完全相等，RANGE 模式会把它们视为同一"范围"，前一行计算 max 会包含当前行，导致新分组的判断出错。务必使用 ROWS 模式。

坑2：第一行的 NULL 处理

第一行的 MAX(end_time) OVER (... 1 PRECEDING) 结果是 NULL（因为没有前置行），而 start_time <= NULL 的结果是 NULL（不是 TRUE/FALSE），在 CASE WHEN 中会走 ELSE 分支。如果用 IF() 或 IIF() 需要显式处理 NULL。

坑3：日期字符串格式不一致

如果 start_time 和 end_time 字段中是字符串而非 timestamp 类型，需要确保格式统一（如 '2023-03-01 08:00:00'），否则字符串比较会出错。

六、举一反三

计算合并后的总活跃时长：在外层加上 TIMESTAMPDIFF(MINUTE, merged_start, merged_end) 得到每段活跃区间的分钟数，再 SUM 得到用户总活跃时长
加入容忍间隔：如果允许间隔5分钟以内的区间也合并，只需把判断条件改为 start_time <= max_end_time + INTERVAL 5 MINUTE 即可
找出同时在线峰值：将 start_time 和 end_time 分别标记为 +1 和 -1，按时间排序求累计和的最大值，得到任意时刻的并发在线人数
按天拆分跨天区间：如果活跃区间跨天，需要在合并后进一步按天切分，便于做日活统计

七、知识点总结

考点	说明
窗口帧 ROWS BETWEEN	精确控制窗口范围，逐个比较前驱行的 end_time
累计 MAX	用 MAX() OVER 在排序后求出历史最大 end_time
CASE WHEN 分组标记	判断是否重叠来标记新区间起点
SUM() OVER 生成分组ID	对 0/1 标记累加，生成连续的 group_id

八、建表语句和数据插入

CREATE TABLE t4_user_session (
    user_id    stringCOMMENT'用户ID',
    start_time stringCOMMENT'开始时间',
    end_time   stringCOMMENT'结束时间'
) COMMENT'用户登录会话表';

INSERTINTO t4_user_session VALUES
('u01','2023-03-01 08:00:00','2023-03-01 09:00:00'),
('u01','2023-03-01 08:30:00','2023-03-01 10:00:00'),
('u01','2023-03-01 11:00:00','2023-03-01 12:00:00'),
('u02','2023-03-01 09:00:00','2023-03-01 10:00:00'),
('u02','2023-03-01 09:30:00','2023-03-01 10:30:00');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-04，如有侵权请联系 cloudcommunity@tencent.com 删除

大数据