Oracle分析函數(shù)使用的總結(jié)[學(xué)練結(jié)合]

1、 Oracle分析函數(shù)使用總結(jié)1. 使用評(píng)級(jí)函數(shù)評(píng)級(jí)函數(shù)（ranking function）用于計(jì)算等級(jí)、百分點(diǎn)、n分片等等，下面是幾個(gè)常用到的評(píng)級(jí)函數(shù)：RANK（）：返回?cái)?shù)據(jù)項(xiàng)在分組中的排名。特點(diǎn)：在排名相等的情況下會(huì)在名次中留下空位DENSE_RANK():與RANK不同的是它在排名相等的情況下不會(huì)在名次中留下空位CUME_DIST()：返回特定值相對(duì)于一組值的位置：他是“cumulative distribution”(累積分布)的簡(jiǎn)寫(xiě)PERCENT_RANK()：返回某個(gè)值相對(duì)于一組值的百分比排名NTILE():返回n分片后的值，比如三分片、四分片等等ROW_NUMBER():為每一條

2、分組紀(jì)錄返回一個(gè)數(shù)字 下面我們分別舉例來(lái)說(shuō)明這些函數(shù)的使用1）RANK（）與DENSE-RANK（）首先顯示下我們的源表數(shù)據(jù)的結(jié)構(gòu)及部分?jǐn)?shù)據(jù)：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。因?yàn)槟J(rèn)狀態(tài)下RANK（）和DENSE-RANK（）在遞減排序中將空值指定為最高排名1，而在遞增排序中則把它指定為最低排名。這里還有一個(gè)問(wèn)題就是我們的例子中沒(méi)有SUM(AMOUNT)相等的值，如果有的話RANK與DENSE-RANK將表現(xiàn)出區(qū)別比如上面的例子如果PRD_TYPE_ID為4的SUM（AMOUNT）的值也為：478270.91的話，那么上面語(yǔ)句的輸出則為：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此外這里還有兩個(gè)參數(shù)來(lái)限制空值的排序即：NULLS FIRST和NULLS LAST我們還以上面的例子來(lái)看：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時(shí)PRD_TYPE_ID為5的空值的排序位于第一，現(xiàn)在則位于第五。接下來(lái)來(lái)看分析函數(shù)與PARTITION BY子句的結(jié)合使用：當(dāng)需要把分組劃分為子分組時(shí)，那么我們便

7、可以結(jié)合PRATITION BY子句和分析函數(shù)同時(shí)使用。如下例根據(jù)月份劃分銷量：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、行。接下來(lái)我們?cè)賮?lái)看分析函數(shù)與我們上次學(xué)的ROLLUP、CUBE、GROUPING SETS的結(jié)合使用：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的總計(jì)排在了最前）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（）函數(shù)下面這個(gè)例子說(shuō)明了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（）函數(shù)的使用前面我們已經(jīng)介紹了這個(gè)函數(shù)的作用就是把記錄結(jié)果集分成N部分的意思，這個(gè)函數(shù)的參數(shù)為NTILE（buckets），這個(gè)bucket參數(shù)指定了分片的片數(shù)，下面我們看例子來(lái)說(shuō)明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三片，這里我的看法是當(dāng)分片不均時(shí)，都是向上最加（即有兩個(gè)1片）當(dāng)N=4時(shí)就與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（）函數(shù)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（）函數(shù)就相當(dāng)于RANK（）函數(shù)?？偨Y(jié)：在上面介紹的這些評(píng)級(jí)函數(shù)中其中RANK（）、DENSE-RANK（）、PERCENT-RANK（）函數(shù)是比較常用的（相對(duì)于其他幾個(gè)而言），因此我們最好要掌握而其他幾個(gè)大家只要知道了解就可以了。2. 反百分點(diǎn)函數(shù)的使用PERCENTILE-DISC（X）函數(shù)與CUME-DIST相反，它在每一個(gè)分組中檢查累積分布的數(shù)值，直到找到大于或等于X的值。PERCENTILE-CONT（X）函數(shù)與PERCENT-RANK（）相反，在每一個(gè)分組中檢查百分比排名的值，直到找

23、到大于或等于X的值。下面我們來(lái)看個(gè)例子獲取百分點(diǎn)大于等于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. 窗口函數(shù)窗口函數(shù)主要用來(lái)計(jì)算一定的記錄范圍內(nèi)、一定的值域內(nèi)、或一段時(shí)間內(nèi)的累積和及移動(dòng)平均值等。之所以叫“窗口”因?yàn)樘幚斫Y(jié)果中使用了一個(gè)滑動(dòng)的查詢結(jié)果集范圍。1）.計(jì)算累積和下面這個(gè)例子是計(jì)算出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 月累積銷量 -定義了窗口的起點(diǎn)和終點(diǎn)

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行。那如果是計(jì)算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換為相應(yīng)的月就可以了

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）.計(jì)算移動(dòng)平均值計(jì)算本月與前三個(gè)月之間銷量的移動(dòng)平均值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）.計(jì)算中心平均值計(jì)算當(dāng)前月份前、后各一個(gè)月內(nèi)的銷量移動(dòng)平均值：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（）函數(shù)的使用：下面這個(gè)例子是用FIRST-VALUE（）和LAST-VALUE（）來(lái)獲得前一個(gè)月和后一個(gè)月的銷量：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