【函式】oracle視窗函式over()的理解

over()開窗函式和聚合函式的不同之處是對於每個組返回多行，而聚合函式對於每個組只返回一行。

開窗函式指定了分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：
over（order by salary） 按照salary排序進行累計，order by是個預設的開窗函式
over（partition by deptno）按照部門分割槽
over（order by salary range between 50 preceding and 150 following）
每行對應的資料視窗是之前行幅度值不超過50，之後行幅度值不超過150
over（order by salary rows between 50 preceding and 150 following）
每行對應的資料視窗是之前50行，之後150行
over（order by salary rows between unbounded preceding and unbounded following）
每行對應的資料視窗是從第一行到最後一行，等效：
over（order by salary range between unbounded preceding and unbounded following）

可能細心的人會看到有range between和rows between兩種寫法，其實一開始我也挺困惑的，做了些實驗，下面看一下。

SQL> select * from t1;

ID VALUE
---------- ----------
1 5
1 6
1 10
2 2
2 4
2 7

6 rows selected.

SQL> select id,value,
2 sum(value) over(partition by id order by value range between 1 preceding an
d 1 following) by_range,
3 sum(value) over(partition by id order by value rows between 1 preceding and
1 following) by_rows
4 from t1
5 order by id;

ID VALUE BY_RANGE BY_ROWS
---------- ---------- ---------- ----------
1 5 11 11
1 6 11 21 --這裡5和6之間相差1 在RANGE內 因此把5和6相加得到11
1 10 10 16 --但是6和10之間相差4 在RANGE外 因此這裡就顯示出的是10 並沒有和6相加
2 2 2 6
2 4 4 13
2 7 7 11 --2和4 4和7之間相差分別為2和3 均在RANGE範圍外 因此都顯示了當前行的原值

6 rows selected.

SQL> select id,value,
2 sum(value) over(partition by id order by value range between 4 preceding an
d 4 following) by_range,
3 sum(value) over(partition by id order by value rows between 1 preceding and
1 following) by_rows
4 from t1
5 order by id;

ID VALUE BY_RANGE BY_ROWS
---------- ---------- ---------- ----------
1 5 11 11
1 6 21 21
1 10 16 16
2 2 6 6
2 4 13 13
2 7 11 11 --增大了RANGE範圍到4 這時得到的結果就和ROWS得到的相同了 因為在VALUE列中前後兩行相差最大就是4

6 rows selected.

RANGE只指定了前後兩個值之間相差值的範圍，而ROWS則指定了前後多少行的範圍。

警告：有些視窗函式強制要求對分割槽中的行排序。因此，對於有些視窗函式，ORDER BY子句是必需的。

當在視窗函式的OVER子句中使用ORDER BY子句時，就指定了兩件事：

1. 分割槽中的行如何排序
2. 在計算中包含哪些行

SQL> select deptno,ename,hiredate,sal,
2 sum(sal) over(partition by deptno) sum1,
3 sum(sal) over(partition by deptno order by hiredate) sum2
4 from emp
5 where deptno=10;

DEPTNO ENAME HIREDATE SAL SUM1 SUM2
---------- ---------- --------------- ---------- ---------- ----------
10 CLARK 09-JUN-81 2450 8750 2450
10 KING 17-NOV-81 5000 8750 7450
10 MILLER 23-JAN-82 1300 8750 8750

SUM1中沒有指定ORDER BY，計算出來的是部門10中3名員工的總工資。而在SUM2中使用了ORDER BY子句，其實下面兩句效果相同：

sum(sal) over(partition by deptno order by hiredate)

sum(sal) over(partition by deptno order by hiredate range between unbounded preceding and current row)

下面再透過一個較為全面的例子，來展示一下ORDER BY中範圍指定對查詢輸出的影響

SQL> select ename,sal,
2 min(sal) over(order by sal) min1,
3 max(sal) over(order by sal) max1,
4 min(sal) over(order by sal range between unbounded preceding and unbounded
following) min2,
5 max(sal) over(order by sal range between unbounded preceding and unbounded
following) max2,
6 min(sal) over(order by sal range between current row and current row) min3,

7 max(sal) over(order by sal range between current row and current row) max3,

8 max(sal) over(order by sal rows between 3 preceding and 3 following) max4
9 from emp;

ENAME SAL MIN1 MAX1 MIN2 MAX2 MIN3 MAX3 MAX4
---------- ----- ----- ----- ----- ----- ----- ---------- ----------
SMITH 800 800 800 800 5000 800 800 1250
JAMES 950 800 950 800 5000 950 950 1250
ADAMS 1100 800 1100 800 5000 1100 1100 1300
WARD 1250 800 1250 800 5000 1250 1250 1500
MARTIN 1250 800 1250 800 5000 1250 1250 1600
MILLER 1300 800 1300 800 5000 1300 1300 2450
TURNER 1500 800 1500 800 5000 1500 1500 2850
ALLEN 1600 800 1600 800 5000 1600 1600 2975
CLARK 2450 800 2450 800 5000 2450 2450 3000
BLAKE 2850 800 2850 800 5000 2850 2850 3000
JONES 2975 800 2975 800 5000 2975 2975 5000

ENAME SAL MIN1 MAX1 MIN2 MAX2 MIN3 MAX3 MAX4
---------- ----- ----- ----- ----- ----- ----- ---------- ----------
SCOTT 3000 800 3000 800 5000 3000 3000 5000
FORD 3000 800 3000 800 5000 3000 3000 5000
KING 5000 800 5000 800 5000 5000 5000 5000

14 rows selected.