Oracle分析函式使用總結
Oracle分析函式使用總結
使用評級函式
評級函式（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) DESC NULLS LAST) AS rank,
4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS 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 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,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 55678.38 1
3 11 46187.38 2
4 11 42178.38 3
2 11 16177.84 4
PRD_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行。
接下來我們再來看分析函式與我們上次學的ROLLUP、CUBE、GROUPING SETS的結合使用：
SELECT
prd_type_id,SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank
FROM all_sales
WHERE year=2003
GROUP BY ROLLUP(prd_type_id)
ORDER BY rank;
PRD_TYPE_ID SUM(AMOUNT) RANK
1972485.13 1 （注：RULLUP的總計排在了最前）
1 905081.84 2
3 478270.91 3
4 402751.16 4
2 186381.22 5
5 6
SELECT
prd_type_id,emp_id,SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank
FROM all_sales
WHERE year=2003
GROUP 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 27
PRD_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 29129.96 32
3 26 45130.11 26
3 478270.91 3
4 21 108326.96 18
PRD_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 36
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
5 26 36
5 36
21 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 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（）的使用，它得到的是銷量的累積分佈和百分比排名：
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_RANK
----------- ----------- ---------- ------------
1 905081.84 .4 .25
2 186381.22 1 1
3 478270.91 .6 .5
4 402751.16 .8 .75
5 .2 0
3）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 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 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_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
2 186381.22 4
3 478270.91 2
4 402751.16 3`
4）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 186381.22 5
3 478270.91 3
4 402751.16 4
5 1
這裡ROW-NUMBER（）函式就相當於RANK（）函式。
總結：在上面介紹的這些評級函式中其中RANK（）、DENSE-RANK（）、PERCENT-RANK（）函式是比較常用的（相對於其他幾個而言），因此我們最好要掌握而其他幾個大家只要知道了解就可以了。
反百分點函式的使用
PERCENTILE-DISC（X）函式與CUME-DIST相反，它在每一個分組中檢查累積分佈的數值，直到找到大於或等於X的值。
PERCENTILE-CONT（X）函式與PERCENT-RANK（）相反，在每一個分組中檢查百分比排名的值，直到
找到大於或等於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_DISC
--------------- ---------------
417855.11 402751.16
視窗函式
視窗函式主要用來計算一定的記錄範圍內、一定的值域內、或一段時間內的累積和及移動平均值等。之所以叫“視窗”因為處理結果中使用了一個滑動的查詢結果集範圍。
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 月累積銷量 --定義了視窗的起點和終點
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 1674926.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換為相應的月就可以了
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(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 151831.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 (ORDER 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.16 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.075
4.FIRST-VALUE（）和LAST-VALUE（）函式的使用：
下面這個例子是用FIRST-VALUE（）和LAST-VALUE（）來獲得前一個月和後一個月的銷量：
SQL> SELECT
2 month 月份,SUM(amount) AS 月總銷量,
3 FIRST_VALUE(SUM(amount)) 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 124951.36 154349.44 170296.16
7 170296.16 124951.36 212735.68
8 212735.68 170296.16 199609.68
9 199609.68 212735.68 264480.79
10 264480.79 199609.68 160221.98
11 160221.98 264480.79 137336.17
月份 月總銷量 前月銷量 後月銷量
---------- ---------- ---------- ----------
12 137336.17 160221.98 137336.17
已選擇12行。
還有一個例子是計算當前月與前、後各一個月的比率
SQL> SELECT
2 month 月份,SUM(amount) AS 月總銷量,
3 SUM(amount)/FIRST_VALUE(SUM(amount)) OVER
4 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 當月與前月比率,
5 SUM(amount)/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 1 .818755807
2 116671.6 1.22136538 .727796855
3 160307.92 1.37400978 .910846665
4 175998.8 1.09787963 1.14026199
5 154349.44 .876991434 1.23527619
6 124951.36 .809535558 .733729756
7 170296.16 1.36289961 .800505867
8 212735.68 1.24921008 1.06575833
9 199609.68 .93829902 .754722791
10 264480.79 1.3249898 1.65071478
11 160221.98 .605798175 1.16664081
月份 月總銷量 當月與前月比率 當月與後月比率
---------- ---------- -------------- --------------
12 137336.17 .857161858 1
已選擇12行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-490822/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 函式總結Oracle函式
- Oracle常用函式總結Oracle函式
- ORACLE常用結構和函式使用方法總結Oracle函式
- ORACLE 實用函式總結Oracle函式
- Oracle 分析函式的使用Oracle函式
- Oracle分析函式七——分析函式案例Oracle函式
- Emgucv使用中常用函式總結函式
- oracle 與 mysql 中的函式總結OracleMySql函式
- Oracle 中 Over() 函式學習總結Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- Oracle 分析函式使用介紹(轉)Oracle函式
- Oracle 分析函式Oracle函式
- Oracle分析函式Oracle函式
- Oracle中B-Tree、Bitmap和函式索引使用案例總結Oracle函式索引
- php函式總結PHP函式
- Oracle函式彙總Oracle函式
- oracle 分析函式(轉)Oracle函式
- oracle分析函式（一）Oracle函式
- Oracle常用分析函式Oracle函式
- Oracle分析函式{轉}Oracle函式
- Oracle分析函式-6Oracle函式
- Oracle分析函式-5Oracle函式
- Oracle分析函式-4Oracle函式
- Oracle分析函式-2Oracle函式
- Oracle分析函式-1Oracle函式
- Oracle分析函式-3Oracle函式
- Oracle分析函式與視窗函式Oracle函式
- phpRedis函式使用總結【分類詳細】PHPRedis函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 使用Oracle分析函式去除重複記錄Oracle函式
- Oracle 函式小結Oracle函式
- Oracle 10gR2分析函式彙總Oracle 10g函式
- mysql日期函式總結MySql函式
- PHP常用函式總結PHP函式
- php 常用函式總結PHP函式
- SQL Server函式總結SQLServer函式
- oracle 10g函式大全--分析函式Oracle 10g函式
- oracle常用函式彙總Oracle函式