Oracle中range的使用

hudingchen

-- 需求：求出下表的一个小时内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-- 使用分析函数,rangeSELECT 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