Oracle 分析函式
1 取出emp表彙總每個員工的部門號，姓名，員工工資已經部門的平均工資
方法一（分析函式）：
SQL> select deptno,ename,sal,avg(sal) over (partition by deptno) avg_sal from emp;
DEPTNO ENAME SAL AVG_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 2916.66667
10 KING 5000 2916.66667
10 MILLER 1300 2916.66667
20 JONES 2975 2175
20 FORD 3000 2175
20 ADAMS 1100 2175
20 SMITH 800 2175
20 SCOTT 3000 2175
30 WARD 1250 1566.66667
30 TURNER 1500 1566.66667
30 ALLEN 1600 1566.66667
30 JAMES 950 1566.66667
30 BLAKE 2850 1566.66667
30 MARTIN 1250 1566.66667
14 rows selected.
方法二(表連線)：
SQL> select a.deptno,a.ename,a.sal,b.avg_sal from emp a,(select deptno,avg(sal) avg_sal from emp group by deptno) b where a.deptno=b.deptno order by deptno;
DEPTNO ENAME SAL AVG_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 2916.66667
10 KING 5000 2916.66667
10 MILLER 1300 2916.66667
20 SMITH 800 2175
20 JONES 2975 2175
20 SCOTT 3000 2175
20 ADAMS 1100 2175
20 FORD 3000 2175
30 ALLEN 1600 1566.66667
30 WARD 1250 1566.66667
30 MARTIN 1250 1566.66667
30 BLAKE 2850 1566.66667
30 TURNER 1500 1566.66667
30 JAMES 950 1566.66667
14 rows selected.
2 顯示各部門員工的工資，並附帶顯示該部分的最高工資。
方法一(分析函式)：
SQL> select deptno,ename,sal,max(sal) over (partition by deptno) max_sal from emp;
DEPTNO ENAME SAL MAX_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 JONES 2975 3000
20 FORD 3000 3000
20 ADAMS 1100 3000
20 SMITH 800 3000
20 SCOTT 3000 3000
30 WARD 1250 2850
30 TURNER 1500 2850
30 ALLEN 1600 2850
30 JAMES 950 2850
30 BLAKE 2850 2850
30 MARTIN 1250 2850
14 rows selected.
當省略視窗子句時:
a) 如果存在order by則預設的視窗是unbounded preceding and current row --當前組的第一行到當前行
b) 如果同時省略order by則預設的視窗是unbounded preceding and unbounded following --整個組
SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename) max_sal from emp;
DEPTNO ENAME SAL MAX_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 2450
10 KING 5000 5000
10 MILLER 1300 5000
20 ADAMS 1100 1100
20 FORD 3000 3000
20 JONES 2975 3000
20 SCOTT 3000 3000
20 SMITH 800 3000
30 ALLEN 1600 1600
30 BLAKE 2850 2850
30 JAMES 950 2850
30 MARTIN 1250 2850
30 TURNER 1500 2850
30 WARD 1250 2850
14 rows selected.
當省略視窗子句時:
a) 如果存在order by則預設的視窗是unbounded preceding and current row --當前組的第一行到當前行
b) 如果同時省略order by則預設的視窗是unbounded preceding and unbounded following --整個組
SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename rows between unbounded preceding and unbounded following) max_sal from emp;
DEPTNO ENAME SAL MAX_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 ADAMS 1100 3000
20 FORD 3000 3000
20 JONES 2975 3000
20 SCOTT 3000 3000
20 SMITH 800 3000
30 ALLEN 1600 2850
30 BLAKE 2850 2850
30 JAMES 950 2850
30 MARTIN 1250 2850
30 TURNER 1500 2850
30 WARD 1250 2850
14 rows selected.
方法二(表連線）
SQL> select a.deptno,a.ename,a.sal,b.max from emp a,(select deptno,max(sal) max from emp group by deptno) b where a.deptno=b.deptno order by deptno;
DEPTNO ENAME SAL MAX
---------- ---------- ---------- ----------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 SMITH 800 3000
20 JONES 2975 3000
20 SCOTT 3000 3000
20 ADAMS 1100 3000
20 FORD 3000 3000
30 ALLEN 1600 2850
30 WARD 1250 2850
30 MARTIN 1250 2850
30 BLAKE 2850 2850
30 TURNER 1500 2850
30 JAMES 950 2850
14 rows selected.
3 對各部門進行分組，並附帶顯示第一行至當前行的彙總
SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row) sum_sal from emp;
--注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至當前行的彙總
DEPTNO ENAME SAL SUM_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 2450
10 KING 5000 7450
10 MILLER 1300 8750
20 ADAMS 1100 1100
20 FORD 3000 4100
20 JONES 2975 7075
20 SCOTT 3000 10075
20 SMITH 800 10875
30 ALLEN 1600 1600
30 BLAKE 2850 4450
30 JAMES 950 5400
30 MARTIN 1250 6650
30 TURNER 1500 8150
30 WARD 1250 9400
14 rows selected.
4 對各部門進行分組，並附帶顯示當前行至最後一行的彙總
SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between current row and unbounded following) max from emp;
--注意ROWS BETWEEN current row AND unbounded following 指當前行到最後一行的彙總
DEPTNO ENAME SAL MAX
---------- ---------- ---------- ----------
10 CLARK 2450 8750
10 KING 5000 6300
10 MILLER 1300 1300
20 ADAMS 1100 10875
20 FORD 3000 9775
20 JONES 2975 6775
20 SCOTT 3000 3800
20 SMITH 800 800
30 ALLEN 1600 9400
30 BLAKE 2850 7800
30 JAMES 950 4950
30 MARTIN 1250 4000
30 TURNER 1500 2750
30 WARD 1250 1250
14 rows selected.
5 對各部門進行分組，並附帶顯示當前行的上一行(rownum-1)到當前行的彙總
SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and current row) max from emp;
--注意ROWS BETWEEN 1 preceding AND current row 是指當前行的上一行(rownum-1)到當前行的彙總
DEPTNO ENAME SAL MAX
---------- ---------- ---------- ----------
10 CLARK 2450 2450
10 KING 5000 7450
10 MILLER 1300 6300
20 ADAMS 1100 1100
20 FORD 3000 4100
20 JONES 2975 5975
20 SCOTT 3000 5975
20 SMITH 800 3800
30 ALLEN 1600 1600
30 BLAKE 2850 4450
30 JAMES 950 3800
30 MARTIN 1250 2200
30 TURNER 1500 2750
30 WARD 1250 2750
14 rows selected.
6 對各部門進行分組，並附帶顯示當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的彙總
SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and 2 following) max from emp;
--注意ROWS BETWEEN 1 preceding AND 2 following 是指當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的彙總
DEPTNO ENAME SAL MAX
---------- ---------- ---------- ----------
10 CLARK 2450 8750
10 KING 5000 8750
10 MILLER 1300 6300
20 ADAMS 1100 7075
20 FORD 3000 10075
20 JONES 2975 9775
20 SCOTT 3000 6775
20 SMITH 800 3800
30 ALLEN 1600 5400
30 BLAKE 2850 6650
30 JAMES 950 6550
30 MARTIN 1250 4950
30 TURNER 1500 4000
30 WARD 1250 2750
14 rows selected.
SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno) sum from emp;
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
10 CLARK 2450 1300
10 KING 5000 1300
10 MILLER 1300 1300
20 JONES 2975 3000
20 FORD 3000 3000
20 ADAMS 1100 3000
20 SMITH 800 3000
20 SCOTT 3000 3000
30 WARD 1250 1250
30 TURNER 1500 1250
30 ALLEN 1600 1250
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
30 JAMES 950 1250
30 BLAKE 2850 1250
30 MARTIN 1250 1250
14 rows selected.
SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno order by sal desc) sum from emp;
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
10 KING 5000 5000
10 CLARK 2450 2450
10 MILLER 1300 1300
20 SCOTT 3000 3000
20 FORD 3000 3000
20 JONES 2975 2975
20 ADAMS 1100 1100
20 SMITH 800 800
30 BLAKE 2850 2850
30 ALLEN 1600 1600
30 TURNER 1500 1500
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
30 MARTIN 1250 1250
30 WARD 1250 1250
30 JAMES 950 950
14 rows selected.
SQL> select deptno,ename,sal,first_value(sal) over(partition by deptno) sum from emp;
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
10 CLARK 2450 2450
10 KING 5000 2450
10 MILLER 1300 2450
20 JONES 2975 2975
20 FORD 3000 2975
20 ADAMS 1100 2975
20 SMITH 800 2975
20 SCOTT 3000 2975
30 WARD 1250 1250
30 TURNER 1500 1250
30 ALLEN 1600 1250
DEPTNO ENAME SAL SUM
---------- ---------- ---------- ----------
30 JAMES 950 1250
30 BLAKE 2850 1250
30 MARTIN 1250 1250
14 rows selected.
=======
Rank()
=======
一 要求：同時取出語文成績和數學成績的前三名；
1：
SQL> select * from t1 order by 1,2 desc;
SUBJECT SCORE
------------------------------ ----------
chinese 120
chinese 88
chinese 77
chinese 70
chinese 65
matchs 150
matchs 100
matchs 90
matchs 80
matchs 60
10 rows selected.
2：
SQL> select rank() over(partition by subject order by score desc) rank,t1.* from t1;
RANK SUBJECT SCORE
---------- ------------------------------ ----------
1 chinese 120
2 chinese 88
3 chinese 77
4 chinese 70
5 chinese 65
1 matchs 150
2 matchs 100
3 matchs 90
4 matchs 80
5 matchs 60
10 rows selected.
3：
SQL> select * from (select rank() over(partition by subject order by score desc) rank,t1.* from t1) a where a.rank<=3;
RANK SUBJECT SCORE
---------- ------------------------------ ----------
1 chinese 120
2 chinese 88
3 chinese 77
1 matchs 150
2 matchs 100
3 matchs 90
6 rows selected.
二 要求：取出分數最高的前三名
SQL> select * from (select rank() over(order by score desc) rank,t1.* from t1) a where a.rank<=3;
RANK SUBJECT SCORE
---------- ------------------------------ ----------
1 matchs 150
2 chinese 120
3 matchs 100
三 要求：取出分數排在第五到第十名
SQL> select * from (select rank() over(order by score desc) rank,t1.* from t1) a where a.rank between 5 and 10;
RANK SUBJECT SCORE
---------- ------------------------------ ----------
5 chinese 88
6 matchs 80
7 chinese 77
8 chinese 70
9 chinese 65
10 matchs 60
6 rows selected.
四 要求：每個部門工資前三名
SQL> select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;
DEPTNO ENAME SAL RK
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
9 rows selected.
=============
dense_rank()
=============
dense_rank與rank()用法相當，但是有一個區別：dence_rank在並列關係是，相關等級不會跳過。rank則跳過
SQL> select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;
DEPTNO ENAME SAL RK
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 ADAMS 1100 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
10 rows selected.
SQL> select * from (select deptno,ename,sal from emp order by sal desc) a where rownum<=5;
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
30 BLAKE 2850
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1763699/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分析函式七——分析函式案例Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- Oracle分析函式Oracle函式
- Oracle分析函式與視窗函式Oracle函式
- oracle 分析函式(轉)Oracle函式
- oracle分析函式（一）Oracle函式
- Oracle常用分析函式Oracle函式
- Oracle分析函式{轉}Oracle函式
- Oracle分析函式-6Oracle函式
- Oracle分析函式-5Oracle函式
- Oracle分析函式-4Oracle函式
- Oracle分析函式-2Oracle函式
- Oracle分析函式-1Oracle函式
- Oracle分析函式-3Oracle函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- oracle 10g函式大全--分析函式Oracle 10g函式
- 聊聊Oracle排序分析函式Oracle排序函式
- oracle 統計分析函式Oracle函式
- ORACLE分析函式手冊Oracle函式
- Oracle 分析函式詳解Oracle函式
- Oracle 分析函式的使用Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- ORACLE分析函式手冊(轉)Oracle函式
- ORACLE分析函式手冊二Oracle函式
- Oracle分析函式使用總結Oracle函式
- oracle分析函式_小貼(一)Oracle函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- ORACLE分析函式手冊二(轉)Oracle函式
- oracle分析函式,keep and over解說Oracle函式
- Expert 101 Oracle 分析函式Oracle函式
- Oracle 分析函式使用介紹(轉)Oracle函式
- ORACLE函式介紹第七篇 非著名函式之分析函式Oracle函式
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- ORACLE函式介紹第五篇 分析函式簡述Oracle函式
- oracle學習筆記８: 分析函式Oracle筆記函式
- oracle分析函式之ratio_to_reportOracle函式