oracle 常见场景01 连续分组

--求出表F01中每个人在每家单位的缴费最小时间和缴费最大时间 ，按时间升序排序 

--创建表

create table F01

(

AAC001 number(20),--人员编号

AAB001 number(20),--单位编号

AAE003 number(6)--缴费时间

);

--插入数据

insert into F01(aac001,aab001,aae003)values(1001,2001,202003);

insert into F01(aac001,aab001,aae003)values(1001,2001,202004);

insert into F01(aac001,aab001,aae003)values(1001,2002,202005);

insert into F01(aac001,aab001,aae003)values(1001,2002,202006);

insert into F01(aac001,aab001,aae003)values(1001,2001,202007);

insert into F01(aac001,aab001,aae003)values(1001,2007,202008);

insert into F01(aac001,aab001,aae003)values(1001,2007,202009);

insert into F01(aac001,aab001,aae003)values(1002,2001,202003);

insert into F01(aac001,aab001,aae003)values(1002,2001,202004);

源表

--数据处理

SELECT a.*, rank1 - rank2 as rank3

          FROM (SELECT a.*,

                      row_number() over(partition by aab001 ORDER BY aae003) rank2

                  FROM (SELECT a.aab001,

                              a.aac001,

                              a.aae003,

                              row_number() over(ORDER BY aae003) rank1 

                          FROM F01 a) a) a；

利用开窗函数产生RANK3数据达到可以连续分组的情况

SELECT aac001, aab001, min(aae003) min_aae003, max(aae003) max_aae003

  FROM (SELECT a.*, rank1 - rank2 as rank3

          FROM (SELECT a.*,

                      row_number() over(partition by aab001 ORDER BY aae003) rank2

                  FROM (SELECT a.aab001,

                              a.aac001,

                              a.aae003,

                              row_number() over(ORDER BY aae003) rank1

                          FROM F01 a) a) a)

GROUP BY aab001, aac001, rank3

ORDER BY min_aae003;

最终数据集合