表 T_SIGNALid create_time value
------ ------------- ----
1 2010-03-01 01:00:00 10
2 2010-03-01 01:03:00 10
3 2010-03-01 01:04:00 10
4 2010-03-01 01:05:00 10
5 2010-03-01 01:07:00 10
6 2010-03-01 01:09:00 10
7 2010-03-01 01:12:00 10
8 2010-03-01 01:14:00 10
9 2010-03-01 01:25:00 10
10 2010-03-01 02:10:00 10 按时间间隔4分钟从上表中取数据(每条数据的create_time肯定比上一条的的create_time大于或等于4分钟)。
取得应结果为:id create_time value
------ ------------- ----
1 2010-03-01 01:00:00 10
3 2010-03-01 01:04:00 10
6 2010-03-01 01:09:00 10
8 2010-03-01 01:14:00 10
9 2010-03-01 01:25:00 10
10 2010-03-01 02:10:00 10 现用一条SQL语句(oracle)实现,该怎么写呢?
(
select 1 f1, to_date('2010-03-01 01:00:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 2 f1, to_date('2010-03-01 01:03:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 3 f1, to_date('2010-03-01 01:04:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 4 f1, to_date('2010-03-01 01:05:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 5 f1, to_date('2010-03-01 01:07:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 6 f1, to_date('2010-03-01 01:09:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 7 f1, to_date('2010-03-01 01:12:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 8 f1, to_date('2010-03-01 01:14:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 9 f1, to_date('2010-03-01 01:25:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 10 f1, to_date('2010-03-01 02:10:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
)
select min(t1.f1) f1,min(t1.f2) f2,min(t1.f3) f3,level
from t1
start with f1 =1
connect by f1 > prior f1
and f2 >= prior f2 + 4/24/60
group by level
order by level
(select id ,create_time,value,lag(create_time) over(order by id) as pre_time
from T_SIGNAL)
where (create_time-pre_time)*24*60>4
你描述的需求和你要求得到的结果有出入...
一般要通过树型结构的语句或者分析函数来处理
关于树型查询的语句start with..connect by可参考
1.http://t.00-1.cn/oracle-01e1a85d6d06f1fd.html
2.http://hi.baidu.com/deepsee/blog/item/67ae46107f4f3ef7c3ce79ea.html
关于分析函可参考
1.http://blog.csdn.net/youjianbo_han_87/archive/2009/06/25/4297867.aspx
2.http://oracle.chinaitlab.com/induction/801521.html
修改了一下写法,如下所示。
还有一个问题,这种写法会等待start with 的树建立完成之后才会过滤每个level的第一个node。好像效率不高。
有没有办法在建立start with的时候就只建立最左边的那一个分支啊。
麻烦高手解答一下。with t1 as
(
select 1 f1, to_date('2010-03-01 01:00:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 2 f1, to_date('2010-03-01 01:03:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 3 f1, to_date('2010-03-01 01:04:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 4 f1, to_date('2010-03-01 01:05:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 5 f1, to_date('2010-03-01 01:07:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 6 f1, to_date('2010-03-01 01:09:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 7 f1, to_date('2010-03-01 01:12:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 8 f1, to_date('2010-03-01 01:14:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 9 f1, to_date('2010-03-01 01:25:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 10 f1, to_date('2010-03-01 02:10:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
)
select f1,f2,f3
from
(
select t1.f1,f2,f3,level,row_number() over(partition by level order by rownum ) N1
from t1
start with f1 =1
connect by f1 > prior f1
and f2 >= prior f2 + 4/24/60
)
where n1 = 1
select 1 id ,to_date('2010-03-01 01:00:00','yyyy-mm-dd hh24:mi:ss')create_time,10 value from dual union all
select 2,to_date('2010-03-01 01:03:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 3,to_date('2010-03-01 01:04:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 4,to_date('2010-03-01 01:05:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 5,to_date('2010-03-01 01:07:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 6,to_date('2010-03-01 01:09:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 7,to_date('2010-03-01 01:12:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 8,to_date('2010-03-01 01:14:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 9,to_date('2010-03-01 01:25:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all
select 10,to_date('2010-03-01 02:10:00','yyyy-mm-dd hh24:mi:ss'),10 from dual)
select *
from tt t
start with id = 1
connect by prior create_time <= create_time - 4 / 60 / 24
and (select max(create_time) keep(dense_rank last order by id)
from tt
where id < t.id) < prior create_time + 4 / 60 / 24