根据时间范围对列进行分组

Question

我有一个视图（在Oracle中），如下所示，它由三个表连接而成：

Col1   Col2    Time
====   =====   =====
txt1   same0   10:50
txt2   same0   10:51
txt3   same0   16:30
txt4   same0   10:54
txt5   same1   15:15
txt6   same2   16:31
txt7   same3   08:05
txt8   same3   08:07

我想要一个对时间小于5分钟的col2数据进行分组的查询（例如），并对分组在一起的行进行计数。所以查询结果应该是这样的：

Col1             Col2    Time                Count
====             =====   =====               =====
txt1,txt2,txt4   same0   10:50,10:51,10:54   3
txt3             same0   16:30               1
txt5             same1   15:15               1
txt6             same2   16:31               1
txt7,txt8        same3   08:05,08:07         2

Time列的数据类型是DATE，但为了更好地理解，这里对其进行了简化

编辑：

在以下情况下（来自注释），最大Count为5是有用的。例如：

Col1   Col2    Time
====   =====   =====
txt1   same0   10:50
txt2   same0   10:51
txt3   same0   10:52
txt4   same0   10:53
txt5   same0   10:54
txt6   same0   10:55
txt7   same0   10:56

应该是：

Col1                       Col2    Time                            Count
====                       =====   =====                           =====
txt1,txt2,txt3,txt4,txt5   same0   10:50,10:51,10:52,10:53,10:54   5
txt6,txt7                  same0   10:55,10:56                     2

Answer 1

SQL Fiddle

如果您想按5分钟的间隔（即10:45-10:49、10:50-10:54、10:55-11:00等）对值进行分组，则可以执行以下操作：

Oracle 11g R2架构设置:

CREATE TABLE TEST ( Col1, Col2, Time ) AS
          SELECT 'txt01', 'same0', TO_DATE( '2015-06-29 10:49', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt02', 'same0', TO_DATE( '2015-06-29 10:50', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt03', 'same0', TO_DATE( '2015-06-29 10:51', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt04', 'same0', TO_DATE( '2015-06-29 10:52', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt05', 'same0', TO_DATE( '2015-06-29 10:53', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt06', 'same0', TO_DATE( '2015-06-29 10:54', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt07', 'same0', TO_DATE( '2015-06-29 10:55', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt08', 'same0', TO_DATE( '2015-06-29 10:56', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt09', 'same0', TO_DATE( '2015-06-29 16:30', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt10', 'same1', TO_DATE( '2015-06-29 15:15', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt11', 'same2', TO_DATE( '2015-06-29 16:31', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt12', 'same3', TO_DATE( '2015-06-29 08:05', 'YYYY-MM-DD HH24:MI' ) FROM DUAL
UNION ALL SELECT 'txt13', 'same3', TO_DATE( '2015-06-29 08:07', 'YYYY-MM-DD HH24:MI' ) FROM DUAL

查询1：

SELECT LISTAGG( Col1, ',' ) WITHIN GROUP ( ORDER BY Time ) AS Col1,
       Col2,
       LISTAGG( TO_CHAR( Time, 'HH24:MI' ), ',' ) WITHIN GROUP ( ORDER BY Time ) AS Time,
       COUNT(1) AS "Count"
FROM   TEST
GROUP BY
       Col2,
       TRUNC( Time ),
       FLOOR( ( TO_NUMBER( TO_CHAR( Time, 'HH24' ) ) * 60 + TO_NUMBER( TO_CHAR( Time, 'MI' ) ) ) / 5 )

结果：

|                          COL1 |  COL2 |                          TIME | Count |
|-------------------------------|-------|-------------------------------|-------|
|                         txt01 | same0 |                         10:49 |     1 |
| txt02,txt03,txt04,txt05,txt06 | same0 | 10:50,10:51,10:52,10:53,10:54 |     5 |
|                   txt07,txt08 | same0 |                   10:55,10:56 |     2 |
|                         txt09 | same0 |                         16:30 |     1 |
|                         txt10 | same1 |                         15:15 |     1 |
|                         txt11 | same2 |                         16:31 |     1 |
|                   txt12,txt13 | same3 |                   08:05,08:07 |     2 |

如果你想让最多5行的组都在同一个5分钟的时间段内，并且可以从任何时候开始，那么你可以使用流水线函数：

Oracle 11g R2架构设置:

CREATE TYPE TEST_GROUP_OBJ AS OBJECT(
  Col1 VARCHAR2(54), -- 5 * Length of Col1 + 4
  Col2 VARCHAR2(10), -- Length of Col2
  Time VARCHAR2(29)  -- 5 * Length of 'HH:MI' + 4
)
/
CREATE TYPE TEST_GROUP_TAB AS TABLE OF TEST_GROUP_OBJ
/
CREATE FUNCTION getFiveMinuteGroupings
RETURN TEST_GROUP_TAB PIPELINED
AS
  TYPE TEST_TAB IS TABLE OF TEST%ROWTYPE;
  t_test_tab TEST_TAB;
  v_time     TEST.TIME%TYPE;
  v_grp      TEST_GROUP_OBJ := TEST_GROUP_OBJ( NULL, NULL, NULL );
  v_count    NUMBER(1,0);
BEGIN
  SELECT *
  BULK COLLECT INTO t_test_tab
  FROM TEST
  ORDER BY Col2, Time;
  IF t_test_tab.COUNT = 0 THEN
    RETURN;
  END IF;
  v_time     := t_test_tab(1).TIME;
  v_grp.COL1 := t_test_tab(1).COL1;
  v_grp.COL2 := t_test_tab(1).COL2;
  v_grp.TIME := TO_CHAR( t_test_tab(1).TIME, 'HH24:MI' );
  v_count    := 1;
  FOR i IN 2 .. t_test_tab.COUNT LOOP
    IF    t_test_tab(i).COL2 =  v_grp.COL2
      AND t_test_tab(i).TIME <= v_time + INTERVAL '5' MINUTE
      AND v_count < 5
    THEN
      v_grp.COL1 := v_grp.COL1 || ',' || t_test_tab(i).COL1;
      v_grp.TIME := v_grp.TIME || ',' || TO_CHAR( t_test_tab(i).TIME, 'HH24:MI' );
      v_count    := v_count + 1;
    ELSE
      PIPE ROW( v_grp );
      v_time     := t_test_tab(i).TIME;
      v_grp.COL1 := t_test_tab(i).COL1;
      v_grp.COL2 := t_test_tab(i).COL2;
      v_grp.TIME := TO_CHAR( t_test_tab(i).TIME, 'HH24:MI' );
      v_count    := 1;
    END IF;
  END LOOP;
  PIPE ROW( v_grp );
END;
/

查询2：

SELECT *
FROM   TABLE( getFiveMinuteGroupings() )

结果：

|                          COL1 |  COL2 |                          TIME |
|-------------------------------|-------|-------------------------------|
| txt01,txt02,txt03,txt04,txt05 | same0 | 10:49,10:50,10:51,10:52,10:53 |
|             txt06,txt07,txt08 | same0 |             10:54,10:55,10:56 |
|                         txt09 | same0 |                         16:30 |
|                         txt10 | same1 |                         15:15 |
|                         txt11 | same2 |                         16:31 |
|                   txt12,txt13 | same3 |                   08:05,08:07 |

Answer 2

这个查询给了我想要的输出：

select  
    listagg(col1, ', ') within group (order by col1) col1, col2, 
    listagg(to_char(ttime, 'hh24:mi'), ', ') within group (order by ttime) as ttime,
    count(1) cnt
  from (
    select col1, col2, ttime, trunc(ttime, 'dd') + floor(to_char(ttime,'sssss')/300)/288 tmp
      from test)
  group by col2, tmp

^{SQLFiddle演示}