Oracle分析函式與視窗函式
一、預設視窗與排名函式
最常用的分析函式是排名函式，用於返回當前記錄在視窗中的位置。常用的排名函式包括rank()、dense_rank()、row_number()等。排名函式所對應的視窗函式必須指定排序規則，即必須使用order by 子句，而Oracle為每條記錄提供的預設視窗為從表中第一條記錄開始，直至與當前記錄具有相同排名的所有記錄。
先看下Oracle的示例表scott.emp
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
1、rank()函式
將表中員工按入職時間升序排列，列出排名次序
select empno, ename, hiredate, rank() over(order by hiredate) hiredate_order from emp;
EMPNO ENAME HIREDATE HIREDATE_ORDER
---------- ---------- ------------------- --------------
7369 SMITH 1980-12-17 00:00:00 1
7499 ALLEN 1981-02-20 00:00:00 2
7521 WARD 1981-02-22 00:00:00 3
7566 JONES 1981-04-02 00:00:00 4
7698 BLAKE 1981-05-01 00:00:00 5
7782 CLARK 1981-06-09 00:00:00 6
7844 TURNER 1981-09-08 00:00:00 7
7654 MARTIN 1981-09-28 00:00:00 8
7839 KING 1981-11-17 00:00:00 9
7900 JAMES 1981-12-03 00:00:00 10
7902 FORD 1981-12-03 00:00:00 10
7934 MILLER 1982-01-23 00:00:00 12
7788 SCOTT 1987-04-19 00:00:00 13
7876 ADAMS 1987-05-23 00:00:00 14
該函式的排名具有跳躍性，如7900和7902員工排名都是10，而下一位7934員工排名則為12。
2、dense_rank()函式
類似rank()函式，但排名不具有跳躍性
select empno, ename, hiredate, dense_rank() over(order by hiredate) hiredate_order from emp;
EMPNO ENAME HIREDATE HIREDATE_ORDER
---------- ---------- ------------------- --------------
7369 SMITH 1980-12-17 00:00:00 1
7499 ALLEN 1981-02-20 00:00:00 2
7521 WARD 1981-02-22 00:00:00 3
7566 JONES 1981-04-02 00:00:00 4
7698 BLAKE 1981-05-01 00:00:00 5
7782 CLARK 1981-06-09 00:00:00 6
7844 TURNER 1981-09-08 00:00:00 7
7654 MARTIN 1981-09-28 00:00:00 8
7839 KING 1981-11-17 00:00:00 9
7900 JAMES 1981-12-03 00:00:00 10
7902 FORD 1981-12-03 00:00:00 10
7934 MILLER 1982-01-23 00:00:00 11
7788 SCOTT 1987-04-19 00:00:00 12
7876 ADAMS 1987-05-23 00:00:00 13
7900和7902員工排名都是10，而下一位7934員工排名則為11，排名不存在跳躍。
3、row_number()函式
row_number()函式單純返回當前記錄在視窗中所處位置的行號，該行號具有唯一性。以上查詢用row_number()函式替代後如下
select empno, ename, hiredate, row_number() over(order by hiredate) hiredate_order from emp;
EMPNO ENAME HIREDATE HIREDATE_ORDER
---------- ---------- ------------------- --------------
7369 SMITH 1980-12-17 00:00:00 1
7499 ALLEN 1981-02-20 00:00:00 2
7521 WARD 1981-02-22 00:00:00 3
7566 JONES 1981-04-02 00:00:00 4
7698 BLAKE 1981-05-01 00:00:00 5
7782 CLARK 1981-06-09 00:00:00 6
7844 TURNER 1981-09-08 00:00:00 7
7654 MARTIN 1981-09-28 00:00:00 8
7839 KING 1981-11-17 00:00:00 9
7900 JAMES 1981-12-03 00:00:00 10
7902 FORD 1981-12-03 00:00:00 11
7934 MILLER 1982-01-23 00:00:00 12
7788 SCOTT 1987-04-19 00:00:00 13
7876 ADAMS 1987-05-23 00:00:00 14
4、count()函式
透過count()函式可以檢視視窗中實際的記錄數目，以上查詢用count()函式替代後如下
select empno, ename, hiredate, count(1) over(order by hiredate) hiredate_order from emp;
EMPNO ENAME HIREDATE HIREDATE_ORDER
---------- ---------- ------------------- --------------
7369 SMITH 1980-12-17 00:00:00 1
7499 ALLEN 1981-02-20 00:00:00 2
7521 WARD 1981-02-22 00:00:00 3
7566 JONES 1981-04-02 00:00:00 4
7698 BLAKE 1981-05-01 00:00:00 5
7782 CLARK 1981-06-09 00:00:00 6
7844 TURNER 1981-09-08 00:00:00 7
7654 MARTIN 1981-09-28 00:00:00 8
7839 KING 1981-11-17 00:00:00 9
7900 JAMES 1981-12-03 00:00:00 11
7902 FORD 1981-12-03 00:00:00 11
7934 MILLER 1982-01-23 00:00:00 12
7788 SCOTT 1987-04-19 00:00:00 13
7876 ADAMS 1987-05-23 00:00:00 14
二、分割槽視窗
分割槽視窗是與當前記錄擁有相同的分割槽標準的所有記錄，語法為
partition by 列名
partition by語句首先根據列名獲得當前記錄的列值，接著獲得表中具有相同列值的所有記錄，並將該記錄集合作為當前記錄的視窗。
需要注意的是，當在分割槽視窗中使用order by排序後，視窗返回的不再是全部記錄，而是隻到當前記錄，從而成為預設大小的視窗。
示例：
查詢EMP表每個員工的職位和薪水，並給出該職位員工的平均薪水
select empno, ename, job, sal, round(avg(sal) over(partition by job)) avg_sal from emp;
EMPNO ENAME JOB SAL AVG_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3000
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1038
7900 JAMES CLERK 950 1038
7369 SMITH CLERK 800 1038
7876 ADAMS CLERK 1100 1038
7698 BLAKE MANAGER 2850 2758
7566 JONES MANAGER 2975 2758
7782 CLARK MANAGER 2450 2758
7839 KING PRESIDENT 5000 5000
7844 TURNER SALESMAN 1500 1400
7654 MARTIN SALESMAN 1250 1400
7521 WARD SALESMAN 1250 1400
7499 ALLEN SALESMAN 1600 1400
示例：
在查詢每個員工職位和入職時間的同時，獲得其在該職位中按入職時間進行的排序位置
select empno, ename, job, hiredate, dense_rank() over(partition by job order by hiredate) hiredata_order from emp;
EMPNO ENAME JOB HIREDATE HIREDATA_ORDER
---------- ---------- --------- ------------------- --------------
7902 FORD ANALYST 1981-12-03 00:00:00 1
7788 SCOTT ANALYST 1987-04-19 00:00:00 2
7369 SMITH CLERK 1980-12-17 00:00:00 1
7900 JAMES CLERK 1981-12-03 00:00:00 2
7934 MILLER CLERK 1982-01-23 00:00:00 3
7876 ADAMS CLERK 1987-05-23 00:00:00 4
7566 JONES MANAGER 1981-04-02 00:00:00 1
7698 BLAKE MANAGER 1981-05-01 00:00:00 2
7782 CLARK MANAGER 1981-06-09 00:00:00 3
7839 KING PRESIDENT 1981-11-17 00:00:00 1
7499 ALLEN SALESMAN 1981-02-20 00:00:00 1
7521 WARD SALESMAN 1981-02-22 00:00:00 2
7844 TURNER SALESMAN 1981-09-08 00:00:00 3
7654 MARTIN SALESMAN 1981-09-28 00:00:00 4
三、視窗子句
視窗子句可以進一步限制或擴充套件視窗的範圍。
1、rows子句
rows子句的使用前提為視窗已經利用order by 進行了排序。此時可以利用rows子句按照位置向前或向後追溯，從而進一步調整視窗大小。語法如下
over(order by 列名 rows between 位移量 preceding and 位移量 following)
preceding向前追溯處於當前記錄之前的記錄，following向後追溯處於當前記錄之後的記錄。
示例：
在查詢每個員工入職時間和薪水的同時，計算與其前後相鄰入職時間的共三位員工的平均薪水
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between 1 preceding and 1 following)) avg_sal from emp;
EMPNO ENAME JOB HIREDATE SAL AVG_SAL
---------- ---------- --------- ------------------- ---------- ----------
7369 SMITH CLERK 1980-12-17 00:00:00 800 1200
7499 ALLEN SALESMAN 1981-02-20 00:00:00 1600 1217
7521 WARD SALESMAN 1981-02-22 00:00:00 1250 1942
7566 JONES MANAGER 1981-04-02 00:00:00 2975 2358
7698 BLAKE MANAGER 1981-05-01 00:00:00 2850 2758
7782 CLARK MANAGER 1981-06-09 00:00:00 2450 2267
7844 TURNER SALESMAN 1981-09-08 00:00:00 1500 1733
7654 MARTIN SALESMAN 1981-09-28 00:00:00 1250 2583
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 2400
7900 JAMES CLERK 1981-12-03 00:00:00 950 2983
7902 FORD ANALYST 1981-12-03 00:00:00 3000 1750
7934 MILLER CLERK 1982-01-23 00:00:00 1300 2433
7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 1800
7876 ADAMS CLERK 1987-05-23 00:00:00 1100 2050
對於第一條記錄和最後一條記錄來說，實際上對應的視窗只有兩條記錄。
2、range子句
rows子句以相對位置作為獲取記錄的標準，而range子句則以相對列值作為篩選記錄的標準。語法如下
over(order by 列名 range between 差值 preceding and 差值 following)
示例：
在查詢每個員工入職時間的同時，計算包括與其入職時間前後相差一年之內的員工數量
select empno, ename, job, hiredate, count(1) over(order by hiredate range between 365 preceding and 365 following) count_emp from emp;
EMPNO ENAME JOB HIREDATE COUNT_EMP
---------- ---------- --------- ------------------- ----------
7369 SMITH CLERK 1980-12-17 00:00:00 11
7499 ALLEN SALESMAN 1981-02-20 00:00:00 12
7521 WARD SALESMAN 1981-02-22 00:00:00 12
7566 JONES MANAGER 1981-04-02 00:00:00 12
7698 BLAKE MANAGER 1981-05-01 00:00:00 12
7782 CLARK MANAGER 1981-06-09 00:00:00 12
7844 TURNER SALESMAN 1981-09-08 00:00:00 12
7654 MARTIN SALESMAN 1981-09-28 00:00:00 12
7839 KING PRESIDENT 1981-11-17 00:00:00 12
7900 JAMES CLERK 1981-12-03 00:00:00 12
7902 FORD ANALYST 1981-12-03 00:00:00 12
7934 MILLER CLERK 1982-01-23 00:00:00 11
7788 SCOTT ANALYST 1987-04-19 00:00:00 2
7876 ADAMS CLERK 1987-05-23 00:00:00 2
3、current row與unbounded
在視窗子句中，除了可以用確定的數值來限定視窗外，還可以使用current row來指定當前記錄，使用unbounded來代替數值，表示不受限制的視窗範圍。
向前位移無限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between unbounded preceding and current row)) avg_sal from emp;
向後位移無限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between 1 preceding and unbounded following)) avg_sal from emp;
前後位移均無限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between unbounded preceding and unbounded following)) avg_sal from emp;
四、常用分析函式
很多聚合函式均可以和視窗函式結合使用，如max()、min()、sum()等。除此之外，Oracle還提供了另外幾種常用的分析函式。
1、first_value()函式
first_value()函式返回已排序視窗中第一條記錄的相關資訊。
示例：
在查詢員工入職時間和所在部門的同時，指出該部門入職時間最早的員工
select empno, ename, hiredate, deptno, first_value(empno) over(partition by deptno order by hiredate) first_emp from emp;
EMPNO ENAME HIREDATE DEPTNO FIRST_EMP
---------- ---------- ------------------- ---------- ----------
7782 CLARK 1981-06-09 00:00:00 10 7782
7839 KING 1981-11-17 00:00:00 10 7782
7934 MILLER 1982-01-23 00:00:00 10 7782
7369 SMITH 1980-12-17 00:00:00 20 7369
7566 JONES 1981-04-02 00:00:00 20 7369
7902 FORD 1981-12-03 00:00:00 20 7369
7788 SCOTT 1987-04-19 00:00:00 20 7369
7876 ADAMS 1987-05-23 00:00:00 20 7369
7499 ALLEN 1981-02-20 00:00:00 30 7499
7521 WARD 1981-02-22 00:00:00 30 7499
7698 BLAKE 1981-05-01 00:00:00 30 7499
7844 TURNER 1981-09-08 00:00:00 30 7499
7654 MARTIN 1981-09-28 00:00:00 30 7499
7900 JAMES 1981-12-03 00:00:00 30 7499
2、last_value()函式
與first_value()函式相反，last_value()函式返回分割槽中最後一條記錄的相關資訊。
示例：
在查詢員工入職時間和所在部門的同時，指出該部門入職時間最遲的員工。首先說明，套用fist_value()的查詢寫法是不行的，如下
select empno, ename, hiredate, deptno, last_value(empno) over(partition by deptno order by hiredate) last_emp from emp;
EMPNO ENAME HIREDATE DEPTNO LAST_EMP
---------- ---------- ------------------- ---------- ----------
7782 CLARK 1981-06-09 00:00:00 10 7782
7839 KING 1981-11-17 00:00:00 10 7839
7934 MILLER 1982-01-23 00:00:00 10 7934
7369 SMITH 1980-12-17 00:00:00 20 7369
7566 JONES 1981-04-02 00:00:00 20 7566
7902 FORD 1981-12-03 00:00:00 20 7902
7788 SCOTT 1987-04-19 00:00:00 20 7788
7876 ADAMS 1987-05-23 00:00:00 20 7876
7499 ALLEN 1981-02-20 00:00:00 30 7499
7521 WARD 1981-02-22 00:00:00 30 7521
7698 BLAKE 1981-05-01 00:00:00 30 7698
7844 TURNER 1981-09-08 00:00:00 30 7844
7654 MARTIN 1981-09-28 00:00:00 30 7654
7900 JAMES 1981-12-03 00:00:00 30 7900
沒有達到希望的查詢結果，原因正如前面所說，當在分割槽視窗中使用order by排序後，視窗返回的不再是全部記錄，而是隻到當前記錄，從而成為預設大小的視窗。正確的做法是可以利用rows子句來指定無限制的視窗，如下
select empno, ename, hiredate, deptno, last_value(empno) over(partition by deptno order by hiredate rows between unbounded preceding and unbounded following) last_emp from emp;
EMPNO ENAME HIREDATE DEPTNO LAST_EMP
---------- ---------- ------------------- ---------- ----------
7782 CLARK 1981-06-09 00:00:00 10 7934
7839 KING 1981-11-17 00:00:00 10 7934
7934 MILLER 1982-01-23 00:00:00 10 7934
7369 SMITH 1980-12-17 00:00:00 20 7876
7566 JONES 1981-04-02 00:00:00 20 7876
7902 FORD 1981-12-03 00:00:00 20 7876
7788 SCOTT 1987-04-19 00:00:00 20 7876
7876 ADAMS 1987-05-23 00:00:00 20 7876
7499 ALLEN 1981-02-20 00:00:00 30 7900
7521 WARD 1981-02-22 00:00:00 30 7900
7698 BLAKE 1981-05-01 00:00:00 30 7900
7844 TURNER 1981-09-08 00:00:00 30 7900
7654 MARTIN 1981-09-28 00:00:00 30 7900
7900 JAMES 1981-12-03 00:00:00 30 7900
由此可見，last_value()函式往往需要結合使用rows子句使用，因此，使用first_value()函式要優於使用last_value()函式。其實為了獲得與last_value()相同的效果，使用first_value()函式時，只要為order by子句新增desc進行降序排列即可。
3、lag()函式
first_value()和last_value()可以返回視窗中第一條和最後一條記錄，但不能處理其他記錄。為此，Oracle提供了lag()函式和lead()函式來靈活處理其他記錄。
lag()函式以當前記錄為座標，按照指定的位移量向上搜尋，並嘗試捕獲記錄，捕獲不到時返回指定的預設值，語法如下
lag(列名或列表示式, 位移量, 預設值)
這裡要求預設值的資料型別應當與列名或列表示式的型別相容。
示例：
查詢每個員工的工資資訊，並給出工資排名在他前一位的員工
select empno, ename, sal, lag(empno, 1, null) over(order by sal) lag_empno, lag(ename, 1, 'N/A') over(order by sal) lag_ename from emp;
EMPNO ENAME SAL LAG_EMPNO LAG_ENAME
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 N/A
7900 JAMES 950 7369 SMITH
7876 ADAMS 1100 7900 JAMES
7521 WARD 1250 7876 ADAMS
7654 MARTIN 1250 7521 WARD
7934 MILLER 1300 7654 MARTIN
7844 TURNER 1500 7934 MILLER
7499 ALLEN 1600 7844 TURNER
7782 CLARK 2450 7499 ALLEN
7698 BLAKE 2850 7782 CLARK
7566 JONES 2975 7698 BLAKE
7788 SCOTT 3000 7566 JONES
7902 FORD 3000 7788 SCOTT
7839 KING 5000 7902 FORD
4、lead()函式
該函式與lag()函式類似，不同之處是lead()函式的位移量為向下偏移。
示例：
查詢每個員工的工資資訊，並給出工資排名在他後一位的員工，這裡直接用lead()替換lag()即可
select empno, ename, sal, lead(empno, 1, null) over(order by sal) lag_empno, lead(ename, 1, 'N/A') over(order by sal) lag_ename from emp;
EMPNO ENAME SAL LAG_EMPNO LAG_ENAME
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 7900 JAMES
7900 JAMES 950 7876 ADAMS
7876 ADAMS 1100 7521 WARD
7521 WARD 1250 7654 MARTIN
7654 MARTIN 1250 7934 MILLER
7934 MILLER 1300 7844 TURNER
7844 TURNER 1500 7499 ALLEN
7499 ALLEN 1600 7782 CLARK
7782 CLARK 2450 7698 BLAKE
7698 BLAKE 2850 7566 JONES
7566 JONES 2975 7788 SCOTT
7788 SCOTT 3000 7902 FORD
7902 FORD 3000 7839 KING
7839 KING 5000 N/A
這裡發現，當使用lead()函式做位移推進時，over(order by)這樣原本只到當前記錄的預設大小的視窗實際範圍變大了，從而能夠獲取到往下的記錄。
5、ratio_to_report()佔比函式
查詢每個員工的工資及其在所有員工和所在部門員工中的百分比
select empno, ename, sal, round((ratio_to_report(sal) over()) * 100, 2) pct1, round((ratio_to_report(sal) over(partition by deptno)) * 100, 2) pct2 from scott.emp;
EMPNO ENAME SAL PCT1 PCT2
---------- ---------- ---------- ---------- ----------
7782 CLARK 2450 8.44 28
7839 KING 5000 17.23 57.14
7934 MILLER 1300 4.48 14.86
7566 JONES 2975 10.25 27.36
7902 FORD 3000 10.34 27.59
7876 ADAMS 1100 3.79 10.11
7369 SMITH 800 2.76 7.36
7788 SCOTT 3000 10.34 27.59
7521 WARD 1250 4.31 13.3
7844 TURNER 1500 5.17 15.96
7499 ALLEN 1600 5.51 17.02
7900 JAMES 950 3.27 10.11
7698 BLAKE 2850 9.82 30.32
7654 MARTIN 1250 4.31 13.3
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28974745/viewspace-2155267/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分析函式之開窗函式over()詳解Oracle函式
- 與SQL視窗函式相同SQL函式
- hive視窗分析函式使用詳解系列二之分組排序視窗函式Hive函式排序
- Mysql視窗函式MySql函式
- Hive視窗函式Hive函式
- SQL 視窗函式SQL函式
- PostgreSQL：視窗函式SQL函式
- SQL視窗分析函式使用詳解系列三之偏移量類視窗函式SQL函式
- hive視窗函式使用Hive函式
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- PostgreSQL>視窗函式的用法SQL函式
- MySQL視窗函式彙總MySql函式
- hive05_視窗函式Hive函式
- hive視窗分析函式使用詳解系列一Hive函式
- mysql視窗函式中的滑動視窗MySql函式
- MySQL視窗函式用法總結MySql函式
- MySQL 8.0 視窗函式-筆記MySql函式筆記
- Hive視窗函式保姆級教程Hive函式
- 常用分析函式開窗講解函式
- ORACLE分析函式手冊(轉)Oracle函式
- oracle 函式Oracle函式
- oracle or 函式Oracle函式
- 【SQL】Lag/Rank/Over視窗函式揭秘，資料分析之旅SQL函式
- PostgreSQL 視窗函式 ( Window Functions ) 如何使用？SQL函式Function
- 詳解SQL操作的視窗函式SQL函式
- 通俗易懂：視窗函式 | 全是案例函式
- Oracle OCP(03)：字元函式、數字函式和日期函式Oracle字元函式
- SparkSQL開窗函式SparkSQL函式
- SparkSQL 開窗函式SparkSQL函式
- Spark 開窗函式Spark函式
- ORACLE分析函式手冊二(轉)Oracle函式
- Oracle常用函式Oracle函式
- 7 Oracle 函式Oracle函式
- Oracle 字串函式Oracle字串函式
- Oracle 字串函式Oracle字串函式
- MySQL視窗函式的具體使用TOCSMySql函式
- SQL輕鬆入門（5）：視窗函式SQL函式
- wm_concat函式與oracle版本函式Oracle