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          1
1-1234 2011-12-06 13:57:04          1
1-1234 2011-12-06 14:30:00          1
1-1234 2011-12-06 13:59:00          2
1-2222 2011-12-06 14:32:00          1
1-2222 2011-12-06 14:35:00          4

6 rows selected

------解决方案--------------------
实测数据：
SQL code

CREATE 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);

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

探讨


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