Oracle分析函数使用总结.docx
《Oracle分析函数使用总结.docx》由会员分享,可在线阅读,更多相关《Oracle分析函数使用总结.docx(17页珍藏版)》请在冰豆网上搜索。
![Oracle分析函数使用总结.docx](https://file1.bdocx.com/fileroot1/2022-10/27/dab43b08-69df-49f6-9da6-398680368e2c/dab43b08-69df-49f6-9da6-398680368e2c1.gif)
Oracle分析函数使用总结
Oracle分析函数使用总结
1.使用评级函数
评级函数(rankingfunction)用于计算等级、百分点、n分片等等,下面是几个常用到的评级函数:
RANK():
返回数据项在分组中的排名。
特点:
在排名相等的情况下会在名次中留下空位
DENSE_RANK():
与RANK不同的是它在排名相等的情况下不会在名次中留下空位
CUME_DIST():
返回特定值相对于一组值的位置:
他是“cumulativedistribution”(累积分布)的简写
PERCENT_RANK():
返回某个值相对于一组值的百分比排名
NTILE():
返回n分片后的值,比如三分片、四分片等等
ROW_NUMBER():
为每一条分组纪录返回一个数字
下面我们分别举例来说明这些函数的使用
1)RANK()与DENSE-RANK()
首先显示下我们的源表数据的结构及部分数据:
SQL>descall_sales;
名称是否为空?
类型
------------------------------------------------------------
YEARNOTNULLNUMBER(38)
MONTHNOTNULLNUMBER(38)
PRD_TYPE_IDNOTNULLNUMBER(38)
EMP_IDNOTNULLNUMBER(38)
AMOUNTNUMBER(8,2)
SQL>select*fromall_saleswhererownum<11;
YEARMONTHPRD_TYPE_IDEMP_IDAMOUNT
---------------------------------------------------
2003112110034.84
2003212115144.65
2003312120137.83
2003412125057.45
2003512117214.56
2003612115564.64
2003712112654.84
2003812117434.82
2003912119854.57
20031012121754.19
已选择10行。
好接下来我们将举例来说明上述函数的使用:
首先是RANK()与DENSE-RANK()的使用:
SQL>select
2prd_type_id,sum(amount),
3RANK()OVER(ORDERBYSUM(amount)DESC)ASrank,
4DENSE_RANK()OVER(ORDERBYSUM(amount)DESC)ASdense_rank
5fromall_sales
6whereyear=2003
7groupbyprd_type_id
8orderbyrank;
PRD_TYPE_IDSUM(AMOUNT)RANKDENSE_RANK
------------------------------------------
511
1905081.8422
3478270.9133
4402751.1644
2186381.2255
注意:
这里PRD_TYPE_ID列为5的SUM(AMOUNT)的值为空,RANK()和DENSE-RANK在这一行的返回值为1。
因为默认状态下RANK()和DENSE-RANK()在递减排序中将空值指定为最高排名1,而在递增排序中则把它指定为最低排名。
这里还有一个问题就是我们的例子中没有SUM(AMOUNT)相等的值,如果有的话RANK与DENSE-RANK将表现出区别比如上面的例子如果PRD_TYPE_ID为4的SUM(AMOUNT)的值也为:
478270.91的话,那么上面语句的输出则为:
PRD_TYPE_IDSUM(AMOUNT)RANKDENSE_RANK
------------------------------------------
511
1905081.8422
3478270.9133
4478270.9133
2186381.2254
此外这里还有两个参数来限制空值的排序即:
NULLSFIRST和NULLSLAST
我们还以上面的例子来看:
SQL>select
2prd_type_id,sum(amount),
3RANK()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASrank,
4DENSE_RANK()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASdense_rank
5fromall_sales
6whereyear=2003
7groupbyprd_type_id
8*orderbyrank
PRD_TYPE_IDSUM(AMOUNT)RANKDENSE_RANK
------------------------------------------
1905081.8411
3478270.9122
4402751.1633
2186381.2244
555
可以看出刚才我们不使用NULLSLAST时PRD_TYPE_ID为5的空值的排序位于第一,现在则位于第五。
接下来来看分析函数与PARTITIONBY子句的结合使用:
当需要把分组划分为子分组时,那么我们便可以结合PRATITIONBY子句和分析函数同时使用。
如下例根据月份划分销量:
SQL>select
2prd_type_id,month,SUM(amount),
3RANK()OVER(PARTITIONBYmonthORDERBYSUM(amount)DESC)ASrank
4fromall_sales
5whereyear=2003
6andamountISNOTNULL
7GROUPBYprd_type_id,month
8*ORDERBYmonth,rank
PRD_TYPE_IDMONTHSUM(AMOUNT)RANK
------------------------------------------
1138909.041
3124909.042
4117398.433
2114309.044
1270567.91
4217267.92
3215467.93
2213367.94
1391826.981
4331026.982
3320626.983
PRD_TYPE_IDMONTHSUM(AMOUNT)RANK
------------------------------------------
2316826.984
14120344.71
3423844.72
4416144.73
2415664.74
1597287.361
4520087.362
3518687.363
2518287.364
1657387.841
4633087.842
PRD_TYPE_IDMONTHSUM(AMOUNT)RANK
------------------------------------------
3619887.843
2614587.844
3781589.041
1760929.042
2715689.043
4712089.044
1875608.921
3862408.922
4858408.923
2816308.924
1985027.421
PRD_TYPE_IDMONTHSUM(AMOUNT)RANK
------------------------------------------
4949327.422
3946127.423
2919127.424
110105305.221
41075325.142
31070325.293
21013525.144
11155678.381
31146187.382
41142178.383
21116177.844
PRD_TYPE_IDMONTHSUM(AMOUNT)RANK
------------------------------------------
31248209.041
11246209.042
41230409.053
21212509.044
已选择48行。
接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPINGSETS的结合使用:
SELECT
prd_type_id,SUM(amount),
RANK()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASrank
FROMall_sales
WHEREyear=2003
GROUPBYROLLUP(prd_type_id)
ORDERBYrank;
PRD_TYPE_IDSUM(AMOUNT)RANK
1972485.131(注:
RULLUP的总计排在了最前)
1905081.842
3478270.913
4402751.164
2186381.225
56
SELECT
prd_type_id,emp_id,SUM(amount),
RANK()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASrank
FROMall_sales
WHEREyear=2003
GROUPBYCUBE(prd_type_id,emp_id)
ORDERBYprd_type_id,emp_id;
PRD_TYPE_IDEMP_IDSUM(AMOUNT)RANK
------------------------------------------
121197916.9612
122214216.9610
12398896.9619
124207216.9611
12593416.9621
12693417.0420
1905081.842
22120426.9633
22219826.9634
22319726.9635
22443866.9627
PRD_TYPE_IDEMP_IDSUM(AMOUNT)RANK
------------------------------------------
22532266.9631
226