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

Q: 用户活跃区间合并

阿里巴巴大数据面试SQL-合并重叠的登录时间段

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴数据平台部的用户行为分析岗面试。淘宝/天猫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,
       case when start_time <= max(end_time) over (partition by user_id order by start_time rows between unbounded preceding and 1 preceding)
            then 0 else 1 end as 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            |
+----------+---------------------+---------------------+--------------+

观察 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 (partition by user_id order by start_time) as group_id
    from (
        select user_id, start_time, end_time,
               case when start_time <= max(end_time) over (partition by user_id order by start_time rows between unbounded preceding and 1 preceding)
                    then 0 else 1 end as is_new_group
        from t4_user_session
    ) t1
) t2
group by 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 |
+----------+---------------------+---------------------+

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

八、建表语句和数据插入

点击展开 DDL & DML

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

INSERT INTO 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');

📱关注公众号

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

💬加群交流

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

一、题目背景​

二、题目​

三、思路分析​

四、逐步推导​

步骤1：标记新区间起点​

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

五、常见坑点​

六、举一反三​

七、知识点总结​

八、建表语句和数据插入​

你可能还想看