类似于使用打卡时间求一段时间内的工作时间之和,给出的表数据大致是这样的。
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