Oracle分析函数使用总结.docx

1、Oracle分析函数使用总结 Oracle分析函数使用总结1. 使用评级函数评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写PERCENT_RANK()：返回某个值相对于一组值的百分比排名NTILE():返回n分片后的值，比如三分片、四分片等等ROW

2、_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 select 2

3、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 4

4、02751.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)

5、 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) DESC NULLS LAST) AS rank, 4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS

6、LAST) AS dense_rank 5 from all_sales 6 where year=2003 7 group by prd_type_id 8* order by rankPRD_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子句的结合使用：当需要把分组

7、划分为子分组时，那么我们便可以结合PRATITION BY子句和分析函数同时使用。如下例根据月份划分销量：SQL select 2 prd_type_id,month,SUM(amount), 3 RANK() OVER (PARTITION BY month 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,rankPRD_TYPE_ID MONTH SUM(A

8、MOUNT) 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 3PRD_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 972

9、87.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 2PRD_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 1PRD_TYPE_ID

10、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 55678.38 1 3 11 46187.38 2 4 11 42178.38 3 2 11 16177.84 4PRD_TYPE_ID MONTH SUM(AMOUNT) RANK- - - - 3 12 48209.04 1 1 12 46209.04 2 4 12 30409.05 3 2 12 12

11、509.04 4已选择48行。接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPING SETS的结合使用：SELECT prd_type_id,SUM(amount), RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rankFROM all_salesWHERE year=2003GROUP BY ROLLUP(prd_type_id)ORDER BY rank;PRD_TYPE_ID SUM(AMOUNT) RANK 1972485.13 1 （注：RULLUP的总计排在了最前）1 905081.84 23

12、 478270.91 34 402751.16 42 186381.22 55 6SELECT prd_type_id,emp_id,SUM(amount), RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rankFROM all_salesWHERE year=2003GROUP BY CUBE(prd_type_id,emp_id)ORDER BY prd_type_id,emp_id;PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK- - - - 1 21 197916.96 12 1 22 214216.96 10 1 23 98896.96 19 1 24 207216.96 11 1 25 93416.96 21 1 26 93417.04 20 1 905081.84 2 2 21 20426.96 33 2 22 19826.96 34 2 23 19726.96 35 2 24 43866.96 27PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK- - - - 2 25 32266.96 31 2 26