分析函數一 (OVER) (DML 六)

官網
彙總函數OVER(xxx)的語法，彙總函數之前有介紹到，這裡主要說明OVER後面的語法，彙總函數OVER(xxx)是個整體，xxx一改，彙總函數也會不一樣

※彙總函數和欄位一起顯示

SELECT sum(sal) s, e.* FROM emp e;
SELECT sum(sal) OVER() s, e.* FROM emp e;

※結果：

※第一個SELECT會發生錯誤，因為彙總函數不能和欄位一起顯示

※使用上一篇的 GROUP BY 不能顯示其他的欄位，所以用分析函數

※GROUP BY 因為其他的欄位都不一樣，所以不知道要顯示什麼？這裡用分析函數最前面是sum，所以它會加總，然後每一行都一樣

※分區

SELECT deptno, sum(sal) FROM emp GROUP BY deptno ORDER BY deptno;
SELECT distinct sum(sal) OVER(PARTITION BY deptno) s, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno) s, e.sal, e.deptno FROM emp e;

※結果：

※第一和第二個SELECT結果一樣，但如果想顯示其他的欄位，就只好用分析函數了

※分區多個欄位

SELECT sum(sal) OVER(PARTITION BY deptno, job) s, e.sal, e.job, e.deptno FROM EMP e;

※結果：

※會以deptno分區完後，再分區job，注意順序

※注意改成兩個分區後，會以兩個分區加總，看S欄位的變化

※分區排序

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY ename) s, e.sal, e.ename, e.deptno FROM EMP e;

※結果：

※可看出ename由小到大排序了(ASC)，也可以 ORDER BY 多個欄位

※因為此範例 ENAME 沒有重覆值，所以S欄位，每一個都會累加

※以 DEPTNO 排序會有重覆值，如下：

SELECT sum(sal) OVER(ORDER BY deptno) s, e.sal, e.deptno FROM emp e;

※結果：

※這時會以DEPTNO為單位加總，和上面的分區結果圖對照(第二張圖)，會發現這裡的語法沒有PARTITION BY，所以不會分區，而S欄位一開始會以10部門加總，這時看起來一樣，但第二次會將20部門加總再加上10部門的總和，這時就和第二張圖不一樣了，然後依此類推

※NULL顯示在前/後

SELECT sum(sal) OVER(ORDER BY comm NULLS FIRST) s, e.sal, e.comm, e.deptno FROM emp e;

※結果：

※加上NULLS FIRST會將有null的放在最前面，而NULLS LAST會放在最後面，如果都不打，等同NULLS LAST

※向上範圍

關鍵字是PRECEDING

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) s, e.sal, e.deptno FROM emp e;

※結果：

※sal RANGE 300 PRECEDING，表示薪水欄位往上在300之內(含)才會加總，如果往上在300之內會一直往上判斷，直到沒有或300之外才結束判斷

※以20部門為例，看中間的薪水欄
．第一個800，往上沒有值，所以左欄還是800
．第二個1100，減第一個800是300，所以加總(中間欄往上是300之內都會加)
左欄變成1100 + 800 = 1900
．第三個2975，減第二個1100並沒有在300之內，所以左欄還是2975
注意因為是以sal排序，所以一樣的會是一組
而因為第四、五個的薪水都是3000，所以是一組
．第四、五個3000，減第三個2975在300之內，所以會加總(中間欄往上是300之內都會加)，而且一次將第四、五個加起來，變成 3000 + 3000 + 2975 = 8975

※再練習一題，如果將RANGE改成350，會變成如下的狀況

※以30部門為例，看中間的薪水欄
．第一個950，往上沒有值，所以左欄還是950
．第二、三個是1250，減第一個950在350之內，所以加總(中間欄往上是350之內都會加)
左欄變成1250 + 1250 + 950 = 3450
．第四個1500，減第三個1250在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1500 + 1250 + 1250 = 4000
．第五個1600，減第四個1500在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1600 + 1500 + 1250 + 1250 = 5600(這個是上個範例沒有的，之所以會加1250是因為1600 - 350 = 1250，所以1250以上(含)都會加)
．第六個2850，減第五個1600，並沒有在350之內，所以左欄還是2850

※向下範圍

關鍵字是FOLLOWING

但並不是sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 350 FOLLOWING)

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 350 FOLLOWING) s, e.sal, e.deptno FROM emp e;

※結果：

※以30部門為例，看中間的薪水欄
．第一個950，看第二、三個是1250，在350之內，所以加總(中間欄往下是350之內都會加)
左欄變成950 + 1250 + 1250 = 3450
．第二、三個是1250，看第四個是1500，在350之內，所以加總(中間欄往下是350之內都會加)
左欄變成1250 + 1250 + 1500 + 1600 = 5600
．第四個1500，看第五個是1600，在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1500 + 1600 = 3100
．第五個1600，看第六個是2850，不在350之內，所以左欄還是1600
．第六個2850，往下沒有值，所以左欄還是2850

※向上加向下範圍

也就是向上和向下的合併

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 300 PRECEDING AND 350 FOLLOWING) s, e.sal, e.deptno FROM emp e;

※結果：

※以30部門為例，看中間的薪水欄，此範例是往上300、往下350
．第一個950，往上沒有值，看第二、三個是1250，在350之內
左欄變成 950 + 1250 + 1250 = 3450

．第二、三個是1250，減第一個950在350之內，往上要加
看第四個是1500，在350之內，往下也要加
左欄變成 950 + 1250 + 1250 + 1500 + 1600 = 6550

．第四個1500，減第二、三個在300之內，往上要加
看第五個是1600，在350之內，往下也要加
左欄變成 1250 + 1250 + 1500 + 1600 = 5600

．第五個1600，減第四個在300之內，往上要加
看第六個是2850，不在350之內，往下不加
左欄變成 1500 + 1600 = 3100

．第六個2850，減第五個不在300之內，往上不加
往下沒有值，往下也不加
左欄還是2850


※當前行

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 300 PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;

※結果：

※前二條SQL的執行結果都和上面一樣，只有相同的薪水才會加總

※第三條SQL的執行結果和向上範圍一樣

※無範圍

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal) s, e.sal, e.deptno FROM emp e;

※結果：

※因為向上無範圍，所以和第二條SQL(分區排序)一樣

※如果向下也無範圍，如下：

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno) s, e.sal, e.deptno FROM emp e;

※那結果就和第二條SQL差不多(分區)，只差在沒排序

※以行當範圍

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) s, e.sal, e.deptno FROM emp e;

※結果：

※此範例是當前行和往上2行薪水的加總，這時並沒有薪水一樣會一起加的問題