文章目錄
-
- 一.row_number、rank、dense_rank
- 二.lag、lead
- 三.first_value、last_value、nth_value
- 四.ratio_to_report
- 五.percent_rank、percentile_cont、percentile_dist
- 六.ntile
備注:測試資料庫版本為Oracle 11g R2
如需要scott使用者下建表及錄入資料語句，可參考:
scott建表及錄入資料sql腳本
這個blog我們來聊聊Oracle進階分析函數
Oracle的分析函數在複雜查詢以及資料倉庫中應用得比較頻繁
與sql打交道比較多的技術人員都需要掌握
| 函數名 | 函數用途 |
|---|---|
| row_number | 對行進行排序并為每一行增加一個唯一編号。這是一個非确定性函數 |
| rank | 将資料行值按照排序後的順序進行排名，在有并列的情況下排名值将被跳過 |
| dense_rank | 将資料行值按照排序後的順序進行排名，在有并列的情況下也不跳過排名值 |
| lag | 通路一個分區或結果集中之前的一行 |
| lead | 通路一個分區或結果集中之後的一行 |
| first_value | 通路一個分區或結果集中第一行 |
| last_value | 通路一個分區或結果集中最後一行 |
| nth_value | 通路一個分區或結果集中的任意一行 |
| ratio_to_report | 計算報告中值的比例 |
| percent_rank | 将計算得到的排名值标準化 |
| percentile_cont | 取出與指定的排名百分比相比對的值，是percent_rank函數的反函數 |
| percentile_dist | 取出與指定的排名百分比相比對的值，采用謹慎分布模型 |
| ntile | 将資料行分組為單元 |
分析函數有3個基本組成部分：
1.分區子句
2.排序子句
3.開窗子句
function1 (argument1,argument2,..argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])
視窗說明子句的文法:
預設的視窗子句是rows between unbounded preceding and current row。如果你沒有顯示聲明視窗，就将會使用預設視窗。
并不是所有的分析函數都支援開窗子句
[rows | range] between <start expr> and [end expr]
whereas
<start expr> is [unbounded preceding | current row | n preceding | n following]
<end expr> is [unbounded following | current row | n preceding | n following]
一.row_number、rank、dense_rank
row_number文法：
row_number不支援開窗子句
rank、dense_rank文法同row_number文法
現在需要對分不同部門來看部門内的工資排名，且從大到小排列：
--可以看到deptno為30的員工工資有重複的,重複的工資為1250
--row_number() 不關注重複的，直接排名，1-2-3-4-5-6
--rank() 重複排名,會跳過，1-2-3-4-4-6
--dense_rank() 重複排名,不跳過，1-2-3-4-4-5
select a.empno,
a.ename,
a.deptno,
a.sal,
row_number() over(partition by a.deptno order by a.sal desc) num,
rank() over(partition by a.deptno order by a.sal desc) rank,
dense_rank() over(partition by a.deptno order by a.sal desc) dense_rank
from emp a;
SQL> select a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 row_number() over(partition by a.deptno order by a.sal desc) num,
6 rank() over(partition by a.deptno order by a.sal desc) rank,
7 dense_rank() over(partition by a.deptno order by a.sal desc) dense_rank
8 from emp a;
EMPNO ENAME DEPTNO SAL NUM RANK DENSE_RANK
----- ---------- ------ --------- ---------- ---------- ----------
7839 KING 10 5000.00 1 1 1
7782 CLARK 10 2450.00 2 2 2
7934 MILLER 10 1300.00 3 3 3
7788 SCOTT 20 3000.00 1 1 1
7902 FORD 20 3000.00 2 1 1
7566 JONES 20 2975.00 3 3 2
7876 ADAMS 20 1100.00 4 4 3
7369 SMITH 20 800.00 5 5 4
7698 BLAKE 30 2850.00 1 1 1
7499 ALLEN 30 1600.00 2 2 2
7844 TURNER 30 1500.00 3 3 3
7654 MARTIN 30 1250.00 4 4 4
7521 WARD 30 1250.00 5 4 4
7900 JAMES 30 950.00 6 6 5
14 rows selected
二.lag、lead
lag文法:
lag不支援開窗子句
lead同lag文法
--根據分組，取值上n條和下n條 如果是第一條或最後一條，就給個預設值
SELECT a.empno,
a.deptno,
a.hiredate,
a.sal,
lag(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre_sal,
lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal,
lag(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre2_sal,
lead(sal, 2, 0) over(partition by a.deptno order by hiredate asc) next_2sal
FROM emp a;
SQL> --根據分組，取值上n條和下n條 如果是第一條或最後一條，就給個預設值
SQL> SELECT a.empno,
2 a.deptno,
3 a.hiredate,
4 a.sal,
5 lag(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre_sal,
6 lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal,
7 lag(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre2_sal,
8 lead(sal, 2, 0) over(partition by a.deptno order by hiredate asc) next_2sal
9 FROM emp a;
EMPNO DEPTNO HIREDATE SAL PRE_SAL NEXT_SAL PRE2_SAL NEXT_2SAL
----- ------ ------------------------------ --------- ---------- ---------- ---------- ----------
7782 10 1981/6/9 星期二 2450.00 0 5000 0 1300
7839 10 1981/11/17 星期二 5000.00 2450 1300 0 0
7934 10 1982/1/23 星期六 1300.00 5000 0 2450 0
7369 20 1980/12/17 星期三 800.00 0 2975 0 3000
7566 20 1981/4/2 星期四 2975.00 800 3000 0 3000
7902 20 1981/12/3 星期四 3000.00 2975 3000 800 1100
7788 20 1987/4/19 星期日 3000.00 3000 1100 2975 0
7876 20 1987/5/23 星期六 1100.00 3000 0 3000 0
7499 30 1981/2/20 星期五 1600.00 0 1250 0 2850
7521 30 1981/2/22 星期日 1250.00 1600 2850 0 1500
7698 30 1981/5/1 星期五 2850.00 1250 1500 1600 1250
7844 30 1981/9/8 星期二 1500.00 2850 1250 1250 950
7654 30 1981/9/28 星期一 1250.00 1500 950 2850 0
7900 30 1981/12/3 星期四 950.00 1250 0 1500 0
14 rows selected
--沒有比自己小我的我們設為AAA,沒有比自己大的，我們設定為ZZZ
select deptno,
ename,
lag(ename, 1, 'AAA') over(partition by deptno order by ename) lower_name,
lead(ename, 1, 'ZZZ') over(partition by deptno order by ename) higher_name
from emp;
--部門重複的話值輸出第一行的部門編号
select (case when deptno= lag(deptno,1)over(partition by deptno order by ename) then null else deptno end) deptno,
ename,
lag(ename, 1, 'AAA') over(partition by deptno order by ename) lower_name,
lead(ename, 1, 'ZZZ') over(partition by deptno order by ename) higher_name
from emp;
SQL> --沒有比自己小我的我們設為AAA,沒有比自己大的，我們設定為ZZZ
SQL> select deptno,
2 ename,
3 lag(ename, 1, 'AAA') over(partition by deptno order by ename) lower_name,
4 lead(ename, 1, 'ZZZ') over(partition by deptno order by ename) higher_name
5 from emp;
DEPTNO ENAME LOWER_NAME HIGHER_NAME
------ ---------- ---------- -----------
10 CLARK AAA KING
10 KING CLARK MILLER
10 MILLER KING ZZZ
20 ADAMS AAA FORD
20 FORD ADAMS JONES
20 JONES FORD SCOTT
20 SCOTT JONES SMITH
20 SMITH SCOTT ZZZ
30 ALLEN AAA BLAKE
30 BLAKE ALLEN JAMES
30 JAMES BLAKE MARTIN
30 MARTIN JAMES TURNER
30 TURNER MARTIN WARD
30 WARD TURNER ZZZ
14 rows selected
SQL> --部門重複的話值輸出第一行的部門編号
SQL> select (case when deptno= lag(deptno,1)over(partition by deptno order by ename) then null else deptno end) deptno,
2 ename,
3 lag(ename, 1, 'AAA') over(partition by deptno order by ename) lower_name,
4 lead(ename, 1, 'ZZZ') over(partition by deptno order by ename) higher_name
5 from emp;
DEPTNO ENAME LOWER_NAME HIGHER_NAME
---------- ---------- ---------- -----------
10 CLARK AAA KING
KING CLARK MILLER
MILLER KING ZZZ
20 ADAMS AAA FORD
FORD ADAMS JONES
JONES FORD SCOTT
SCOTT JONES SMITH
SMITH SCOTT ZZZ
30 ALLEN AAA BLAKE
BLAKE ALLEN JAMES
JAMES BLAKE MARTIN
MARTIN JAMES TURNER
TURNER MARTIN WARD
WARD TURNER ZZZ
14 rows selected
三.first_value、last_value、nth_value
first_value、last_value文法:
first_value(expression) over (partition-clause order-by-clause windowing-clause)
last_value(expression) over (partition-clause order-by-clause windowing-clause)
nth_value文法:
nth_value (measure, n) [ from first | from last ] [ respect nulls | ignore nulls ]
over (partitioning-clause order-by-clause windowing-clause)
/*
需求:求每個部門工資最高的和工資最低的以及工資第二高的
*/
--預設不帶開窗子句,從第一行到目前行
select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
first_value(a.sal) over(partition by a.deptno order by sal) first,
last_value(a.sal) over(partition by a.deptno order by sal) last,
nth_value(a.sal,2) over(partition by a.deptno order by sal) top_2
from emp a;
--rows between unbounded preceding and current row 從第一行到目前行
select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) first,
last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) last,
nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and current row) top_2
from emp a;
--rows between unbounded preceding and unbounded following 從第一行到最後一行
select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) first,
last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) last,
nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) top_2
from emp a;
--1 preceding and 1 following 目前行的前一行到目前行的後一行
select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
first_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) first,
last_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) last,
nth_value(a.sal,2) over(partition by a.deptno order by sal rows between 1 preceding and 1 following top_2
from emp a;
SQL> /*
2 需求:求每個部門工資最高的和工資最低的以及工資第二高的
3 */
SQL> --預設不帶開窗子句,從第一行到目前行
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by sal) first,
3 last_value(a.sal) over(partition by a.deptno order by sal) last,
4 nth_value(a.sal,2) over(partition by a.deptno order by sal) top_2
5 from emp a;
EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2
----- ------ -------- --------- ---------- ---------- ----------
7934 10 1982 1300.00 1300 1300
7782 10 1981 2450.00 1300 2450 2450
7839 10 1981 5000.00 1300 5000 2450
7369 20 1980 800.00 800 800
7876 20 1987 1100.00 800 1100 1100
7566 20 1981 2975.00 800 2975 1100
7788 20 1987 3000.00 800 3000 1100
7902 20 1981 3000.00 800 3000 1100
7900 30 1981 950.00 950 950
7654 30 1981 1250.00 950 1250 1250
7521 30 1981 1250.00 950 1250 1250
7844 30 1981 1500.00 950 1500 1250
7499 30 1981 1600.00 950 1600 1250
7698 30 1981 2850.00 950 2850 1250
14 rows selected
SQL> --
SQL> --
SQL> --rows between unbounded preceding and current row 從第一行到目前行
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) first,
3 last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) last,
4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and current row) top_2
5 from emp a;
EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2
----- ------ -------- --------- ---------- ---------- ----------
7934 10 1982 1300.00 1300 1300
7782 10 1981 2450.00 1300 2450 2450
7839 10 1981 5000.00 1300 5000 2450
7369 20 1980 800.00 800 800
7876 20 1987 1100.00 800 1100 1100
7566 20 1981 2975.00 800 2975 1100
7788 20 1987 3000.00 800 3000 1100
7902 20 1981 3000.00 800 3000 1100
7900 30 1981 950.00 950 950
7654 30 1981 1250.00 950 1250 1250
7521 30 1981 1250.00 950 1250 1250
7844 30 1981 1500.00 950 1500 1250
7499 30 1981 1600.00 950 1600 1250
7698 30 1981 2850.00 950 2850 1250
14 rows selected
SQL> --
SQL> --
SQL> --rows between unbounded preceding and unbounded following 從第一行到最後一行
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) first,
3 last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) last,
4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) top_2
5 from emp a;
EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2
----- ------ -------- --------- ---------- ---------- ----------
7934 10 1982 1300.00 1300 5000 2450
7782 10 1981 2450.00 1300 5000 2450
7839 10 1981 5000.00 1300 5000 2450
7369 20 1980 800.00 800 3000 1100
7876 20 1987 1100.00 800 3000 1100
7566 20 1981 2975.00 800 3000 1100
7788 20 1987 3000.00 800 3000 1100
7902 20 1981 3000.00 800 3000 1100
7900 30 1981 950.00 950 2850 1250
7654 30 1981 1250.00 950 2850 1250
7521 30 1981 1250.00 950 2850 1250
7844 30 1981 1500.00 950 2850 1250
7499 30 1981 1600.00 950 2850 1250
7698 30 1981 2850.00 950 2850 1250
14 rows selected
SQL> --
SQL> --
SQL> --1 preceding and 1 following 目前行的前一行到目前行的後一行
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) first,
3 last_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) last,
4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) top_2
5 from emp a;
EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2
----- ------ -------- --------- ---------- ---------- ----------
7934 10 1982 1300.00 1300 2450 2450
7782 10 1981 2450.00 1300 5000 2450
7839 10 1981 5000.00 2450 5000 5000
7369 20 1980 800.00 800 1100 1100
7876 20 1987 1100.00 800 2975 1100
7566 20 1981 2975.00 1100 3000 2975
7788 20 1987 3000.00 2975 3000 3000
7902 20 1981 3000.00 3000 3000 3000
7900 30 1981 950.00 950 1250 1250
7654 30 1981 1250.00 950 1250 1250
7521 30 1981 1250.00 1250 1500 1250
7844 30 1981 1500.00 1250 1600 1500
7499 30 1981 1600.00 1500 2850 1600
7698 30 1981 2850.00 1600 2850 2850
14 rows selected
四.ratio_to_report
ratio_to_report文法:
測試資料:
DROP TABLE testa;
CREATE TABLE testa (area VARCHAR2 (20), month VARCHAR2 (20),
amount NUMBER);
insert into testa values ('上海', '1月', 199);
insert into testa values ('上海', '2月', 199);
insert into testa values ('上海', '3月', 155);
insert into testa values ('上海', '3月', 155);
insert into testa values ('上海', '4月', 125);
insert into testa values ('廣州', '1月', 75);
insert into testa values ('廣州', '2月', 67);
insert into testa values ('北京', '1月', 235);
insert into testa values ('北京', '2月', 330);
Commit;
--求每個月銷售額占比
SELECT area,
MONTH,
SUM(amount) amount,
ROUND(ratio_to_report(SUM(amount)) OVER(PARTITION BY area), 4) * 100 area_pct
FROM testa
GROUP BY area, MONTH;
SQL> --求每個月銷售額占比
SQL> SELECT area,
2 MONTH,
3 SUM(amount) amount,
4 ROUND(ratio_to_report(SUM(amount)) OVER(PARTITION BY area), 4) * 100 area_pct
5 FROM testa
6 GROUP BY area, MONTH;
AREA MONTH AMOUNT AREA_PCT
-------------------- -------------------- ---------- ----------
北京 1月 235 41.59
北京 2月 330 58.41
廣州 1月 75 52.82
廣州 2月 67 47.18
上海 1月 199 23.89
上海 2月 199 23.89
上海 3月 310 37.21
上海 4月 125 15.01
8 rows selected
五.percent_rank、percentile_cont、percentile_dist
percent_rank文法:
percent_rank(expr) within group (order-by-clause )
percent_rank() over ([partition-by-clause] [order-by-clause] )
PERCENTILE_CONT文法:
PERCENTILE_DISC文法:
–percent_rank函數以0到1之間的分數形式傳回某個值在資料分區中的排名
–percent_rank的計算公式為(rank-1)/(n-1)
SELECT a.empno,
a.ename,
a.deptno,
a.sal,
percent_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) num
FROM emp a;
SQL> SELECT a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 percent_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) num
6 FROM emp a;
EMPNO ENAME DEPTNO SAL NUM
----- ---------- ------ --------- ----------
7839 KING 10 5000.00 0
7782 CLARK 10 2450.00 0.5
7934 MILLER 10 1300.00 1
7788 SCOTT 20 3000.00 0
7902 FORD 20 3000.00 0
7566 JONES 20 2975.00 0.5
7876 ADAMS 20 1100.00 0.75
7369 SMITH 20 800.00 1
7698 BLAKE 30 2850.00 0
7499 ALLEN 30 1600.00 0.2
7844 TURNER 30 1500.00 0.4
7654 MARTIN 30 1250.00 0.6
7521 WARD 30 1250.00 0.6
7900 JAMES 30 950.00 1
14 rows selected
–percentile_cont
–如計算一個城市或地區中等收入家庭的收入值，中位值是percent_rank為0.5
–percentile_cont(0.5)子句将會傳回中位值,如果沒有0.5,則取值上下最接近的2個值求平均值
SELECT ename,
sal,
deptno,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Cont",
PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) "Percent_Rank"
FROM emp
ORDER BY deptno,sal desc;
SQL> SELECT ename,
2 sal,
3 deptno,
4 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Cont",
5 PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) "Percent_Rank"
6 FROM emp
7 ORDER BY deptno,sal desc;
ENAME SAL DEPTNO Percentile_Cont Percent_Rank
---------- --------- ------ --------------- ------------
KING 5000.00 10 2450 0
CLARK 2450.00 10 2450 0.5
MILLER 1300.00 10 2450 1
SCOTT 3000.00 20 2975 0
FORD 3000.00 20 2975 0
JONES 2975.00 20 2975 0.5
ADAMS 1100.00 20 2975 0.75
SMITH 800.00 20 2975 1
BLAKE 2850.00 30 1375 0
ALLEN 1600.00 30 1375 0.2
TURNER 1500.00 30 1375 0.4
WARD 1250.00 30 1375 0.6
MARTIN 1250.00 30 1375 0.6
JAMES 950.00 30 1375 1
14 rows selected
SQL>
–percentile_disc
percentile_disc函數在功能上類似percentile_cont函數，隻是percentile_cont函數使用了連續分布模型，而percentile_disc函數使用了離散分布模型。
SELECT ename,
sal,
deptno,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Cont",
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Disc",
PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) "Percent_Rank"
FROM emp
ORDER BY deptno,sal desc;
SQL> SELECT ename,
2 sal,
3 deptno,
4 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Cont",
5 PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) "Percentile_Disc",
6 PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) "Percent_Rank"
7 FROM emp
8 ORDER BY deptno,sal desc;
ENAME SAL DEPTNO Percentile_Cont Percentile_Disc Percent_Rank
---------- --------- ------ --------------- --------------- ------------
KING 5000.00 10 2450 2450 0
CLARK 2450.00 10 2450 2450 0.5
MILLER 1300.00 10 2450 2450 1
SCOTT 3000.00 20 2975 2975 0
FORD 3000.00 20 2975 2975 0
JONES 2975.00 20 2975 2975 0.5
ADAMS 1100.00 20 2975 2975 0.75
SMITH 800.00 20 2975 2975 1
BLAKE 2850.00 30 1375 1500 0
ALLEN 1600.00 30 1375 1500 0.2
TURNER 1500.00 30 1375 1500 0.4
WARD 1250.00 30 1375 1500 0.6
MARTIN 1250.00 30 1375 1500 0.6
JAMES 950.00 30 1375 1500 1
14 rows selected
六.ntile
Ntile文法:
Ntile 把資料行分成N個桶。每個桶會有相同的行數，正負誤差為1
将員工表emp按照工資分為2、3個桶
--分成2個桶
SELECT ENAME, SAL, NTILE(2) OVER (ORDER BY SAL ASC ) FROM EMP;
--分成3個桶
SELECT ENAME, SAL, NTILE(3) OVER (ORDER BY SAL ASC ) FROM EMP;
SQL> --分成2個桶
SQL> SELECT ENAME, SAL, NTILE(2) OVER (ORDER BY SAL ASC ) FROM EMP;
ENAME SAL NTILE(2)OVER(ORDERBYSALASC)
---------- --------- ---------------------------
SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 1
WARD 1250.00 1
MARTIN 1250.00 1
MILLER 1300.00 1
TURNER 1500.00 1
ALLEN 1600.00 2
CLARK 2450.00 2
BLAKE 2850.00 2
JONES 2975.00 2
SCOTT 3000.00 2
FORD 3000.00 2
KING 5000.00 2
14 rows selected
SQL> --分成3個桶
SQL> SELECT ENAME, SAL, NTILE(3) OVER (ORDER BY SAL ASC ) FROM EMP;
ENAME SAL NTILE(3)OVER(ORDERBYSALASC)
---------- --------- ---------------------------
SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 1
WARD 1250.00 1
MARTIN 1250.00 1
MILLER 1300.00 2
TURNER 1500.00 2
ALLEN 1600.00 2
CLARK 2450.00 2
BLAKE 2850.00 2
JONES 2975.00 3
SCOTT 3000.00 3
FORD 3000.00 3
KING 5000.00 3
14 rows selected
![資料分析實戰20絕技[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)