Oracle中range的使用

晓楠833

-- 需求：求出下表的一个小时内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