Oracle分析函数使用的总结[学练结合]

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

2、分组纪录返回一个数字 下面我们分别举例来说明这些函数的使用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 prd_type_id,s

3、um(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

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_R

5、ANK- - - - 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 LAST) AS dens

6、e_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(AMOUNT) RANK-

8、- - - 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 97287.36 1 4 5 2

9、0087.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 MONTH SUM(AMO

10、UNT) 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 12509.04 4已选择48

11、行。接下来我们再来看分析函数与我们上次学的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_IDSUM(AMOUNT)RANK1972485.131 （注：RULLUP的总计排在了最前）1905081.8423478270.9134402751.1

12、642186381.2255 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

13、 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 50266.42 24 2 186381.22 14 3 21 140326.96 15 3 22 116826.96 16 3 23 112026.96 17 3 24 34829.96 29 3 25

14、29129.96 32 3 26 45130.11 26 3 478270.91 3 4 21 108326.96 18PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK- - - - 4 22 81426.96 23 4 23 92426.96 22 4 24 47456.96 25 4 25 33156.96 30 4 26 39956.36 28 4 402751.16 6 5 21 36 5 22 36 5 23 36 5 24 36 5 25 36PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK- - - - 5 26 36 5 36 21

15、466997.84 4 22 432297.84 5 23 323077.84 8 24 333370.84 7 25 187970.84 13 26 228769.93 9 1972485.13 1已选择42行。SQL SELECT 2 prd_type_id,emp_id,SUM(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank 4 FROM all_sales 5 WHERE year=2003 6 GROUP BY GROUPING SETS(prd_type_id,emp_id) 7 ORDER

16、 BY prd_type_id,emp_id;PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK- - - - 1 905081.84 1 2 186381.22 10 3 478270.91 2 4 402751.16 5 5 11 21 466997.84 3 22 432297.84 4 23 323077.84 7 24 333370.84 6 25 187970.84 9 26 228769.93 8已选择11行。2）CUME-DIST（）和PERCENT-RANK（）函数下面这个例子说明了CUME-DIST（）与PERCENT-RANK（）的使用，它得到的是销量

17、的累积分布和百分比排名：SQL SELECT 2 prd_type_id,SUM(amount), 3 CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS cume_dist, 4 PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank 5 FROM all_sales 6 WHERE year=2003 7 GROUP BY prd_type_id 8 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) CUME_DIST PERCENT_

18、RANK- - - - 1 905081.84 .4 .25 2 186381.22 1 1 3 478270.91 .6 .5 4 402751.16 .8 .75 5 .2 03）NTILE（）函数的使用前面我们已经介绍了这个函数的作用就是把记录结果集分成N部分的意思，这个函数的参数为NTILE（buckets），这个bucket参数指定了分片的片数，下面我们看例子来说明SQL SELECT 2 prd_type_id,SUM(amount), 3 NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile 4 FROM all_sales 5 W

19、HERE year=2003 6 AND amount IS NOT NULL 7 GROUP BY prd_type_id 8 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) NTILE- - - 1 905081.84 1 2 186381.22 2 3 478270.91 1 4 402751.16 2注意这里的N为2，因此分成了下面的1，2两片SQL SELECT 2 prd_type_id,SUM(amount), 3 NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS ntile 4 FROM all_

20、sales 5 WHERE year=2003 6 AND amount IS NOT NULL 7 GROUP BY prd_type_id 8 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) NTILE- - - 1 905081.84 1 2 186381.22 3 3 478270.91 1 4 402751.16 2注意这里的N为3，因此分成了下面的1，2，3三片，这里我的看法是当分片不均时，都是向上最加（即有两个1片）当N=4时就与RANK相同了PRD_TYPE_ID SUM(AMOUNT) NTILE- - - 1 905081.84 1

21、 2 186381.22 4 3 478270.91 2 4 402751.16 34）ROW-NUMBER（）函数SQL SELECT 2 prd_type_id,SUM(amount), 3 ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number 4 FROM all_sales 5 WHERE year=2003 6 GROUP BY prd_type_id 7 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER- - - 1 905081.84 2 2 18638

22、1.22 5 3 478270.91 3 4 402751.16 4 5 1这里ROW-NUMBER（）函数就相当于RANK（）函数。总结：在上面介绍的这些评级函数中其中RANK（）、DENSE-RANK（）、PERCENT-RANK（）函数是比较常用的（相对于其他几个而言），因此我们最好要掌握而其他几个大家只要知道了解就可以了。2. 反百分点函数的使用PERCENTILE-DISC（X）函数与CUME-DIST相反，它在每一个分组中检查累积分布的数值，直到找到大于或等于X的值。PERCENTILE-CONT（X）函数与PERCENT-RANK（）相反，在每一个分组中检查百分比排名的值，直到找

23、到大于或等于X的值。下面我们来看个例子获取百分点大于等于0.6的销售总量：SQL SELECT 2 PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_cont, 3 PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_disc 4 FROM all_sales 5 WHERE year=2003 6 GROUP BY prd_type_id;PERCENTILE_CONT PERCENTILE_

24、DISC- - 417855.11 402751.163. 窗口函数窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。1）.计算累积和下面这个例子是计算出2003年从1月到12月的累积销量。SQL SELECT 2 month 月份,SUM(amount) AS 月总销量, 3 SUM(SUM(amount) OVER 4 (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 月累积销量 -定义了窗口的起点和终点

25、 5 FROM all_sales 6 WHERE year=2003 7 GROUP BY month 8 ORDER BY month; 月份 月总销量 月累积销量- - - 1 95525.55 95525.55 2 116671.6 212197.15 3 160307.92 372505.07 4 175998.8 548503.87 5 154349.44 702853.31 6 124951.36 827804.67 7 170296.16 998100.83 8 212735.68 1210836.51 9 199609.68 1410446.19 10 264480.79 1

26、674926.98 11 160221.98 1835148.96 月份 月总销量 月累积销量- - - 12 137336.17 1972485.13已选择12行。那如果是计算6月到12月的累积销量呢！SQL SELECT 2 month 月份,SUM(amount) AS 月总销量, 3 SUM(SUM(amount) OVER 4 (ORDER BY month ROWS UNBOUNDED PRECEDING) AS 月累积销量 5 FROM all_sales 6 WHERE year=2003 7 AND month BETWEEN 6 AND 12 -6和12换为相应的月就可以了

27、 8 GROUP BY month 9 ORDER BY month; 月份 月总销量 月累积销量- - - 6 124951.36 124951.36 7 170296.16 295247.52 8 212735.68 507983.2 9 199609.68 707592.88 10 264480.79 972073.67 11 160221.98 1132295.65 12 137336.17 1269631.82已选择7行。2）.计算移动平均值计算本月与前三个月之间销量的移动平均值SQL SELECT 2 month 月份,SUM(amount) AS 月总销量, 3 AVG(SUM(

28、amount) OVER 4 (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS 三月平均累积销量 5 FROM all_sales 6 WHERE year=2003 7 GROUP BY month 8 ORDER BY month; 月份 月总销量 三月平均累积销量- - - 1 95525.55 95525.55 2 116671.6 106098.575 -前两月的平均销量 3 160307.92 124168.357 -三月 4 175998.8 137125.968 -本月加前三月 5 154349.44 15

29、1831.94 6 124951.36 153901.88 7 170296.16 156398.94 8 212735.68 165583.16 9 199609.68 176898.22 10 264480.79 211780.578 11 160221.98 209262.033 月份 月总销量 三月平均累积销量- - - 12 137336.17 190412.155已选择12行。3）.计算中心平均值计算当前月份前、后各一个月内的销量移动平均值：SQL SELECT 2 month 月份,SUM(amount) AS 月总销量, 3 AVG(SUM(amount) OVER 4 (OR

30、DER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 平均累积销量 5 FROM all_sales 6 WHERE year=2003 7 GROUP BY month 8 ORDER BY month; 月份 月总销量 平均累积销量- - - 1 95525.55 106098.575 2 116671.6 124168.357 3 160307.92 150992.773 4 175998.8 163552.053 5 154349.44 151766.533 6 124951.36 149865.653 7 170296.1

31、6 169327.733 8 212735.68 194213.84 9 199609.68 225608.717 10 264480.79 208104.15 11 160221.98 187346.313 月份 月总销量 平均累积销量- - - 12 137336.17 148779.0754.FIRST-VALUE（）和LAST-VALUE（）函数的使用：下面这个例子是用FIRST-VALUE（）和LAST-VALUE（）来获得前一个月和后一个月的销量：SQL SELECT 2 month 月份,SUM(amount) AS 月总销量, 3 FIRST_VALUE(SUM(amount)

32、 OVER 4 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 前月销量, 5 LAST_VALUE(SUM(amount) OVER 6 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 后月销量 7 FROM all_sales 8 WHERE year=2003 9 GROUP BY month 10 ORDER BY month; 月份 月总销量 前月销量 后月销量- - - - 1 95525.55 95525.55 116671.6 2 116671.6 95525.55 160307.92 3 160307.92 116671.6 175998.8 4 175998.8 160307.92 154349.44 5 154349.44 175998.8 124951.36 6 1