如何根据一天内的任务完成情况从给定日期列获取开始日期和结束日期(oracle)

Question

在虚拟数据中，您可以看到有两列Task_Completion和Time_stamp.有一个Java调度程序，每当任务完成时运行，例如，调度程序在2016年2月15日运行5次，仅在2月17日运行 所以我想要一个查询来计算给定列time_stamp的start_time和end_time

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                     18-FEB-16 10.19.04.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
18-FEB-16 10.16.33.333000000 PM          18-FEB-16 10.21.33.333000000 PM

有两个条件

条件 1. 如果time_stamp有0-5 分钟的间隔，例如在15-Feb上，那么start_time将是 11.37.56.013000000，end_time将是 11.47.48.970000000，但如果没有，那么再次检查当天是否有任何计划运行，例如 2 月 15 日 12.21.57.587000000(start_time( 和12.26.55.767000000(end_time(

条件2. 如果计划一天只运行一次，则平均该time_stamp并从该时间开始添加/订阅2.30 分钟(即无需再次检查(

例如

17-2 月 10.21.33.320000000(start_time( 和 10.26.33.320000000(end_time(与 18-2月 18日相同

所以我的逻辑是，同时做条件 1 和条件 2 的联合，这就是我最终得到的......

--------------------condition2-------------------
select count(*), min(time_stamp) - interval '150' second start_date, max(time_stamp) + interval '150' second end_date
from serverstatus 
group by to_char(time_stamp,'dd-mon-yy')
having count(*)=1
UNION ALL
------------condition1 query--------------------------

我需要条件 1 查询

Answer 1

SELECT start_time - CASE num_grp_per_day
WHEN 1
THEN INTERVAL '150' SECOND
ELSE INTERVAL '0' SECOND
END AS start_time,
end_time   + CASE num_grp_per_day
WHEN 1
THEN INTERVAL '150' SECOND
ELSE INTERVAL '0' SECOND
END AS end_time
FROM   (
SELECT DISTINCT
MIN( time_stamp ) OVER ( PARTITION BY grp ) AS start_time,
MAX( time_stamp ) OVER ( PARTITION BY grp ) AS end_time,
COUNT( DISTINCT grp ) OVER ( PARTITION BY TRUNC( time_stamp ) ) AS num_grp_per_day
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
)
)
)
ORDER BY start_time;

或者，如果您只想将所有零宽度组扩展到 5 分钟间隔(而不仅仅是每天有一个(，那么：

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;