拼多多大数据面试SQL-求连续段的最后一个数及每个连续段的个数
一、题目
有一张表t3_id记录了id，id不重复，但是会存在间断，求出连续段的最后一个数及每个连续段的个数。
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+二、分析
- 本题还是对重新分组的考察，首先使用lag函数，计算与上一ID的差值，为1则代表连续，否则存在断点；
- 使用累积求和方式对数据进行重新分组；
- 根据重新分组标签进行分组，使用聚合函数max()，count()计算出每组的最后一个数和每组的个数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.lag()函数进行开窗计算与上一行的差值；
执行SQL
select id,
id - lag(id) over (order by id) as diff
from t3_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 t3_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,
max(id) as max_part,
count(1) as num_part
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 t3_id) t) tt
group by group_type查询结果
+-------------+-----------+-----------+
| group_type | max_part | num_part |
+-------------+-----------+-----------+
| 1 | 3 | 3 |
| 2 | 6 | 2 |
| 3 | 8 | 1 |
| 4 | 10 | 1 |
| 5 | 15 | 4 |
+-------------+-----------+-----------+四、建表语句和数据插入
--建表语句
CREATE TABLE t3_id (
id bigint COMMENT 'ID'
) COMMENT 'ID记录表';
-- 插入数据
insert into t3_id(id)
values
(1),
(2),
(3),
(5),
(6),
(8),
(10),
(12),
(13),
(14),
(15)本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；