題目來源：拼多多。

1 題目

有一張表t_id記錄了id，id不重復，但是會存在間斷，求出連續段的起始位置和結束位置。

樣例數據

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

2 建表語句

--建表語句
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)

3 題解

（1）lag()函數進行開窗計算與上一行的差值；

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()進行累積計算，獲得分組依據字段。

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 difffrom 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）得出結果

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_typefrom (select id,id - lag(id) over (order by id) as difffrom t_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       |
+-------------+------------+----------+