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