备注:测试数据库版本为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() over (partition-clause order-by-clause)
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 (expression, offset, default) over (partition-clause order-by-clause)
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语法:
ratio_to_report(exp) over()
测试数据:
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语法:
percent_rank(expr) within group (order-by-clause ) over ([partition-by-clause] [order-by-clause] )
PERCENTILE_DISC语法:
PERCENTILE_DISC(expr) within group (order-by-clause ) over ([partition-by-clause] [order-by-clause] )
--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(expr) OVER ([ query_partition_clause ] order_by_clause)
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
