oracle 怎么测试函数,Oracle分析函数使用总结
赞
踩
1.使用评级函数
评级函数(ranking function)用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：
RANK()：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位
DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位
CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写
PERCENT_RANK()：返回某个值相对于一组值的百分比排名
NTILE():返回n分片后的值，比如三分片、四分片等等
ROW_NUMBER():为每一条分组纪录返回一个数字
下面我们分别举例来说明这些函数的使用
1)RANK()与DENSE-RANK()
首先显示下我们的源表数据的结构及部分数据：
SQL> desc all_sales;
名称 是否为空?类型
----------------------------------------- -------- -----------
YEAR NOT NULL NUMBER(38)
MONTH NOT NULL NUMBER(38)
PRD_TYPE_ID NOT NULL NUMBER(38)
EMP_ID NOT NULL NUMBER(38)
AMOUNT NUMBER(8,2)
SQL> select * from all_sales where rownum<11;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2003 1 1 21 10034.84
2003 2 1 21 15144.65
2003 3 1 21 20137.83
2003 4 1 21 25057.45
2003 5 1 21 17214.56
2003 6 1 21 15564.64
2003 7 1 21 12654.84
2003 8 1 21 17434.82
2003 9 1 21 19854.57
2003 10 1 21 21754.19
已选择10行。
好接下来我们将举例来说明上述函数的使用：首先是RANK()与DENSE-RANK()的使用：
SQL> select
2 prd_type_id,sum(amount),
3 RANK() OVER(ORDER BY SUM(amount) DESC) AS rank,
4 DENSE_RANK() OVER(ORDER BY SUM(amount) DESC) AS dense_rank
5 from all_sales
6 where year=2003
7 group by prd_type_id
8 order by rank;
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
5 1 1
1 905081.84 2 2
3 478270.91 3 3
4 402751.16 4 4
2 186381.22 5 5
注意：这里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_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
5 1 1
1 905081.84 2 2
3 478270.91 3 3
4 478270.91 3 3
2 186381.22 5 4
此外这里还有两个参数来限制空值的排序即：NULLS FIRST和NULLS LAST
我们还以上面的例子来看：
SQL> select
2 prd_type_id,sum(amount),
3 RANK() OVER (ORDER BY SUM(amount) DESCNULLS LAST) AS rank,
4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESCNULLS LAST) AS dense_rank
5 from all_sales
6 where year=2003
7 group by prd_type_id
8* order by rank
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
1 905081.84 1 1
3 478270.91 2 2
4 402751.16 3 3
2 186381.22 4 4
5 5 5
可以看出刚才我们不使用NULLS LAST时PRD_TYPE_ID为5的空值的排序位于第一，现在则位于第五。
接下来来看分析函数与PARTITION BY子句的结合使用：
当需要把分组划分为子分组时，那么我们便可以结合PRATITION BY子句和分析函数同时使用。如下例根据月份划分销量：
SQL> select
2 prd_type_id,month,SUM(amount),
3 RANK() OVER (PARTITION BYmonth ORDER BY SUM(amount) DESC) AS rank
4 from all_sales
5 where year=2003
6 and amount IS NOT NULL
7 GROUP BY prd_type_id,month
8* ORDER BY month,rank
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 38909.04 1
3 1 24909.04 2
4 1 17398.43 3
2 1 14309.04 4
1 2 70567.9 1
4 2 17267.9 2
3 2 15467.9 3
2 2 13367.9 4
1 3 91826.98 1
4 3 31026.98 2
3 3 20626.98 3
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
2 3 16826.98 4
1 4 120344.7 1
3 4 23844.7 2
4 4 16144.7 3
2 4 15664.7 4
1 5 97287.36 1
4 5 20087.36 2
3 5 18687.36 3
2 5 18287.36 4
1 6 57387.84 1
4 6 33087.84 2
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
3 6 19887.84 3
2 6 14587.84 4
3 7 81589.04 1
1 7 60929.04 2
2 7 15689.04 3
4 7 12089.04 4
1 8 75608.92 1
3 8 62408.92 2
4 8 58408.92 3
2 8 16308.92 4
1 9 85027.42 1
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
4 9 49327.42 2
3 9 46127.42 3
2 9 19127.42 4
1 10 105305.22 1
4 10 75325.14 2
3 10 70325.29 3
2 10 13525.14 4
1 11
