现在有张表
LASTTIME TAGID
------------------- ----------
2011-11-09 05:34:21 1-2591
2011-11-09 07:10:43 1-2591
2011-11-09 19:44:15 1-2591
2011-11-09 19:44:20 1-2591
2011-11-09 19:44:22 1-2591
2011-11-09 19:45:27 1-2591
2011-11-09 19:45:29 1-2590
...
像上面这种情况，想要把 TAGID 在4分钟之内重复出现的记录只查询出时间最大的那条，不重复出现就显示那一条
实现下面这种情况
LASTTIME TAGID
------------------- ----------
2011-11-09 05:34:21 1-2591
2011-11-09 07:10:43 1-2591
2011-11-09 19:45:27 1-2591
2011-11-09 19:45:29 1-2590
...
如果是：
LASTTIME TAGID
------------------- ----------
2011-11-09 19:40:15 1-2591
2011-11-09 19:43:15 1-2591
2011-11-09 19:46:15 1-2591
2011-11-09 19:49:15 1-2591
这个情况，也是只显示最后一条
LASTTIME TAGID
------------------- ----------
2011-11-09 19:49:15 1-2591
这个数据是一个停车场的无线设备采集的数据，由于进出的时候车辆有个小时间的停顿，这个时候的无线设备还是一直在采集数据，所以要筛选。想问问大家怎么实现
------解决方案--------------------
- SQL code
SQL> WITH t AS ( 2 SELECT TO_DATE('2011-11-09 05:34:21','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 3 SELECT TO_DATE('2011-11-09 07:10:43','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 4 SELECT TO_DATE('2011-11-09 19:44:15','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 5 SELECT TO_DATE('2011-11-09 19:44:20','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 6 SELECT TO_DATE('2011-11-09 19:44:22','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 7 SELECT TO_DATE('2011-11-09 19:45:27','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2591' tagid FROM DUAL UNION ALL 8 SELECT TO_DATE('2011-11-09 19:45:29','yyyy-mm-dd hh24:mi:ss') lasttime,'1-2590' tagid FROM DUAL 9 ) 10 SELECT m.lasttime, 11 m.tagid 12 FROM (SELECT t.*, 13 COUNT(*) OVER(PARTITION BY tagid ORDER BY lasttime RANGE BETWEEN CURRENT ROW AND INTERVAL '4' minute following) cnt 14 FROM t) m 15 WHERE m.cnt = 1 16 ;LASTTIME TAGID----------- ------2011/11/09 1-25902011/11/09 1-25912011/11/09 1-25912011/11/09 1-2591