数据结构如下:
时间 value值
2017-01-01 00:00:01 273
2017-01-01 00:01:01 272
2017-01-01 00:02:01 271
2017-01-01 00:03:01 272
2017-01-01 00:04:01 271
2017-01-01 00:05:01 265
2017-01-01 00:06:01 272
2017-01-01 00:07:01 276
2017-01-01 00:08:01 272
2017-01-01 00:09:01 278
2017-01-01 00:10:01 279
2017-01-01 00:11:01 268
2017-01-01 00:12:01 272
2017-01-01 00:13:01 271
2017-01-01 00:14:01 268
2017-01-01 00:15:01 271
2017-01-01 00:16:01 268求value值大于270的,持续时间大于等于4秒的,各次报警时长,例如上述例子应返回两条记录 开始时间 结束时间 时长
2017-01-01 00:00:01 2017-01-01 00:04:01 4秒
2017-01-01 00:06:01 2017-01-01 00:10:01 4秒由于数据库权限问题,不能使用存储过程,不能使用临时表,单纯sql完成,求各位大神指点
时间 value值
2017-01-01 00:00:01 273
2017-01-01 00:01:01 272
2017-01-01 00:02:01 271
2017-01-01 00:03:01 272
2017-01-01 00:04:01 271
2017-01-01 00:05:01 265
2017-01-01 00:06:01 272
2017-01-01 00:07:01 276
2017-01-01 00:08:01 272
2017-01-01 00:09:01 278
2017-01-01 00:10:01 279
2017-01-01 00:11:01 268
2017-01-01 00:12:01 272
2017-01-01 00:13:01 271
2017-01-01 00:14:01 268
2017-01-01 00:15:01 271
2017-01-01 00:16:01 268求value值大于270的,持续时间大于等于4秒的,各次报警时长,例如上述例子应返回两条记录 开始时间 结束时间 时长
2017-01-01 00:00:01 2017-01-01 00:04:01 4秒
2017-01-01 00:06:01 2017-01-01 00:10:01 4秒由于数据库权限问题,不能使用存储过程,不能使用临时表,单纯sql完成,求各位大神指点
select to_date('2017-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss') times,273 value from dual union all
select to_date('2017-01-01 00:01:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:02:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:03:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:04:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:05:01','yyyy-mm-dd hh24:mi:ss') times,265 value from dual union all
select to_date('2017-01-01 00:06:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:07:01','yyyy-mm-dd hh24:mi:ss') times,276 value from dual union all
select to_date('2017-01-01 00:08:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:09:01','yyyy-mm-dd hh24:mi:ss') times,278 value from dual union all
select to_date('2017-01-01 00:10:01','yyyy-mm-dd hh24:mi:ss') times,279 value from dual union all
select to_date('2017-01-01 00:11:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual union all
select to_date('2017-01-01 00:12:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:13:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:14:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual union all
select to_date('2017-01-01 00:15:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:16:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual
)
select times,time2,(time2-times)*24*60 from
(select times,lead(times,4)over(order by times) time2,value,
row_number()over(order by times) rn
from t
where value > 270)
where (time2-times)*24*60=4
; 只有等于4的,大于4的嗯还没想到
select to_date('2017-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss') times,273 value from dual union all
select to_date('2017-01-01 00:01:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:02:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:03:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:04:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:05:01','yyyy-mm-dd hh24:mi:ss') times,265 value from dual union all
select to_date('2017-01-01 00:06:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:07:01','yyyy-mm-dd hh24:mi:ss') times,276 value from dual union all
select to_date('2017-01-01 00:08:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:09:01','yyyy-mm-dd hh24:mi:ss') times,278 value from dual union all
select to_date('2017-01-01 00:10:01','yyyy-mm-dd hh24:mi:ss') times,279 value from dual union all
select to_date('2017-01-01 00:11:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual union all
select to_date('2017-01-01 00:12:01','yyyy-mm-dd hh24:mi:ss') times,272 value from dual union all
select to_date('2017-01-01 00:13:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:14:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual union all
select to_date('2017-01-01 00:15:01','yyyy-mm-dd hh24:mi:ss') times,271 value from dual union all
select to_date('2017-01-01 00:16:01','yyyy-mm-dd hh24:mi:ss') times,268 value from dual
)
select a.times,b.times,(b.times-a.times)*24*60
from (select t.*,row_number()over(order by times) rn from t where value > 270) a,
(select t.*,row_number()over(order by times) rn from t where value > 270) b
where b.rn-a.rn>=4
and (b.times-a.times)*24*60 >= 4
and (b.rn-a.rn)=(b.times-a.times)*24*60
;
嗯,这个是准确的了,支持大于和等于4,上面那个不支持大于4
http://bbs.csdn.net/topics/392136918?page=1#post-402173609