如何根据一天中的任务完成情况从给定日期列中获取开始日期和结束日期(Oracle DB) [英] How to get start date and end date from the given date column according to task completion in a day (oracle DB)
问题描述
在虚拟数据中，您可以看到有两列Task_Completion和Time_stamp.有一个Java调度程序可以在任务完成时运行，例如，调度程序在15-FEB-2016上运行5次，在17-FEB上运行只有一次，所以我想要一个查询，该查询根据给定的time_stamp列计算start_time和end_time
Here in dummy data, you can see there are two columns Task_Completion and Time_stamp.there is a java scheduler that runs whenever the task is finish, for example, scheduler run on 15-FEB-2016 5 times and on 17-FEB only one time So I wanted a query that calculates the start_time and end_time from the given column time_stamp
Task_Completion Time_stamp
true 15-FEB-16 11.37.56.013000000 AM
true 15-FEB-16 11.42.55.593000000 AM
true 15-FEB-16 11.47.48.970000000 AM
true 15-FEB-16 12.21.57.587000000 PM
true 15-FEB-16 12.26.55.767000000 PM
true 17-FEB-16 10.24.03.320000000 PM
true 17-FEB-16 10.44.03.320000000 PM
true 18-FEB-16 10.19.03.333000000 PM
true 18-FEB-16 10.23.03.333000000 PM
ture 18-FEB-16 10.55.03.333000000 PM
所以输出必须像
start_time end_time
15-FEB-16 11.37.56.013000000 AM 15-FEB-16 11.47.48.970000000 AM
15-FEB-16 12.21.57.587000000 PM 15-FEB-16 12.26.55.767000000 PM
17-FEB-16 10.21.33.320000000 PM 17-FEB-16 10.26.33.320000000 PM
17-FEB-16 10.41.33.320000000 PM 17-FEB-16 10.46.33.320000000 PM
18-FEB-16 10.19.03.333000000 PM 18-FEB-16 10.23.03.333000000 PM
18-FEB-16 10.52.33.333000000 PM 18-FEB-16 10.57.33.333000000 PM
有条件:
如果在时间戳中存在0-5分钟的间隔(例如在 2月15日)，则 start_time 是 11.37.56.013000000 和 end_time 将是 11.47.48.970000000 ，但是如果没有，那么请再次检查当天是否有任何日程安排在 2月15日的 12.21.57.587000000(开始时间)和 12.26.55.767000000(结束时间)
There are conditions:
If there is a 0-5min gap in time_stamp for example on 15-Feb then start_time will be 11.37.56.013000000 and end_time will be 11.47.48.970000000 but if there is not, then again check is there any schedule runs on that day like on 15-Feb at 12.21.57.587000000(start_time) and 12.26.55.767000000(end_time)
但是，如果某个时间表与该日期的下一个时间表之间的时间间隔超过5分钟，则查询必须返回一个硬编码值 ，例如 在 17-FEB-16 调度程序上运行两次 10.24.03.320000000 PM 和 10.44.03.320000000 PM ，因此它们的间隔超过5分钟，然后必须返回2次输出，因为时间间隔超过5分钟.如果是这种情况，则将start.time添加2.30分钟，并将end_time添加2.30分钟(硬编码) 例如 17-FEB-16 10.21.33.320000000 PM(开始时间)， 17-FEB-16 10.26.33.320000000 PM(结束时间) 和 -FEB-16 10.41.33.320000000 PM(开始时间)，17-FEB-16 10.46.33.320000000 PM(结束时间)并且此条件必须适用于所有日期.
But if a schedule runs with a time gap of more than 5min to next schedule on that date then query must return a hard coded value for example on 17-FEB-16 scheduler runs two times 10.24.03.320000000 PM and 10.44.03.320000000 PM so their gap is more than 5 mins then it must return output 2 times because time gap is more than 5 min. it that case add 2.30 min to start_time and 2.30 min to end_time (hardcoded) that is fix like 17-FEB-16 10.21.33.320000000 PM(start time) , 17-FEB-16 10.26.33.320000000 PM(end_time) and 17-FEB-16 10.41.33.320000000 PM(start time) , 17-FEB-16 10.46.33.320000000 PM(end_time) and this condition must apply to all dates.
类似地，排定运行时间为 3次，因此计为2次运行，即10.19.03至10.23.03，然后是10.52.33至10.57. 33(开始时间为2.30min，结束时间为2.30min +结束时间).
Similarly, on 18th the schedule runs 3 times so it will be count as 2 run i.e 10.19.03 to 10.23.03 and next 10.52.33 to 10.57.33 (2.30min-start_time and 2.30min+end_time).
推荐答案
SELECT MIN( time_stamp ) - CASE COUNT(*)
WHEN 1
THEN INTERVAL '150' SECOND
ELSE INTERVAL '0' SECOND
END AS start_time,
MAX( time_stamp ) + CASE COUNT(*)
WHEN 1
THEN INTERVAL '150' SECOND
ELSE INTERVAL '0' SECOND
END AS end_time
FROM (
SELECT time_stamp,
SUM( diff ) OVER ( ORDER BY time_stamp ) AS grp
FROM (
SELECT time_stamp,
CASE
WHEN time_stamp - LAG( time_stamp ) OVER ( ORDER BY time_stamp )
<= INTERVAL '5' MINUTE
THEN 0
ELSE 1
END AS diff
FROM your_table
)
)
GROUP BY grp;
这篇关于如何根据一天中的任务完成情况从给定日期列中获取开始日期和结束日期(Oracle DB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
