Oracle分析函數
01.Oracle分析函數
--運行總計:比如逐行顯示在某些部門中的累計匯總工資，每行包含前面各行工資之和，
--查找一組內的百分數：比如顯示在某些部門中付給個人的總工資百分數，將他們的工資與該部門的工資總和相除
--前n個查詢:查詢指定條件的前N個記錄
--移動平均值計算：將當前行的值與前N行的值 加一起求平均值
--執行等級查詢:比如顯示一個部門內某個員工工資的相關等級
select ename,
deptno,
sal,
sum(sal) over(order by deptno, ename) 運行總計,
sum(sal) over(partition by deptno order by ename) 分部門運行統計,
row_number() over(partition by deptno order by ename) 序列
from emp
where deptno in (10, 20)
order by deptno, ename;
SQL> select ename,
2 deptno,
3 sal,
4 sum(sal) over(order by deptno, ename) 運行總計,
5 sum(sal) over(partition by deptno order by ename) 分部門運行統計,
6 row_number() over(partition by deptno order by ename) 序列
7 from emp
8 where deptno in (10, 20)
9 order by deptno, ename;
ENAME DEPTNO SAL 運行總計 分部門運行統計 序列
---------- ---------- ---------- ---------- -------------- ----------
CLARK 10 2450 2450 2450 1
KING 10 5000 7450 7450 2
MILLER 10 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 20 3000 12850 4100 2
JONES 20 2975 15825 7075 3
SCOTT 20 3000 18825 10075 4
SMITH 20 800 19625 10875 5
已選擇8行。
02.Oracle分析函數_不指定排序規則
指定排序規則
SQL> select ename, deptno, sal, sum(sal) over(order by ename) 運行總計
2 from emp
3 where deptno in (10, 20);
ENAME DEPTNO SAL 運行總計
---------- ---------- ---------- ----------
ADAMS 20 1100 1100
CLARK 10 2450 3550
FORD 20 3000 6550
JONES 20 2975 9525
KING 10 5000 14525
MILLER 10 1300 15825
SCOTT 20 3000 18825
SMITH 20 800 19625
已選擇8行。
--注意 sum要進行運行總計計算，如果不指定排序規則，在運行總計欄只能看到相同的結果,如下
SQL> select ename, deptno, sal, sum(sal) over() 運行總計
2 from emp
3 where deptno in (10, 20);
ENAME DEPTNO SAL 運行總計
---------- ---------- ---------- ----------
SMITH 20 800 19625
JONES 20 2975 19625
CLARK 10 2450 19625
SCOTT 20 3000 19625
KING 10 5000 19625
ADAMS 20 1100 19625
FORD 20 3000 19625
MILLER 10 1300 19625
已選擇8行。
03.統計部門中各部門中不同職位的薪資總計，同時顯示不同職位的薪資的運行總計
SQL> select o.deptno,
2 o.job,
3 sum(o.sal) 部門職稱匯總,
4 sum(sum(o.sal)) over(partition by o.deptno order by o.job) 部門薪資
運行匯總
5 from emp o
6 where deptno in (10, 20, 30)
7 and job is not null
8 group by o.deptno, o.job
9 order by o.deptno;
DEPTNO JOB 部門職稱匯總 部門薪資運行匯總
---------- --------- ------------ ----------------
10 CLERK 1300 1300
10 MANAGER 2450 3750
10 PRESIDENT 5000 8750
20 ANALYST 6000 6000
20 CLERK 1900 7900
20 MANAGER 2975 10875
30 CLERK 950 950
30 MANAGER 2850 3800
30 SALESMAN 5600 9400
已選擇9行。
04_等級函數.
SQL> select o.deptno,
2 --o.job,
3 sum(o.sal) 部門職稱匯總,
4 rank() over(order by sum(o.sal) desc) 薪資等級,
5 dense_rank() over(order by sum(o.sal) desc) dense_rank排名,
6 row_number() over(partition by o.deptno order by sum(o.sal) desc) 分
組行號,
7 sum(sum(o.sal)) over(partition by o.deptno order by o.job) 部門薪資
運行總計
8 from emp o
9 where o.deptno in (10, 20, 30)
10 and job is not null
11 group by o.deptno, o.job;
DEPTNO 部門職稱匯總 薪資等級 DENSE_RANK排名 分組行號 部門薪資運行總計
---------- ------------ ---------- -------------- ---------- ----------------
20 6000 1 1 1 6000
30 5600 2 2 1 9400
10 5000 3 3 1 8750
20 2975 4 4 2 10875
30 2850 5 5 2 3800
10 2450 6 6 2 3750
20 1900 7 7 3 7900
10 1300 8 8 3 1300
30 950 9 9 3 950
已選擇9行。
SQL>
05.分區子句
--使用row_number進行行號統計時使用分區
--注意 row_number 函數總是從1開始計數
SQL> select deptno,
2 empno,
3 ename,
4 row_number() over(partition by deptno order by empno) 分組
5 from emp
6 where deptno in (10, 20, 30);
DEPTNO EMPNO ENAME 分組行號
---------- ---------- ---------- ----------
10 7782 CLARK 1
10 7839 KING 2
10 7934 MILLER 3
20 7369 SMITH 1
20 7566 JONES 2
20 7788 SCOTT 3
20 7876 ADAMS 4
20 7902 FORD 5
30 7499 ALLEN 1
30 7521 WARD 2
30 7654 MARTIN 3
DEPTNO EMPNO ENAME 分組行號
---------- ---------- ---------- ----------
30 7698 BLAKE 4
30 7844 TURNER 5
30 7900 JAMES 6
已選擇14行。
06.排序子句
--order by 子句用於指定分組中數據的排序方式，排序方式會明顯地影響任何分析函數的結果，舉例來說，在進行AVG
--運算時，如果不指定排序，將會看到所有的結果都相同
--排序子句SQL> select ename, sal, avg(sal) over() from emp where rownum <= 3;
ENAME SAL AVG(SAL)OVER()
---------- ---------- --------------
SMITH 800 1216.66667
ALLEN 1600 1216.66667
WARD 1250 1216.66667
--如果指定按ename排序，其結果又大不一樣
SQL> select ename, sal, avg(sal) over( order by ename) from emp where rownum <=
3;
ENAME SAL AVG(SAL)OVER(ORDERBYENAME)
---------- ---------- --------------------------
ALLEN 1600 1600
SMITH 800 1200
WARD 1250 1216.66667
07.開窗子句
--開窗子句
--從當前記錄開始直至某個部分的最後一條記錄結束記錄
--從統計時可以統計分組以外的記錄
--在當前行的前幾行或後幾行進行滾動計算
SQL> select deptno,
2 sum(sal) 部門薪資小計,
3 sum(sum(sal)) over(order by deptno rows between unbounded preceding
and unbounded following) 部門總計
4 from emp
5 group by deptno;
DEPTNO 部門薪資小計 部門總計
---------- ------------ ----------
10 8750 29025
20 10875 29025
30 9400 29025
08.分析函數列表 count函數
SQL> select empno,
2 ename,
3 count(*) over(partition by deptno order by empno) 條數統計
4 from emp;
EMPNO ENAME 條數統計
---------- ---------- ----------
7782 CLARK 1
7839 KING 2
7934 MILLER 3
7369 SMITH 1
7566 JONES 2
7788 SCOTT 3
7876 ADAMS 4
7902 FORD 5
7499 ALLEN 1
7521 WARD 2
7654 MARTIN 3
EMPNO ENAME 條數統計
---------- ---------- ----------
7698 BLAKE 4
7844 TURNER 5
7900 JAMES 6
已選擇14行。
--通過range between來判斷sal值是否在50-150之間
SQL> select empno,
2 ename,
3 sal,
4 count(*) over(order by sal range between 50 preceding and 150 follow
ing) 薪水差異個數
5 from emp;
EMPNO ENAME SAL 薪水差異個數
---------- ---------- ---------- ------------
7369 SMITH 800 2
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 3
7654 MARTIN 1250 3
7934 MILLER 1300 3
7844 TURNER 1500 2
7499 ALLEN 1600 1
7782 CLARK 2450 1
7698 BLAKE 2850 4
7566 JONES 2975 3
EMPNO ENAME SAL 薪水差異個數
---------- ---------- ---------- ------------
7788 SCOTT 3000 3
7902 FORD 3000 3
7839 KING 5000 1
已選擇14行。
--09.分析函數列表 AVG
SQL> select deptno,
2 empno,
3 ename,
4 sal,
5 avg(sal) over(partition by deptno order by deptno) avg_sal
6 from emp;
DEPTNO EMPNO ENAME SAL AVG_SAL
---------- ---------- ---------- ---------- ----------
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
10 7934 MILLER 1300 2916.66667
20 7566 JONES 2975 2175
20 7902 FORD 3000 2175
20 7876 ADAMS 1100 2175
20 7369 SMITH 800 2175
20 7788 SCOTT 3000 2175
30 7521 WARD 1250 1566.66667
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
DEPTNO EMPNO ENAME SAL AVG_SAL
---------- ---------- ---------- ---------- ----------
30 7900 JAMES 950 1566.66667
30 7698 BLAKE 2850 1566.66667
30 7654 MARTIN 1250 1566.66667
已選擇14行。
10_分析函數列表min_max函數
SQL> select deptno,
2 empno,
3 ename,
4 hiredate,
5 sal,
6 min(sal) over(partition by deptno order by hiredate range unbounded
preceding) 最低薪水,
7 max(sal) over(partition by deptno order by hiredate range unbounded
preceding) 最高薪水
8 from emp;
DEPTNO EMPNO ENAME HIREDATE SAL 最低薪水 最高薪水
---------- ---------- ---------- -------------- ---------- ---------- ----------
10 7782 CLARK 09-6月 -81 2450 2450 2450
10 7839 KING 17-11月-81 5000 2450 5000
10 7934 MILLER 23-1月 -82 1300 1300 5000
20 7369 SMITH 17-12月-80 800 800 800
20 7566 JONES 02-4月 -81 2975 800 2975
20 7902 FORD 03-12月-81 3000 800 3000
20 7788 SCOTT 19-4月 -87 3000 800 3000
20 7876 ADAMS 23-5月 -87 1100 800 3000
30 7499 ALLEN 20-2月 -81 1600 1600 1600
30 7521 WARD 22-2月 -81 1250 1250 1600
30 7698 BLAKE 01-5月 -81 2850 1250 2850
DEPTNO EMPNO ENAME HIREDATE SAL 最低薪水 最高薪水
---------- ---------- ---------- -------------- ---------- ---------- ----------
30 7844 TURNER 08-9月 -81 1500 1250 2850
30 7654 MARTIN 28-9月 -81 1250 1250 2850
30 7900 JAMES 03-12月-81 950 950 2850
已選擇14行。
11.--分析函數列表rank,dense_rank和row_number函數
SQL> select deptno,
2 ename,
3 sal,
4 --mgr,
5 rank() over(order by deptno) rank結果， dense_rank() over(order by d
eptno) dense_rank結果,
6 row_number() over(order by deptno) row_number結果
7 from emp
8 where deptno in (10, 20, 30);
DEPTNO ENAME SAL RANK結果 DENSE_RANK結果 ROW_NUMBER結果
---------- ---------- ---------- ---------- -------------- --------------
10 CLARK 2450 1 1 1
10 KING 5000 1 1 2
10 MILLER 1300 1 1 3
20 JONES 2975 4 2 4
20 FORD 3000 4 2 5
20 ADAMS 1100 4 2 6
20 SMITH 800 4 2 7
20 SCOTT 3000 4 2 8
30 WARD 1250 9 3 9
30 TURNER 1500 9 3 10
30 ALLEN 1600 9 3 11
DEPTNO ENAME SAL RANK結果 DENSE_RANK結果 ROW_NUMBER結果
---------- ---------- ---------- ---------- -------------- --------------
30 JAMES 950 9 3 12
30 BLAKE 2850 9 3 13
30 MARTIN 1250 9 3 14
已選擇14行。
12_分析函數列表first和last函數
SQL> select deptno,
2 min(sal) keep(dense_rank first order by comm) 最低提成薪水,
3 max(sal) keep(dense_rank last order by comm) 最高提成薪水
4 from emp
5 group by deptno;
DEPTNO 最低提成薪水 最高提成薪水
---------- ------------ ------------
10 1300 5000
20 800 3000
30 1500 2850
--13.first_value和last_value函數
SQL> select deptno,
2 empno,
3 sal,
4 first_value(sal) over(partition by deptno order by empno) "第一個值
,
5 last_value(sal) over(partition by deptno order by empno) "最後一個值
"
6 from emp;
DEPTNO EMPNO SAL 第一個值 最後一個值
---------- ---------- ---------- ---------- ----------
10 7782 2450 2450 2450
10 7839 5000 2450 5000
10 7934 1300 2450 1300
20 7369 800 800 800
20 7566 2975 800 2975
20 7788 3000 800 3000
20 7876 1100 800 1100
20 7902 3000 800 3000
30 7499 1600 1600 1600
30 7521 1250 1600 1250
30 7654 1250 1600 1250
DEPTNO EMPNO SAL 第一個值 最後一個值
---------- ---------- ---------- ---------- ----------
30 7698 2850 1600 2850
30 7844 1500 1600 1500
30 7900 950 1600 950
已選擇14行。
14.--lag和lead函數
--lag的功能是返回指定列col前n1行的值(如果前n1行已經超出比照范圍，則返回n2,如果不指定n2則默認返回null),
--如果不指定n1，其默認值為1,lead函數與此相反，返回指定列col1後面的n1行的
--使用lag和lead函數查找當前雇員的前一個雇員的薪水後和後一個雇員薪水
SQL> select ename,
2 hiredate,
3 sal,
4 deptno,
5 lag(sal, 1, 0) over(order by hiredate) as "前一個雇員薪水",
6 lead(sal, 1, 0) over(order by hiredate) as "後一個雇員薪水"
7 from emp
8 where deptno = 30;
ENAME HIREDATE SAL DEPTNO 前一個雇員薪水 後一個雇員薪水
---------- -------------- ---------- ---------- -------------- --------------
ALLEN 20-2月 -81 1600 30 0 1250
WARD 22-2月 -81 1250 30 1600 2850
BLAKE 01-5月 -81 2850 30 1250 1500
TURNER 08-9月 -81 1500 30 2850 1250
MARTIN 28-9月 -81 1250 30 1500 950
JAMES 03-12月-81 950 30 1250 0
已選擇6行。
--15.分析函數使用示例 記錄排名
SQL> select deptno,
2 -- empno,
3 ename,
4 sum(sal) dept_sales,
5 rank() over(partition by deptno order by sum(sal) desc nulls last)
薪資排名_跳號,
6 dense_rank() over(partition by deptno order by sum(sal) desc nulls l
ast) 薪資排名_同級同號,
7 row_number() over(partition by deptno order by sum(sal) desc nulls l
ast) 薪資排名_不跳號
8 from emp
9 group by deptno, empno, ename;
DEPTNO ENAME DEPT_SALES 薪資排名_跳號 薪資排名_同級同號 薪資排名_不跳號
---------- ---------- ---------- ------------- ----------------- ---------------
10 KING 5000 1 1 1
10 CLARK 2450 2 2 2
10 MILLER 1300 3 3 3
20 FORD 3000 1 1 1
20 SCOTT 3000 1 1 2
20 JONES 2975 3 2 3
20 ADAMS 1100 4 3 4
20 SMITH 800 5 4 5
30 BLAKE 2850 1 1 1
30 ALLEN 1600 2 2 2
30 TURNER 1500 3 3 3
DEPTNO ENAME DEPT_SALES 薪資排名_跳號 薪資排名_同級同號 薪資排名_不跳號
---------- ---------- ---------- ------------- ----------------- ---------------
30 MARTIN 1250 4 4 4
30 WARD 1250 4 4 5
30 JAMES 950 6 5 6
已選擇14行。
16.--分析函數使用 首尾記錄查詢
SQL> select min(empno) keep(dense_rank first order by sum(sal) desc nulls last)
薪資排名首位， max(empno) keep(dense_rank last order by sum(sal) desc nulls last
) 薪資排名尾位
2 from emp
3 where sal is not null
4 and deptno is not null
5 group by empno;
薪資排名首位 薪資排名尾位
------------ ------------
7839 7369
--17.分析函數使用 前後排名查詢
SQL> select min(empno) keep(dense_rank first order by sum(sal) desc nulls last)
薪資排名首位， max(empno) keep(dense_rank last order by sum(sal) desc nulls last
) 薪資排名尾位
2 from emp
3 where sal is not null
4 and deptno is not null
5 group by empno;
薪資排名首位 薪資排名尾位
------------ ------------
7839 7369
--18.分析函數使用示例 層次查詢
SQL> select *
2 from (select deptno,
3 empno,
4 ename,
5 sum(sal) dept_sales,
6 ntile(3) over(partition by deptno order by sum(sal) nulls la
st) rank_ration
7 from emp
8 where deptno is not null
9 group by deptno, empno, ename)
10 where rank_ration = 1;
DEPTNO EMPNO ENAME DEPT_SALES RANK_RATION
---------- ---------- ---------- ---------- -----------
10 7934 MILLER 1300 1
20 7369 SMITH 800 1
20 7876 ADAMS 1100 1
30 7900 JAMES 950 1
30 7654 MARTIN 1250 1
19.--范圍統計查詢
--范圍查詢是分析函數發揮其功能的重點，范圍查詢是指查詢當前記錄的前面或後面的記錄進行統計，比如想知道
--員工史密斯的雇傭日期前10天和後10天新進員工的最高薪資，可以使用分析函數來實現這個功能
SQL> select empno,
2 --ename,
3 hiredate,
4 sal,
5 max(sal) over(order by hiredate rows between 10 preceding and curren
t row) "前10天入職最高薪資",
6 max(sal) over(order by hiredate rows between current row and 10 foll
owing) "後10天入職最高薪資"
7 from emp
8 where sal is not null;
EMPNO HIREDATE SAL 前10天入職最高薪資 後10天入職最高薪資
---------- -------------- ---------- ------------------ ------------------
7369 17-12月-80 800 800 5000
7499 20-2月 -81 1600 1600 5000
7521 22-2月 -81 1250 1600 5000
7566 02-4月 -81 2975 2975 5000
7698 01-5月 -81 2850 2975 5000
7782 09-6月 -81 2450 2975 5000
7844 08-9月 -81 1500 2975 5000
7654 28-9月 -81 1250 2975 5000
7839 17-11月-81 5000 5000 5000
7900 03-12月-81 950 5000 3000
7902 03-12月-81 3000 5000 3000
EMPNO HIREDATE SAL 前10天入職最高薪資 後10天入職最高薪資
---------- -------------- ---------- ------------------ ------------------
7934 23-1月 -82 1300 5000 3000
7788 19-4月 -87 3000 5000 3000
7876 23-5月 -87 1100 5000 1100
已選擇14行。
--20.相鄰記錄比較
SQL> select ename,
2 hiredate,
3 deptno,
4 sal,
5 sal - prev_sal "與前面的差異"，sal - next_sal "與後面的差異"
6 from (select ename,
7 hiredate,
8 sal,
9 deptno,
10 lag(sal, 1, 0) over(order by hiredate) as prev_sal,
11 lead(sal, 1, 0) over(order by hiredate) as next_sal
12 from emp
13 where deptno is not null
14 and sal is not null);
ENAME HIREDATE DEPTNO SAL 與前面的差異 與後面的差異
---------- -------------- ---------- ---------- ------------ ------------
SMITH 17-12月-80 20 800 800 -800
ALLEN 20-2月 -81 30 1600 800 350
WARD 22-2月 -81 30 1250 -350 -1725
JONES 02-4月 -81 20 2975 1725 125
BLAKE 01-5月 -81 30 2850 -125 400
CLARK 09-6月 -81 10 2450 -400 950
TURNER 08-9月 -81 30 1500 -950 250
MARTIN 28-9月 -81 30 1250 -250 -3750
KING 17-11月-81 10 5000 3750 4050
JAMES 03-12月-81 30 950 -4050 -2050
FORD 03-12月-81 20 3000 2050 1700
ENAME HIREDATE DEPTNO SAL 與前面的差異 與後面的差異
---------- -------------- ---------- ---------- ------------ ------------
MILLER 23-1月 -82 10 1300 -1700 -1700
SCOTT 19-4月 -87 20 3000 1700 1900
ADAMS 23-5月 -87 20 1100 -1900 1100
已選擇14行。
21.--抑制重復
SQL> select *
2 from (select empno,
3 ename,
4 sal,
5 hiredate,
6 row_number() over(partition by extract(year from hiredate)
rder by empno) rn
7 from emp
8 where hiredate is not null
9 and extract(year from hiredate) in (1981, 19872, 1983))
10 where rn = 1;
EMPNO ENAME SAL HIREDATE RN
---------- ---------- ---------- -------------- ----------
7499 ALLEN 1600 20-2月 -81 1
22.--行列轉換
SQL> select job,
2 empno,
3 ename,
4 row_number() over(partition by job order by ename) rb
5 from emp
6 where job is not null;
JOB EMPNO ENAME RB
--------- ---------- ---------- ----------
ANALYST 7902 FORD 1
ANALYST 7788 SCOTT 2
CLERK 7876 ADAMS 1
CLERK 7900 JAMES 2
CLERK 7934 MILLER 3
CLERK 7369 SMITH 4
MANAGER 7698 BLAKE 1
MANAGER 7782 CLARK 2
MANAGER 7566 JONES 3
PRESIDENT 7839 KING 1
SALESMAN 7499 ALLEN 1
JOB EMPNO ENAME RB
--------- ---------- ---------- ----------
SALESMAN 7654 MARTIN 2
SALESMAN 7844 TURNER 3
SALESMAN 7521 WARD 4
已選擇14行。
