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) 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?? 22876