oracle函数range,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