Oracle中range的使用
-- 需求：求出下表的一个小时内id个数的最大值和最小值。SQL> WITH t AS (2SELECT 1 "id",TO_DATE('2011-04-27 14:05:12','yyyy-mm-dd hh24:mi:ss') c_time FROM DUAL UNION ALL3SELECT 2,TO_DATE('2011-04-27 15:10:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL4SELECT 3,TO_DATE('2011-04-27 15:20:52','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL5SELECT 4,TO_DATE('2011-06-27 15:12:12','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL6SELECT 5,TO_DATE('2011-06-27 15:25:52','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL7SELECT 6,TO_DATE('2011-06-27 15:32:12','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL8SELECT 7,TO_DATE('2011-06-28 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL9SELECT 8,TO_DATE('2011-07-11 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL 10SELECT 9,TO_DATE('2011-07-11 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL 11SELECT 10,TO_DATE('2011-07-22 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL UNION ALL 12SELECT 11,TO_DATE('2011-07-23 15:25:42','yyyy-mm-dd hh24:mi:ss') FROM DUAL 13) 14SELECT "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) cntFROM 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_hourFROM (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
页:
[1]