拼多多大数据面试SQL-求连续段的起始位置和结束位置
一、题目
有一张表t2_id记录了id，id不重复，但是会存在间断，求出连续段的起始位置和结束位置。
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+
三、思路分析
- 本题对重新分组的考察，此类题目真的是常见呀！
- 使用累积求和方式对数据进行重新分组；
- 根据重新分组标签进行分组，使用聚合函数min(),max()计算出每组的起始位置和结束位置；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
四、逐步推导
1.lag()函数进行开窗计算与上一行的差值；
执行SQL
select id,
id - lag(id) over (order by id) as diff
from t2_id
查询结果
+-----+-------+
| id | diff |
+-----+-------+
| 1 | NULL |
| 2 | 1 |
| 3 | 1 |
| 5 | 2 |
| 6 | 1 |
| 8 | 2 |
| 10 | 2 |
| 12 | 2 |
| 13 | 1 |
| 14 | 1 |
| 15 | 1 |
+-----+-------+
2.获得分组字段
根据diff进行判断，如果差值为1代表连续赋值为0，否则代表不连续赋值为1,然后使用sum()进行累积计算，获得分组依据字段。
执行SQL
select id,
sum(if(diff = 1, 0, 1)) over (order by id) as group_type
from (select id,
id - lag(id) over (order by id) as diff
from t2_id) t
查询结果
+-----+-------------+
| id | group_type |
+-----+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 5 | 2 |
| 6 | 2 |
| 8 | 3 |
| 10 | 4 |
| 12 | 5 |
| 13 | 5 |
| 14 | 5 |
| 15 | 5 |
+-----+-------------+
3.得出结果
执行SQL
select group_type,
min(id) as start_pos,
max(id) as end_pos
from (select id,
sum(if(diff = 1, 0, 1)) over (order by id) as group_type
from (select id,
id - lag(id) over (order by id) as diff
from t2_id) t) tt
group by group_type
查询结果
+-------------+------------+----------+
| group_type | start_pos | end_pos |
+-------------+------------+----------+
| 1 | 1 | 3 |
| 2 | 5 | 6 |
| 3 | 8 | 8 |
| 4 | 10 | 10 |
| 5 | 12 | 15 |
+-------------+------------+----------+
五、常见坑点
坑1：同天多条记录干扰排序 — 如果同一天有多条记录，ROW_NUMBER() 分配不同行号导致差值法分组失效。先用 SELECT DISTINCT 去重。
坑2：date_sub 跨月自动处理 — date_sub 跨月时自动修正月份边界，但不要用字符串运算代替日期函数。
坑3：NULL值使窗口函数行为不一致 — ORDER BY 中 NULL 默认排到最后（Spark SQL: NULLS LAST），不指定时可能排序错乱。
六、举一反三
-
最大连续未登录/未出单天数：反向计算断签天数，识别即将流失的用户或商家
-
指定连续N天筛选：HAVING中加
COUNT(*) >= N，用于签到奖励或金牌商家认证 -
按维度分组的连续：PARTITION BY 加入 category/region，对比不同维度的连续行为差异
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER + 差值法 | 连续问题经典解法：日期-行号=分组标识，相同差值同一连续段 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE t2_id (
id bigint COMMENT 'ID'
) COMMENT 'ID记录表';
-- 插入数据
insert into t2_id(id)
values
(1),
(2),
(3),
(5),
(6),
(8),
(10),
(12),
(13),
(14),
(15)
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
