日期:2014-05-17  浏览次数:21018 次

时间段内重复值
现在有张表 
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-2590
2011/11/09  1-2591
2011/11/09  1-2591
2011/11/09  1-2591