Oracle中range的使用

-- 需求：求出下表的一个小时内id个数的最大值和最小值。
SQL> WITH t AS (
  2  SELECT 1 "id",TO_DATE('2011-04-27 14:05:12','yyyy-mm-dd hh24:mi:ss') c_time FROM DUAL UNION ALL
  3  SELECT 2,TO_DATE('2011-04-27 15:10:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  4  SELECT 3,TO_DATE('2011-04-27 15:20:52','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  5  SELECT 4,TO_DATE('2011-06-27 15:12:12','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  6  SELECT 5,TO_DATE('2011-06-27 15:25:52','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  7  SELECT 6,TO_DATE('2011-06-27 15:32:12','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  8  SELECT 7,TO_DATE('2011-06-28 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
  9  SELECT 8,TO_DATE('2011-07-11 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
 10  SELECT 9,TO_DATE('2011-07-11 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
 11  SELECT 10,TO_DATE('2011-07-22 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL
 12  SELECT 11,TO_DATE('2011-07-23 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL
 13  )
 14  SELECT "id",TO_CHAR(c_time,'yyyy-mm-dd hh24:mi:ss') FROM t ORDER BY 2;

        id TO_CHAR(C_TIME,'YYYY-MM-DDHH24:MI:SS')
---------- --------------------------------------
         1 2011-04-27 14:05:12
         2 2011-04-27 15:10:42
         3 2011-04-27 15:20:52
         4 2011-06-27 15:12:12
         5 2011-06-27 15:25:52
         6 2011-06-27 15:32:12
         7 2011-06-28 15:25:42
         8 2011-07-11 15:25:42
         9 2011-07-11 15:25:42
        10 2011-07-22 15:25:42
        11 2011-07-23 15:25:42
-- 使用分析函数,range
SELECT t."id",
       TO_CHAR(c_time, 'yyyy-mm-dd hh24:mi:ss'),
       COUNT(*) OVER(ORDER BY c_time RANGE BETWEEN CURRENT ROW AND INTERVAL '1' hour following) cnt
  FROM t;
-- 其中range表示范围，between...and 表示之前的范围和之后的范围
-- CURRENT ROW表示当前行，INTERVAL '1'表示一个小时
-- 结果如下：
        id C_TIME                                        CNT
---------- -------------------------------------- ----------
         1 2011-04-27 14:05:12                             1
         2 2011-04-27 15:10:42                             2
         3 2011-04-27 15:20:52                             1
         4 2011-06-27 15:12:12                             3
         5 2011-06-27 15:25:52                             2
         6 2011-06-27 15:32:12                             1
         7 2011-06-28 15:25:42                             1
         8 2011-07-11 15:25:42                             2
         9 2011-07-11 15:25:42                             2
        10 2011-07-22 15:25:42                             1
        11 2011-07-23 15:25:42                             1

-- 最终的sql:
SELECT MIN(cnt) min_hour,
       MAX(cnt) max_hour
  FROM (SELECT COUNT(*) over(ORDER BY c_time RANGE BETWEEN CURRENT ROW AND INTERVAL '1' hour following) cnt
          FROM t)
-- 结果：
  MIN_HOUR   MAX_HOUR
---------- ----------
         1          3