sql判断是否连续并生成连续分组id

chimchim66

已于 2022-11-10 20:53:34 修改

阅读量756

点赞数

分类专栏： sql 文章标签： sql 数据库 mysql

于 2022-11-10 11:37:11 首次发布

本文链接：https://blog.youkuaiyun.com/chimchim66/article/details/127785671

版权

sql 专栏收录该内容

31 篇文章

订阅专栏

一、需求

想实现根据时间升序排序取出同班级下一个进入班级的时间，然后判断同一班级上一个人和下一个人是否连续,并生成符合分组条件的连续分组id。

二、测试案例

1.测试数据

create table test_detail(
id   bigint comment '主键',
num  string comment '班级号码',
name string comment '名字',
start_timestamp bigint comment '进入班级时间'
)comment '测试数据明细'
row format delimited fields terminated by '\t'
stored as textfile;

insert into table test_detail values(1,'01','桑稚',1666826633);
insert into table test_detail values(2,'01','桑稚',1666826857);
insert into table test_detail values(3,'01','桑稚',1666826883);
insert into table test_detail values(4,'01','温以凡',1666914770);
insert into table test_detail values(5,'01','温以凡',1666915255);
insert into table test_detail values(6,'01','温以凡',1666915791);
insert into table test_detail values(7,'01','桑稚',1666915843);
insert into table test_detail values(8,'01','桑稚',1666916328);
insert into table test_detail values(9,'01','桑稚',1666916493);
insert into table test_detail values(10,'02','段嘉许',1667002192);
insert into table test_detail values(11,'02','桑延',1666139582);
insert into table test_detail values(12,'02','段嘉许',1666140046);
insert into table test_detail values(13,'02','段嘉许',1666140076);

2.实现

select 
    id              --主键
   ,num             --班级号码
   ,name            --名字
   ,start_timestamp --进入班级时间
   ,lead(start_timestamp,1,2000000000) over(partition by num order by start_timestamp asc ) as end_timestamp 
   --同一班级下一个进入班级时间（最后一个人没有下一位，默认为2000000000）
   ,lag(name)  over(partition by num order by start_timestamp asc ) as last_name 
   --同一班级上一位进入班级的人
   ,lead(name) over(partition by num order by start_timestamp asc ) as next_name 
   --同一班级下一位进入班级的人
   ,row_number() over (partition by num order by start_timestamp asc) 
         -rank() over (partition by num,name order by start_timestamp)  as group_id 
   --连续内容的分组id
   ,case when name=lag(name) over(partition by num order by start_timestamp asc ) or
              name=lead(name) over(partition by num order by start_timestamp asc )
         then 1 else 0 
    end as is_continue 
   --判断同一班级进入班级的人是否连续，1为连续，0为不连续
from test_detail
;