--求出表F02中人员连续缴费3个月及以上的人员及其缴费月数
--创建表
create table F02
(
AAC001 number(20),--人员编号
AAE003 number(6)--缴费时间
);
--插入数据
insert into F02(aac001,aae003)values(100001,202201);
insert into F02(aac001,aae003)values(100001,202202);
insert into F02(aac001,aae003)values(100001,202203);
insert into F02(aac001,aae003)values(100002,202201);
insert into F02(aac001,aae003)values(100003,202201);
insert into F02(aac001,aae003)values(100004,202201);
insert into F02(aac001,aae003)values(100004,202202);
insert into F02(aac001,aae003)values(100004,202203);
insert into F02(aac001,aae003)values(100004,202204);
insert into F02(aac001,aae003)values(100004,202205);
insert into F02(aac001,aae003)values(100005,202201);
insert into F02(aac001,aae003)values(100005,202202);
insert into F02(aac001,aae003)values(100005,202204);
--数据处理
--1 对数据使用开窗函数分组人员编号按缴费时间升序排序
SELECT a.aac001,a.aae003,row_number()over(partition by AAC001 order by aae003) RANK1 FROM f02 a;
--2 用时间的值减去RANK1 得到RANK2 由于时间是可连续性的 如果是不连续时间 则RANK2的值不同 例如100005
SELECT aac001,aae003,aae003-RANK1 rank2 FROM (
SELECT a.aac001,a.aae003,row_number()over(partition by AAC001 order by aae003) RANK1 FROM f02 a
);
SELECT AAC001,COUNT(1) FROM (
SELECT aac001,aae003,aae003-RANK1 rank2 FROM (
SELECT a.aac001,a.aae003,row_number()over(partition by AAC001 order by aae003) RANK1 FROM f02 a
) ) GROUP BY AAC001,rank2
HAVING COUNT(1)>=3;
