SQL面试题练习 —— 求连续段的最后一个数及每个连续段的个数

题目来源：拼多多。

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 题解

第一步：lag()函数进行开窗计算与上一行的差值。

select
    id,
    id-lag(id) over(order by id) as diff
from
    t_id;

执行结果

第二步：根据diff进行判断，如果差值为1代表连续赋值为0，否则代表不连续赋值为1，然后使用sum()进行累积计算，获得分组依据字段。

select
    id,
    if(diff=1,0,1) as judge,
    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

执行结果

第三步：得出结果。

select
    group_type,
    max(id) as max_part,
    count(id) as num_part
from
    (select
        id,
        if(diff=1,0,1) as judge,
        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
order by group_type;

执行结果