Oracle数据间断累加2

一.问

说明:当我们手里有一列数据(假设为工号,流水菜单,日期,时间),此数据是不规则间断的
要求:我们要计算此数据开始到间断之间的连续次数,如果间断,则重新开始计算下一个间断间的连续次数

/*后续我们将此表更名为test,方便大家查看*/
select '6101'userid,'A1'menuname,'2022/07/08'aday,'2022/07/08 07:20'adate from dual union all
select '6101'userid,'A1'menuname,'2022/07/08'aday,'2022/07/08 07:30'adate from dual union all
select '6101'userid,'A1'menuname,'2022/07/08'aday,'2022/07/08 07:40'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 07:50'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 07:55'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 07:56'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 07:57'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 07:58'adate from dual union all
select '6101'userid,'A1'menuname,'2022/07/08'aday,'2022/07/08 08:20'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 08:21'adate from dual union all
select '6101'userid,'A2'menuname,'2022/07/08'aday,'2022/07/08 08:22'adate from dual

二.解

1.首先,我们要计算出辅助列(contin/contin2):

select row_number()over(order by userid ,aday,adate) rownumber,adate,aday,userid,menuname,
decode(lag(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,
decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,aday||menuname||userid),aday||menuname||userid)contin,
decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,'Y') contin2
from test order by userid,aday,adate

如图,我们可以得到contin,这一列记录了间断的开始时间于结束时间,而中间的连续数据值为空,以及contin2,这一列则只在结束时间处加字符标记’Y’

2.我们将**contin **列为空的数据排除

select aday,rownumber-nvl(lag(rownumber)over(partition by userid,aday order by userid,aday,adate),rownumber)+
decode(lag(contin2)over(partition by userid,aday order by userid,aday,adate),'Y',0,1)
unit,contin,contin2,userid,menuname,adate from
(
	select row_number()over(order by userid ,aday,adate) rownumber,adate,aday,userid,menuname,
	decode(lag(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,
	decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,aday||menuname||userid),aday||menuname||userid)contin,
	decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,'Y') contin2
	from test order by userid,aday,adate
)where contin is not null order by userid ,aday,adate

3.然后将contin2为空的数据排除

select aday,unit,userid,menuname,adate from
(
	select aday,rownumber-nvl(lag(rownumber)over(partition by userid,aday order by userid,aday,adate),rownumber)+
	decode(lag(contin2)over(partition by userid,aday order by userid,aday,adate),'Y',0,1)
	unit,contin,contin2,userid,menuname,adate from
	(
		select row_number()over(order by userid ,aday,adate) rownumber,adate,aday,userid,menuname,
		decode(lag(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,
		decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,aday||menuname||userid),aday||menuname||userid)contin,
		decode(lead(aday||menuname||userid)over(partition by userid,aday order by userid ,aday,adate),aday||menuname||userid,null,'Y') contin2
		from test order by userid,aday,adate
	)where contin is not null order by userid ,aday,adate
)where contin2='Y' order by userid ,aday,adate

这样,我们就获取了想要的数据