9-固定时间段内的工作时间求和

类似于使用打卡时间求一段时间内的工作时间之和，给出的表数据大致是这样的。

ID:车间ID
gid:设备id
ACTIVITY：类型 ，有两种：in 开启， out 关闭
时间：启动或者关闭的时间点

现在的要求是输入一个开始时间 和一个结束时间 求每台设备（同一车间的同一设备）的在此范围内的工作时间。
异常数据处理：
1.重复数据需要去重
2.同一台设备连续的in 需要取最后一个 
3.连续的out需要取第一个
4.如果一个时间段的开始时间小于输入的开始时间，则按照输入开始时间计算
5.如果一个时间段的结束时间大于输入的结束时间，则按照输入结束时间计算
上面的表输入

最终得到的结果是

数据在下方：

CREATE TABLE "SYSTEM"."AAREPORT" (  "ID" VARCHAR2(255 BYTE) ,  
"ACTIVITY" VARCHAR2(255 BYTE) ,  "TXNTIMESTAMP" DATE , 
 "GID" VARCHAR2(255 BYTE) )TABLESPACE "SYSTEM"LOGGINGNOCOMPRESSPCTFREE 10INITRANS 1STORAGE 
 (  INITIAL 65536   NEXT 1048576   MINEXTENTS 1  MAXEXTENTS 2147483645  FREELISTS 1  FREELIST GROUPS 1  BUFFER_POOL DEFAULT)PARALLEL 1NOCACHEDISABLE ROW MOVEMENT;

-- ------------------------------ Records of AAREPORT-- ----------------------------
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('1', 'in', TO_DATE('2020-07-15 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('1', 'out', TO_DATE('2020-07-16 03:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('2', 'in', TO_DATE('2020-07-15 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'in', TO_DATE('2020-07-15 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'out', TO_DATE('2020-07-16 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'in', TO_DATE('2020-07-16 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('4', 'in', TO_DATE('2020-07-16 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('4', 'out', TO_DATE('2020-07-16 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('4', 'in', TO_DATE('2020-07-16 09:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('4', 'out', TO_DATE('2020-07-17 21:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'a');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('1', 'in', TO_DATE('2020-07-15 07:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('1', 'out', TO_DATE('2020-07-16 04:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'in', TO_DATE('2020-07-18 12:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'in', TO_DATE('2020-07-17 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');
INSERT INTO "SYSTEM"."AAREPORT" VALUES ('3', 'out', TO_DATE('2020-07-17 18:00:00', 'SYYYY-MM-DD HH24:MI:SS'), 'b');

答案：

with t_x as  ---用户参数输入
(select &输入开始时间 as start_time ,&输入结束时间 as end_time from dual)
,
t10 as     ---用于对重复数据去重和对最后无out数据的容错
(select distinct id,gid,activity,txntimestamp
from system.aareport  union all
select distinct id,gid,'out' as  activity,date'4712-12-31' as txntimestamp
from system.aareport
)
,t2 as   --- 对数据进行编号
(
select  id,gid,activity,txntimestamp,
row_number()over(partition by id,gid order by txntimestamp) as flag
from t10
)
-----将开始时间和结束时间写在同一行中，并对实际时间和传入时间进行对比选取
-----最后计算总时间的和
select t3.id,t3.gid,min(greatest(t3.txntimestamp,t5.start_time)) as start_time ,
max(least(t4.txntimestamp,t5.end_time)) as end_time ,
sum(least(t4.txntimestamp,t5.end_time)-
greatest(t3.txntimestamp,t5.start_time))*24 as sum_time
from t2 t3 , t2 t4,t_x t5
where t3.id=t4.id and t3.gid =t4.gid and
t3.flag=t4.flag-1  and t3.txntimestamp <=t5.end_time
and t4.txntimestamp >=t5.start_time
and t3.activity ='in'
and t4.activity ='out'
group by t3.id  ,t3.gid