现在有张表
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
这个数据是一个停车场的无线设备采集的数据,由于进出的时候车辆有个小时间的停顿,这个时候的无线设备还是一直在采集数据,所以要筛选。想问问大家怎么实现
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
这个数据是一个停车场的无线设备采集的数据,由于进出的时候车辆有个小时间的停顿,这个时候的无线设备还是一直在采集数据,所以要筛选。想问问大家怎么实现
FROM 表名
GROUP BY TagId;
--author:cosio
--date:2011-11-10 16:00
--test sql
create table qiudf_T
(
iid int,
iTime DATE
) ;
INSERT INTO qiudf_T
(
select 1, to_date('2006-08-08 08:02:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 1, to_date('2006-08-08 08:03:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 1, to_date('2006-08-08 08:04:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 1, to_date('2006-08-08 08:06:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 2, to_date('2006-08-08 08:03:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 3, to_date('2006-08-08 08:03:00','yyyy-mm-dd hh24:mi:ss') FROM dual
union all
select 4, to_date('2006-08-08 12:02:00','yyyy-mm-dd hh24:mi:ss') FROM dual
);
COMMIT;--sql:
SELECT *
from qiudf_T a
where exists (
select 1 from qiudf_T
where iid=a.iid
and iTime > a.iTime
and to_char(itime,'mi')-to_char(a.itime,'mi')<=4)--result:
1 2006-8-8 8:02:00
1 2006-8-8 8:03:00
1 2006-8-8 8:04:00--把上面的数据删除,即把select 换成delete就OK了!
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
from
( select t.create_dt,lead(t.create_dt,1)over(partition by to_char(t.create_dt,'yyyymmdd') order by t.create_dt asc) create_dt2_lead1,
round(((lead(t.create_dt,1)over(partition by to_char(t.create_dt,'yyyymmdd') order by t.create_dt asc) -create_dt) )*24*60) diff_,
round((create_dt -(lag(t.create_dt,1)over(partition by to_char(t.create_dt,'yyyymmdd') order by t.create_dt asc)) )*24*60) diff_2
from wl_test t
) where diff_ > 4
order by 1