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分析函式之開窗函式over()詳解Oracle函式
- ORACLE分析函式手冊二(轉)Oracle函式
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- oracle 函式Oracle函式
- oracle or 函式Oracle函式
- Oracle常用函式Oracle函式
- 7 Oracle 函式Oracle函式
- Oracle 字串函式Oracle字串函式
- Oracle 字串函式Oracle字串函式
- Oracle分析函式-first_value()和last_value()Oracle函式AST
- oracle 21c 新特性之 CHECKSUM 分析函式Oracle函式
- Oracle vs PostgreSQL Develop（14） - 分析函式KEEP DENSE_RANKOracleSQLdev函式
- Oracle分析函式之LEAD和LAG實際應用Oracle函式
- Oracle OCP(03)：字元函式、數字函式和日期函式Oracle字元函式
- Oracle 自定義函式Oracle函式
- oracle 高階函式Oracle函式
- oracle json 解析函式OracleJSON函式
- Oracle 隨機函式Oracle隨機函式
- Oracle函式彙總Oracle函式
- oracle 10g函式大全–日期型函式Oracle 10g函式
- oracle常用函式介紹Oracle函式
- Oracle OCP(04)：聚合函式Oracle函式
- oracle函式手冊(轉)Oracle函式
- 【Oracle的NVL函式用法】Oracle函式
- Oracle函式入坑指南Oracle函式
- 【函式】Oracle12c 列轉行函式使用listagg函式Oracle
- oracle interval日期函式的bug！Oracle函式
- Oracle OCP(05)：轉換函式Oracle函式
- Oracle的LAST_DAY函式OracleAST函式
- Oracle中pivot函式詳解Oracle函式
- 工作中，Oracle常用函式Oracle函式
- Oracle中Decode()函式的使用Oracle函式
- Hive之分析函式Hive函式
- ORALCE函式：LAG()和LEAD() 分析函式詳解函式
- Oracle的SYS_CONNECT_BY_PATH函式Oracle函式
- 原創：oracle聚合函式介紹Oracle函式
- oracle Forms Builder常用函式 (轉載)OracleORMUI函式