over(Partition by...) 一个超级牛皮的ORACLE特有函数。
天天都用ORACLE，用了快2年了。最近才接触到这个功能强大而灵活的函数。真实惭愧啊！
oracle的分析函数over 及开窗函数
一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
1：统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30
规则：按天统计：每天都统计前面几天的总额
得到的结果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 14 49 --1天＋2天＋3天
4 18 67 .
5 30 97 .
2:统计各班成绩第一名的同学信息
NAME CLASS S
----- ----- ----------------------
fda 1 80
ffd 1 78
dss 1 95
cfe 2 74
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78
通过：
--
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
--
得到结果：
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
2.rank()和dense_rank()的区别是：
--rank()是跳跃排序，有两个第二名时接下来就是第四名
--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
3.分类统计 (并显示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum(c)over(partition by a) from t2
得到结果：
A B C SUM(C)OVER(PARTITIONBYA)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
如果用sum，group by 则只能得到
A SUM(C)
-- ----------------------
h 3
m 4
n 6
x 9
无法得到B列值
＝＝＝＝＝
select * from test
数据:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test
A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
---如果不需要已某个栏位的值分割,那就要用 null
eg: 就是将C的栏位值summary 放在每行后面
select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test
A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
求个人工资占部门工资的百分比
SQL> select * from salary;
NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;
NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100
二：开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
2：
over（order by salary range between 5 preceding and 5 following）
每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
例如:对于以下列
aa
1
2
2
2
3
4
5
6
7
9
sum(aa)over（order by aa range between 2 preceding and 2 following）
得出的结果是
AA SUM
---------------------- -------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9
就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和
对于aa=2来说 ，sum=1+2+2+2+3+4=14 ；
又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；
3：其它：
over（order by salary rows between 2 preceding and 4 following）
每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
等效
over(partition by null)
常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
示例
SQL> select type,qty from test;
TYPE QTY
---------- ----------
1 6
2 9
SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test;
TYPE QTY CNT2
---------- ---------- ------------
3 1/2
1 6 2/2
2 5 1/3
7 2/3
2 9 3/3
SQL> select * from test;
---------- -------------------------------------------------
1 11111
2 22222
3 33333
4 44444
SQL> select t.id,mc,to_char(b.rn)||'/'||t.id)e
2 from test t,
(select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L
4 where b.rn<=to_number(t.id)
order by id
ID MC TO_CHAR(B.RN)||'/'||T.ID
--------- -------------------------------------------------- ---------------------------------------------------
1 11111 1/1
2 22222 1/2
2 22222 2/2
3 33333 1/3
3 33333 2/3
3 33333 3/3
44444 1/4 44444 2/4
4 44444 3/4CNOUG4 44444 4/4
10 rows selected
*******************************************************************
关于partition by
这些都是分析函数，好像是8.0以后才有的 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序） rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 lag（arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。
1.
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
2.
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
3.
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
4.
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;
5.
select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p
order by deptno;
6.
select deptno, sal,sum(sal) over(partition by deptno) from emp;--每行记录后都有总计值 select deptno, sum(sal) from emp group by deptno;
7. 求每个部门的平均工资以及每个人与所在部门的工资差额
select deptno,ename,sal ,
round(avg(sal) over(partition by deptno)) as dept_avg_sal,
round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
from emp;
----------------------------------------------------------------------
1、rollup介绍：
group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据。（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。 oracle中rollup和mysql的差不多，但比mysql的强大（mysql只有下面的第1和第2使用方式），且oracle中rollup可以和order by一起使用。具体使用如下：
1）对比没有带rollup的goup by ：
Group by A,B产生的分组种数：1种；
group by A,B
返回结果集：也就是这一种分组的结果集。
2）带rollup但group by与rollup之间没有任何内容 ：
A、Group by rollup(A ,B) 产生的分组种数：3种；
第一种：group by A,B
第二种：group by A
第三种：group by NULL
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
B、Group by rollup(A ,B,C) 产生的分组种数：4种；
第一种：group by A,B,C
第二种：group by A,B
第三种：group by A
第四种：group by NULL
返回结果集：为以上四种分组统计结果集的并集且未去掉重复数据。
3）带rollup但groupby与rollup之间还包含有列信息
A、Group by A , rollup(A ,B) 产生的分组种数：3种；
第一种：group by A,A,B 等价于group by A,B
第二种：group by A,A 等价于group by A
第三种：group by A,NULL 等价于group by A
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
B、Group by C , rollup(A ,B) 产生的分组种数：3种；
第一种：group by C,A,B
第二种：group by C,A
第三种：group by C,NULL 等价于group by C
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
4）带rollup且rollup子句括号内又使用括号对列进行组合
A、Group by rollup((A ,B)) 产生的分组种数：2种；
第一种：group by A,B
第二种：group by NULL
返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。
B、Group by rollup(A ,(B,C)) 产生的分组种数：3种；
第一种：group by A,B,C
第二种：group by A
第三种：group by NULL
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
注：对这种情况，可以理解为几个列被括号括在一起时，就只能被看成一个整体，分组时不需要再细化。因此也可推断rollup括号内也顶多加到一重括号，加多重了应该没有任何意义（这个推断我没有做验证的哦）。
2、与rollup组合使用的其它几个辅助函数：
1）grouping()函数：
必须接受一列且只能接受一列做为其参数。参数列值为空返回1，参数列值非空返回0。（如果参数的列在rollup中，则返回1；否则返回0）
2）grouping_id()函数：
必须接受一列或多列做为其参数。返回值为按参数排列顺序，依次对各个参数使用grouping()函数，并将结果值依次串成一串二进制数然后再转化为十进制所得到的值。
例如：grouping(A) = 0 ;grouping(B) = 1;
则：grouping_id(A,B) = (01)2 = 1;
grouping_id(B,A)= (10)2 =2;
3）group_id()函数
调用时不需要且不能传入任何参数。返回值为某个特定的分组出现的重复次数(第一大点中的第3种情况中往往会产生重复的分组)。重复次数从0开始，例如某个分组第一次出现则返回值为0，第二次出现时返回值为1，……，第n次出现返回值为n-1。
注：使用以上三个函数往往是为了过滤掉一部分统计数据，而达到美化统计结果的作用。
3、cube和rollup区别：
带cube子句的groupby会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。
1）假设有n个维度，rollup会有n个聚合：
rollup(a,b) 统计列包含：(a,b)、(a)、()
rollup(a,b,c)统计列包含：(a,b,c)、(a,b)、(a)、()
……以此类推ing……
2）假设有n个纬度，cube会有2的n次方个聚合：
cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此类推ing……
oracle中的GROUP_ID、GROUPING、GROUPING_ID函数
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
关于ROLLUP和CUBE函数的使用，请参见我的另一篇文章。
http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx
1、在ROLLUP中对单列使用GROUPING()
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
加上GROUPING来看看
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
可以看到，为空的地方返回1，非空的地方返回0。
2、使用CASE转换GROUPING()的返回值
可能你会觉得前面的0和1太枯燥了，代表不了任何意义，说白了就是不够人性化，呵呵。这个时候我们可以使用CASE来转换为一些有意义的值。
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 sum(salary)
7 from employees2
8 group by rollup(division_id)
9 order by division_id;
DIV SUM(SALARY)
------------- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
all divisions 8881000
3、使用CASE和GROUPING()转换多个列的值
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum(salary)
11 from employees2
12 group by rollup(division_id,job_id)
13 order by division_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
DIV JOB SUM(SALARY)
------------- -------- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions all jobs 8881000
16 rows selected.
4、CUBE与GROUPING()结合使用
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum(salary)
11 from employees2
12 group by cube(division_id,job_id)
13 order by division_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
DIV JOB SUM(SALARY)
------------- -------- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions ENG 245000
all divisions MGR 6246000
all divisions PRE 800000
all divisions TEC 115000
all divisions WOR 1475000
all divisions all jobs 8881000
21 rows selected.
5、使用GROUPING SETS子句
使用GROUPING SETS子句可以只返回小计记录。
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by grouping sets(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
9 rows selected.
-----------------------------------------------------------------------
建表：
create table earnings
( earnmonth varchar2(32),--打工月份
area varchar2(10),--地区
sno varchar2(10),--打工者编号
sname varchar(19),--名字
times int,--次数
singleincome number(10,2),--每次多少钱
personincome number---当月总收入入
)
insert into earnings values('200912','北京','511601','大奎',11,30,11*30);
insert into earnings values('200912','北京','511602','大凯',8,25,8*25);
insert into earnings values('200912','北京','511603','小东',30,6.25,6.25*30);
insert into earnings values('200912','北京','511604','大亮',16,8.25,16*8.25);
insert into earnings values('200912','北京','511605','小王',11,30,11*30);
insert into earnings values('200912','南京','511301','小雨',15,12.25,15*12.25);
insert into earnings values('200912','南京','511302','小饭',17,16.67,17*16.67);
insert into earnings values('200912','南京','511303','小妮',27,33.33,17*33.33);
insert into earnings values('200912','南京','511304','小第',16,18,16*18);
insert into earnings values('200912','南京','511305','小阳',11,19.88,11*19.88);
insert into earnings values('201001','北京','511601','大奎',0,30,0);
insert into earnings values('201001','北京','511602','大凯',14,25,14*25);
insert into earnings values('201001','北京','511603','小东',19,6.25,6.25*19);
insert into earnings values('201001','北京','511604','大亮',7,8.25,7*8.25);
insert into earnings values('201001','北京','511605','小王',8,30,8*30);
insert into earnings values('201001','南京','511301','小雨',6,12.25,6*12.25);
insert into earnings values('201001','南京','511302','小饭',11,16.67,11*16.67);
insert into earnings values('201001','南京','511303','小妮',13,33.33,13*33.33);
insert into earnings values('201001','南京','511304','小第',20,18,20*18);
insert into earnings values('201001','南京','511305','小阳',30,19.88,30*19.88);
SELECT * FROM earnings
1 . sum 函数 统计总和
按照月份 统计每个地区的总收入
select earnmonth ,area,sum(personincome) from earnings group by earnmonth,area;
------------------------------------------------------------------------------------------------------
2 . rollup 函数
-- http://blog.itpub.net/519536/viewspace-610995/
--ROLLUP 分组函数可以理解为Group By分组函数封装后的精简用法
按照月份 地区统计收入
-- group by 函数后面接rollup 是在纯粹的 group by 的分组上再 加上对earnmonth的汇总统计
select earnmonth ,area,sum(personincome) from earnings group by rollup(earnmonth,area);
-- *****注意earnmonth，area 谁在前后是有区别的，影响汇总的结果是月份还是区域，
-- 实现上面效果的普通的sql 语句
select earnmonth ,area,sum(personincome) from earnings group by earnmonth,area
union all
select earnmonth ,null,sum(personincome) from earnings group by earnmonth
union all
select null ,null,sum(personincome) from earnings
order by 1,2;
--其他知识 上面语句中想问问这个是什么意思??
order by 1,2 根据第一列和第二列排序 相当于 order by earnmonth ,area
-- UNION 用法 http://www.w3school.com.cn/sql/sql_union.asp
------------------------------------------------------------------------------------------------------
3 . cube 函数 http://blog.itpub.net/519536/viewspace-610997/
-- https://msdn.microsoft.com/zh-cn/library/ms175939
按照月份 地区 进行收入总收入
select earnmonth ,area,sum(personincome) from earnings group by cube(earnmonth,area) order by 1,2;
--group by 后面接cube 是对earnmonth汇总统计的基础上对area 在统计
--group by 是分组函数，按照earnmonth，area先后次序分组
--上面的查询 是先按照earnmonth 分组，在earnmonth 内部再按照area分组，并且在area组内统计personincome总和
------------------------------------------------------------------------------------------------------
4 . grouping 函数 http://millerrch.iteye.com/blgroupingog/1882423
-- 上面的rollup 和 cube 函数都会对结果产生null，这时 可用grouping 来确认该记录是哪个字段得出来的
select earnmonth ,area,sum(personincome) from earnings group by rollup(earnmonth,area); -- 这是有空值的
--grouping 函数的用法 带一个参数，参数是字段名称如果是本身的结果（列值非空）就返回0，如果是合计的结果（列值为空）则返回1
select earnmonth ,（case when(grouping(area)=1 )and (grouping(earnmonth)=0 )then '月份小计' when(grouping(area)=1 )and (grouping(earnmonth)=1 )then '总计' else area end ) as area,sum(personincome) from earnings group by rollup(earnmonth,area);
------------------------------------------------------------------------------------------------------
5 . rank 函数,dense_rank 函数,row_number 函数
-- http://www.jb51.net/article/85125.htm
按照 月份 地区 求打工收入排序
-- rank 排名会有并列，两个第一名 会按照人数跳跃产生名次 1,1,3.。。。。。。。
--http://www.jb51.net/article/51627.htm
select earnmonth ,area,sname,personincome,rank () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
-- dense_rank 排名会有并列，两个第一名 但不会按照人数跳跃产生名次，1,1,2.。。。。。。。
select earnmonth ,area,sname,personincome,dense_rank () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
-- row_number 排名不会有并列，既是两个数据一样，排名也不一样，1，2，3，4。。。。。。
-- http://blog.csdn.net/tanzuai/article/details/42391885
select earnmonth ,area,sname,personincome,row_number () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
------------------------------------------------------------------------------------------------------
SUM 累计求和
select earnmonth ,area,sname,personincome,sum (personincome) over(partition by sname order by personincome asc) "总收入" from earnings;
------------------------------------------------------------------------------------------------------
sum,avg.max,min综合运用
--按照月份，地区 求打工收入最高值，最低，平均，总和
select distinct earnmonth "月份" ,area "地区",max(personincome) over (partition by earnmonth ,area ) "max",min(personincome) over (partition by earnmonth ,area ) "min",avg(personincome) over (partition by earnmonth ,area ) "avg",sum(personincome) over (partition by earnmonth ,area ) "sum" from earnings;
---------------------
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）
(1)rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
(2)dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
(3)row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
(4)ntile是要把查询得到的结果平均分为几组，如果不平均则分给第一组。
例如：
复制代码
create table s_score
( s_id number(6)
,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);
select
s_id
,score
,rank() over(order by score desc) rank --按照成绩排名，纯排名
,dense_rank() over(order by score desc) dense_rank --按照成绩排名，相同成绩排名一致
,row_number() over(order by score desc) row_number --按照成绩依次排名
,ntile(3) over (order by score desc) group_s --按照分数划分成绩梯队
from s_score;
复制代码
排名/排序的时候，有时候，我们会想到利用伪列row_num，利用row_num确实可以解决某些场景下的问题(但是相对也比较复杂)，而且有些场景下的问题却很难解决。
例:取成绩前三名，并且前三名含有并列的情况。通过上面例子，我们可以直观的看到，结果应该有5条记录：
复制代码
select
s_id
,score
,dense_rank
from (
select
s_id
,score
,rank() over(order by score desc) rank
,dense_rank() over(order by score desc) dense_rank
,row_number() over(order by score desc) row_number
from s_score
) t
where dense_rank <= 3;
S_ID SCORE DENSE_RANK
------- ------ ----------
3 99.00 1
1 98.00 2
5 98.00 2
4 98.00 2
6 80.00 3
复制代码
如果只是简单的想到去用rownum <= 3 得到的结果显然不可能是正确的。
组内的排名或者排序是经常遇到的一种场景。
例如，取每个销售部门内，销售业绩最好的前三名。取每个班级内成绩排名信息等等..
取每个班级内每门课成绩排名第一的同学信息：
复制代码
drop table S_SCORE;
create table S_SCORE
(
S_ID NUMBER(6),
CLASS_ID VARCHAR2(2),
COURSE VARCHAR2(20),
SCORE NUMBER(5,2)
);
INSERT INTO S_SCORE VALUES(1001,'A','MATH','67');
INSERT INTO S_SCORE VALUES(1004,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1002,'A','MATH','99');
INSERT INTO S_SCORE VALUES(1003,'A','MATH','55');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','70');
INSERT INTO S_SCORE VALUES(1001,'A','ORACLE','97');
INSERT INTO S_SCORE VALUES(1004,'B','ORACLE','48');
INSERT INTO S_SCORE VALUES(1002,'A','ORACLE','79');
INSERT INTO S_SCORE VALUES(1003,'A','ORACLE','65');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','82');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','78');
select
s_id
,class_id
,course
,score
,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE;
S_ID CLASS_ID COURSE SCORE DRK
------- -------- -------------------- ------- ----------
1002 A MATH 99.00 1
1001 A MATH 67.00 2
1003 A MATH 55.00 3
1001 A ORACLE 97.00 1
1002 A ORACLE 79.00 2
1003 A ORACLE 65.00 3
1004 B MATH 88.00 1
1001 B MATH 88.00 1
1001 B MATH 70.00 2
1001 B ORACLE 82.00 1
1001 B ORACLE 78.00 2
1004 B ORACLE 48.00 3
select
s_id
,class_id
,course
,score
from (
select
s_id
,class_id
,course
,score
,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
) t
where drk = 1;
S_ID CLASS_ID COURSE SCORE
------- -------- -------------------- -------
1002 A MATH 99.00
1001 A ORACLE 97.00
1004 B MATH 88.00
1001 B MATH 88.00
1001 B ORACLE 82.00
复制代码
rank()和dense_rank()用法相似，这里就不在举例说明了。可以将上面的例子中dense_rank()替换成rank()实现。
接下来,看一个使用row_number()的场景
例:查看每个部门最近一笔销售记录：
复制代码
select * from criss_sales order by dept_id,sale_date desc;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT
------- ----------- ---------- -----------
D01 2014/5/4 G02 80
D01 2014/4/30 G03 800
D01 2014/4/8 G01 200
D01 2014/3/4 G00 700
D02 2014/5/2 G03 900
D02 2014/4/27 G01 300
D02 2014/4/8 G02 100
D02 2014/3/6 G00 500
复制代码
即,我们希望得到这两条记录：
D01 2014/5/4 G02 80
D02 2014/5/2 G03 900
复制代码
select
dept_id
,sale_date
,goods_type
,sale_cnt
,row_number() over (partition by dept_id order by sale_date desc)
from criss_sales;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT ROW_NUMBER()OVER(PARTITIONBYDE
------- ----------- ---------- ----------- ------------------------------
D01 2014/5/4 G02 80 1
D01 2014/4/30 G03 800 2
D01 2014/4/8 G01 200 3
D01 2014/3/4 G00 700 4
D02 2014/5/2 G03 900 1
D02 2014/4/27 G01 300 2
D02 2014/4/8 G02 100 3
D02 2014/3/6 G00 500 4
select
dept_id
,sale_date
,goods_type
,sale_cnt
from (
select
dept_id
,sale_date
,goods_type
,sale_cnt
,row_number() over (partition by dept_id order by sale_date desc) rn
from criss_sales
) t
where rn = 1;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT
------- ----------- ---------- -----------
D01 2014/5/4 G02 80
D02 2014/5/2 G03 900
复制代码
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?
这时比较好的选择，就是使用ntile函数：
复制代码
select
dept_id
,sale_date
,goods_type
,sale_cnt
,ntile(3) over (order by sale_cnt desc nulls last) all_cmp
,ntile(3) over (partition by dept_id order by sale_cnt desc nulls last) all_dept
from criss_sales;
5218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
