--然后序列号对5取余,如果整除,说明是间隔5 select id,count(*) from ( --先取得按ID分组得到排序的序列号, select id,value,row_number()over(partition by id order by value)-1 rn from tab ) where mod(rn,5)=0 group by id
没看懂题,不同ID之间的差值?还是相同ID不同记录之间?
相同ID之间Value字段的差值大于5
SQL> select * from tt;
ID VALUE --- ----- 1 12 1 18 1 19 2 10 2 19 2 28
6 rows selected
SQL> SQL> WITH t1 AS (SELECT ID ,VALUE,ROW_number()over(PARTITION BY ID ORDER BY VALUE ASC) rn FROM tt) 2 SELECT ID,COUNT(*) FROM t1 a 3 WHERE EXISTS(SELECT 1 FROM t1 b WHERE a.id=b.id AND b.rn=a.rn+1 AND b.value>=a.value+5) 4 GROUP BY ID 5 ;
ID COUNT(*) --- ---------- 1 1 2 2
SQL>
--然后序列号对5取余,如果整除,说明是间隔5 select vtr_id,count(*) from ( --先取得按ID分组得到排序的序列号, select vtr_id,sensor_spd,row_number()over(partition by vtr_id order by sensor_spd)-1 rn from GIS_WEICHAI_DATA_1S ) --为了便于调试,我改了表中的数据,1000,3000,5000,6000 各一条 where mod(rn,2000)=0 group by vtr_id 查询结果为: 4681 2 1
select vtr_id,count(*) from ( --先取得按ID分组得到排序的序列号, select vtr_id,sensor_spd,row_number()over(partition by vtr_id order by sensor_spd)-1 rn from GIS_WEICHAI_DATA_1S ) --为了便于调试,我改了表中的数据,1000,3000,5000,6000 各一条 where mod(rn,2000)=0 group by vtr_idorder by sensor_spd 改成 order by vtr_id 试试
select id,count(*) from (
--先取得按ID分组得到排序的序列号,
select id,value,row_number()over(partition by id order by value)-1 rn from tab )
where mod(rn,5)=0
group by id
ID VALUE
--- -----
1 12
1 18
1 19
2 10
2 19
2 28
6 rows selected
SQL>
SQL> WITH t1 AS (SELECT ID ,VALUE,ROW_number()over(PARTITION BY ID ORDER BY VALUE ASC) rn FROM tt)
2 SELECT ID,COUNT(*) FROM t1 a
3 WHERE EXISTS(SELECT 1 FROM t1 b WHERE a.id=b.id AND b.rn=a.rn+1 AND b.value>=a.value+5)
4 GROUP BY ID
5 ;
ID COUNT(*)
--- ----------
1 1
2 2
SQL>
select vtr_id,count(*) from (
--先取得按ID分组得到排序的序列号,
select vtr_id,sensor_spd,row_number()over(partition by vtr_id order by sensor_spd)-1 rn from GIS_WEICHAI_DATA_1S )
--为了便于调试,我改了表中的数据,1000,3000,5000,6000 各一条
where mod(rn,2000)=0
group by vtr_id
查询结果为:
4681 2
1
写点在下面吧:
VTR_ID SENSOR_SPD REC_TIME
---------- ---------- --------------
4681 1000 02-8月 -10
4681 3000 02-8月 -10
4681 5000 02-8月 -10
4681 6000 02-8月 -10
4681 1110 02-8月 -10
谢谢这位大哥了,如果用下面的数据,返回结果有点问题,能不能再想想办法:
VTR_ID SENSOR_SPD REC_TIME
---------- ---------- --------------
4681 1000 02-8月 -10
4681 3000 02-8月 -10
4681 5000 02-8月 -10
4681 6000 02-8月 -10
4681 1110 02-8月 -10
--先取得按ID分组得到排序的序列号,
select vtr_id,sensor_spd,row_number()over(partition by vtr_id order by sensor_spd)-1 rn from GIS_WEICHAI_DATA_1S )
--为了便于调试,我改了表中的数据,1000,3000,5000,6000 各一条
where mod(rn,2000)=0
group by vtr_idorder by sensor_spd
改成 order by vtr_id 试试