大数据面试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 diff
      from 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_type
      from (select id,
                   id - lag(id) over (order by id) as diff
            from 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);

• 📢博客主页：https://lansonli.blog.csdn.net
• 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
• 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
• 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨