Oracle求时间的连续性

首先展示数据示例与运行结果

示例

 结果：

 

查询语句：

WITH TEST_TEST as 
(
select TO_DATE('2016-11-25 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-24 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-24 16:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-23 16:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-23 16:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-21 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-20 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-19 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-18 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-17 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-16 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-15 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-14 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-13 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
union all
select TO_DATE('2016-11-10 13:30:45', 'yyyy-MM-dd HH24:MI:SS') as FDATE from dual
)

select 
	MIN(TIME_VALUE) as START_TIME,
	MAX(TIME_VALUE) as END_TIME,
	count(*) as DAYS
from(
	select 
		TIME_VALUE,
		to_number(TIME_VALUE-TO_DATE('2000-01-01', 'YYYY-MM-DD'))-ROW_NUMBER() over(order by TIME_VALUE) as CS 
	from (
		select 
			distinct TO_DATE(TO_CHAR(FDATE, 'YYYY-MM-DD'),'YYYY-MM-DD') TIME_VALUE 
		from 
			TEST_TEST 
		)
	)
group by CS order by 1 ;