这个查询该怎么写呀？

Question

有如下数据，现在要查询连续3秒以上sensor_spd大于等于100的记录,vtr_id可能有多个！vtr_id        rec_time           sensor_spd
4681         2010-8-2 15:07:37   100
4681         2010-8-2 15:07:38   110
4681         2010-8-2 15:07:39   120
4681         2010-8-2 15:07:40   130
4681         2010-8-2 15:07:41   140
4681         2010-8-2 15:07:42   150
4681         2010-8-2 15:07:43   110
4681         2010-8-2 15:07:45   60
4682         2010-8-2 15:07:37   110
4682         2010-8-2 15:07:38   50
4682         2010-8-2 15:07:39   100
4682         2010-8-2 15:07:45   70
4682         2010-8-2 15:07:46   77

Answer 1

select count(distinct vtr_id) from(
select vtr_id,rec_time,sensor_spd,count(flagnum) over(partition by p) kk from(
select vtr_id,rec_time,rec_time2,flagnum,sensor_spd,rn,rownum,rn-rownum p from(
select vtr_id,rec_time,rec_time2,
case when rec_time=rec_time2 + 1/(24*60*60) or rec_time2 is null then 1 else 0 end flagnum, sensor_spd,rownum rn from(
select vtr_id,rec_time,lag(rec_time) over(partition by vtr_id order by vtr_id) rec_time2,sensor_spd from temp  
)
) where flagnum = 1
)
) where kk > 2 and sensor_spd >= 100

Answer 2

SQL> select * from test;
 
               VTR_ID REC_TIME               SENSOR_SPD
----------------- ----------- ---------------------
                4681  2010-8-2 15:07:37        100
                4681  2010-8-2 15:07:38        110
                4681  2010-8-2 15:07:39        120
                4681  2010-8-2 15:07:40        130
                4681  2010-8-2 15:07:41        140
                4681  2010-8-2 15:07:42        150
                4681  2010-8-2 15:07:43        110
                4681  2010-8-2 15:07:45        60
                4682  2010-8-2 15:07:37        110
                4682  2010-8-2 15:07:38        50
                4682  2010-8-2 15:07:39        100
                4682  2010-8-2 15:07:45        70
                4682  2010-8-2 15:07:46        77 
13 rows selected 
SQL> 
SQL> WITH t1 AS (SELECT * FROM test t WHERE t.sensor_spd>=100),t2 AS(
  2  SELECT a.vtr_id,a.rec_time,a.sensor_spd,LEVEL lvl,connect_by_root(a.rec_time) root_time
  3    FROM t1 a
  4    START WITH NOT EXISTS(SELECT 1  FROM t1 b WHERE a.vtr_id=b.vtr_id AND b.rec_time=a.rec_time-1/24/60/60)
  5  CONNECT  BY PRIOR a.vtr_id=a.vtr_id AND PRIOR a.rec_time=a.rec_time-1/24/60/60)
  6  SELECT vtr_id,rec_time,sensor_spd FROM t2 c WHERE EXISTS(SELECT 1 FROM t2 d WHERE c.vtr_id=d.vtr_id AND c.root_time=d.root_time AND d.lvl>=3)
  7  ORDER BY vtr_id,rec_time ASC;
 
               VTR_ID REC_TIME               SENSOR_SPD
--------------------- ----------- ---------------------
               4681 2010-8-2 15:07:37      100
               4681 2010-8-2 15:07:38      110
               4681 2010-8-2 15:07:39      120
               4681 2010-8-2 15:07:40      130
               4681 2010-8-2 15:07:41      140
               4681 2010-8-2 15:07:42      150
               4681 2010-8-2 15:07:43      110
 
7 rows selected
 
SQL> 

Answer 3

SQL> WITH t1 AS (SELECT * FROM test WHERE sensor_spd>=100),
  2  t2 AS(
  3  SELECT vtr_id,rec_time,sensor_spd,rec_time-row_number()over(PARTITION BY vtr_id ORDER BY rec_time ASC)*1/24/60/60 avg_time
  4  FROM t1 ),t3 AS(SELECT vtr_id,avg_time FROM t2 GROUP BY vtr_id,avg_time HAVING COUNT(*)>=3)
  5  SELECT vtr_id,rec_time,sensor_spd FROM  t2   WHERE EXISTS(SELECT 1 FROM t3 WHERE t2.vtr_id=t3.vtr_id AND t2.avg_time=t3.avg_time)
  6  ORDER BY vtr_id,rec_time ASC;
 
               VTR_ID REC_TIME               SENSOR_SPD
--------------------- ----------- ---------------------
                  4681    2010-8-2 15:07:37         100
                  4681    2010-8-2 15:07:38         110
                  4681    2010-8-2 15:07:39         120
                  4681    2010-8-2 15:07:40         130
                  4681    2010-8-2 15:07:41         140
                  4681    2010-8-2 15:07:42         150
                  4681    2010-8-2 15:07:43         110
 
7 rows selected
 
SQL> 

Answer 4

select vtr_id,rec_time,sensor_spd
from 
(select vtr_id,rec_time,sensor_spd,count(1) over(partition by vtr_id,rn1) rt
from(select vtr_id,rec_time,rec_time-numtodsinterval(row_number() over(partition by vtr_id order by rec_time),'second') rn1,
sensor_spd from t1
where sensor_spd>=100) )
where rt>=3

Answer 5

谢谢各位大虾的帮助，如果是求两条记录之间sensor_spd的值相差大于5的总记录数该怎么写？数据都是每秒一条的！