select查询有关问题_Oracle管理

select查询问题
例子：
表a有3个字段，name,time,type
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...
要查询表全部数据，如果是name和type都相同，且time间隔小于5分钟的情况下只取一条（随便或者time的最大那条）；
查询结果
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...

------解决方案--------------------

SQL code

SQL> WITH t AS (  2      SELECT '1-1234' t_name,TO_DATE('2011-12-6 10:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL  3      SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL  4      SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:04','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL  5      SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:59:00','yyyy-mm-dd hh24:mi:ss') t_time,2 t_type FROM DUAL UNION ALL  6      SELECT '1-1234' t_name,TO_DATE('2011-12-6 14:30:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL  7      SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:32:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL  8      SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:35:00','yyyy-mm-dd hh24:mi:ss') t_time,4 t_type FROM DUAL  9  ) 10  SELECT m.t_name, 11         TO_CHAR(m.t_time,'yyyy-mm-dd hh24:mi:ss') t_time, 12         m.t_type 13    FROM ( 14  SELECT t.*, 15         COUNT(*) OVER(PARTITION BY t.t_name, t_type ORDER BY t.t_time RANGE BETWEEN CURRENT ROW AND INTERVAL '5' minute following) cn 16    FROM t 17    ) m 18    WHERE m.cn = 1 19  ;T_NAME T_TIME                  T_TYPE------ ------------------- ----------1-1234 2011-12-06 10:57:02          11-1234 2011-12-06 13:57:04          11-1234 2011-12-06 14:30:00          11-1234 2011-12-06 13:59:00          21-2222 2011-12-06 14:32:00          11-2222 2011-12-06 14:35:00          46 rows selected
------解决方案--------------------
实测数据：
SQL codeCREATE TABLE T37(    NAME VARCHAR2(20),    TIME    DATE,    TYPE    NUMBER(4));INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 10:57:02', 'YYYY-MM-DD HH24:MI:SS'), 1);INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:57:02', 'YYYY-MM-DD HH24:MI:SS'), 1);INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:57:04', 'YYYY-MM-DD HH24:MI:SS'), 1);INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:59:00', 'YYYY-MM-DD HH24:MI:SS'), 2);INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 14:30:00', 'YYYY-MM-DD HH24:MI:SS'), 1);INSERT INTO T37 VALUES('1-2222', to_date('2011-12-6 14:32:00', 'YYYY-MM-DD HH24:MI:SS'), 1);INSERT INTO T37 VALUES('1-2222', to_date('2011-12-6 14:35:00', 'YYYY-MM-DD HH24:MI:SS'), 4);
------解决方案--------------------
探讨

我以前还用过这个开窗函数，怎么就没想到了..
不过这个表的数据是百万级的，用开窗函数比较慢，有没有更好的方法吗？