3,491
社区成员
发帖
与我相关
我的任务
分享
create table aa (datetime date)
insert into aa(datetime) values(to_date('2011-3-22 12:00:01','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:02','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:03','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:11','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:12','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:40','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:41','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:42','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:43','YYYY-MM-DD HH24:MI:SS'));
with ab as (
select datetime
, max(datetime) over (order by datetime rows between 1 preceding and 1 preceding) as PREV_VALUE
, row_number() over (order by datetime) as IDX
from aa
)
select min(datetime) as START_VALUE, max(datetime) as END_VALUE, count(*) as CNT
from ab
start with PREV_VALUE is null or datetime > PREV_VALUE + 3/86400
connect by IDX = Prior IDX + 1 and datetime < prior datetime + 3/86400
group by rownum - level
order by START_VALUE
;
START_VALUE END_VALUE CNT
3/22/2011 12:00:01 PM 3/22/2011 12:00:03 PM 3
3/22/2011 12:00:11 PM 3/22/2011 12:00:12 PM 2
3/22/2011 12:00:40 PM 3/22/2011 12:00:43 PM 4
create table aa (datetime date)
insert into aa(datetime) values(to_date('2011-3-22 12:00:01','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:02','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:03','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:04','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:05','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:06','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:07','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:08','YYYY-MM-DD HH24:MI:SS'));
insert into aa(datetime) values(to_date('2011-3-22 12:00:09','YYYY-MM-DD HH24:MI:SS'));
--这个问题如果要用单纯的SQL来解决,可能要参考Oracle model来做
--要不然你只好用PLSQL过程控制来一笔一笔游标判断再做