拼多多大数据面试SQL-求连续段的最后一个数及每个连续段的个数

一、题目

有一张表t_id记录了id，id不重复，但是会存在间断，求出连续段的最后一个数及每个连续段的个数。

+-----+
| id  |
+-----+
| 1   |
| 2   |
| 3   |
| 5   |
| 6   |
| 8   |
| 10  |
| 12  |
| 13  |
| 14  |
| 15  |
+-----+

二、分析

1. 本题还是对重新分组的考察，首先使用lag函数，计算与上一ID的差值，为1则代表连续，否则存在断点；
2. 使用累积求和方式对数据进行重新分组；
3. 根据重新分组标签进行分组，使用聚合函数max()，count()计算出每组的最后一个数和每组的个数；

三、SQL

1.lag()函数进行开窗计算与上一行的差值；

执行SQL

select id,
       id - lag(id) over (order by id) as diff
from t_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 t_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 t_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 t_id (
id bigint COMMENT 'ID'
) COMMENT 'ID记录表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
;
-- 插入数据
insert into t_id(id)
values
(1),
(2),
(3),
(5),
(6),
(8),
(10),
(12),
(13),
(14),
(15)

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:www.dwsql.com 同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。