大数据面试SQL（八）：求连续段的起始位置和结束位置

文章目录

求连续段的起始位置和结束位置

一、题目

二、分析

三、SQL实战

四、样例数据参考

求连续段的起始位置和结束位置

一、题目

有一张表t2_id记录了id，id不重复，但是会存在间断，求出连续段的起始位置和结束位置。

样例数据：

目标结果：

二、分析

1、本题对重新分组的考察，此类题目真的比较常见的。

2、使用累积求和方式对数据进行重新分组。

3、根据重新分组标签进行分组，使用聚合函数min(),max()计算出每组的起始位置和结束位置。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

三、SQL实战

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

查询语句：

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

查询结果：

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 t2_id) t;

查询结果：

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 t2_id) t) tt
group by group_type
having min(id) <> max(id);

查询结果：

四、样例数据参考

--建表语句
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),(16);