Oracle数据间断累加

白露-

已于 2022-05-09 17:54:38 修改

阅读量915

点赞数 1

分类专栏： Oracle汇总笔记文章标签： sql

于 2022-05-09 11:07:50 首次发布

本文链接：https://blog.csdn.net/m0_56227168/article/details/124661135

版权

Oracle汇总笔记专栏收录该内容

20 篇文章 0 订阅

订阅专栏

一.问

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

二.解

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

select shift_date,

case when lag(shift_date,1,shift_date+1)over(order by shift_date)+1=shift_date 
then 
	(case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
	then null 
	else shift_date end) 
else shift_date end contin,

case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
then null 
else 'Y' end contin2 

from .....

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

我们将**contin **列为空的数据排除,并且将此列的上下两行数据相减

select contin-nvl(lag(contin,1,null)over(order by shift_date),contin) unit,contin,contin2 from
	(select shift_date,
	
	case when lag(shift_date,1,shift_date+1)over(order by shift_date)+1=shift_date 
	then 
		(case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
		then null 
		else shift_date end) 
	else shift_date end contin,
	
	case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
	then null 
	else 'Y' end contin2 
	
	from .....)
where contin is not null

在这里插入图片描述
如图,我们得到了unit这一列记录数据间间隔的值,

2.5.如果出现contin2值粘合现象,如图:
在这里插入图片描述
则使用如下代码来进行判断unit的值,基本逻辑为,当两个’Y’值为下上两行时,unit的值为0:

case when lag(contin2,1,null)over(order by usetname,shift_date) =contin2 and contin2='Y' 
then 0 
else contin-nvl(lag(contin,1,null)over(order by usetname,shift_date),contin)end unit

在这里插入图片描述
如过不出现则不做此判断,直接跳过,按正常逻辑来处理unit列

3.我们只需要开始与结束时间的间隔,所以我们将contin2列为空的数据排除,因为要将开始的那一天也算,所以最后我们进行调整(持续时间天数+1)

select unit+1 unit,contin from	
	(select contin-nvl(lag(contin,1,null)over(order by shift_date),contin) unit,contin,contin2 from
		(select shift_date,
		
		case when lag(shift_date,1,shift_date+1)over(order by shift_date)+1=shift_date 
		then 
			(case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
			then null 
			else shift_date end) 
		else shift_date end contin,
		
		case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
		then null 
		else 'Y' end contin2 
		
		from .....)
	where contin is not null)
where contin2 ='Y' order by contin

在这里插入图片描述
如图,我们得到了结束时间与开始时间到结束时间的间断的累加数值

4.如果你觉得这样还不满足?想要开始时间与结束时间?

select contin-unit opening_time,contin end_time,unit+1 unit from
	(select contin-nvl(lag(contin,1,null)over(order by shift_date),contin)  unit,contin,contin2 from
		(select shift_date,
		
		case when lag(shift_date,1,shift_date+1)over(order by shift_date)+1=shift_date 
		then 
			(case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
			then null 
			else shift_date end) 
		else shift_date end contin,
		
		case when lead(shift_date,1,shift_date-1)over(order by shift_date)-1=shift_date 
		then null 
		else 'Y' end contin2 
		
		from .....)
	where contin is not null)
where contin2 ='Y' order by contin

在这里插入图片描述
5.如果要代入其他行列,如用户姓名,则将所有lead函数和lag的over()里添加

partition by user_name

6.如果是多个用户，每个用户所持数据不一，而我们想看截止当天的持续时间，则：

and end_time=trunc(sysdate)

7.如果多个用户的部门不一，想要各个部门的数据整合，并且要得出每个状态的具体人数：示意代码

selelct 部门，
sum（case when unit>6 then 1 else 0 end)正常，
sum(case when unit<=5 and unit>=3 then 1 else 0 end)警告，
sum(case when unit <=2 then 1 else 0 end)异常
from(...