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
分享到：
相关推荐
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
Oracle分析函数使用总结[定义].pdf
oracle 分析函数 总结 使用
Oracle分析函数使用的总结.doc
分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值...
6 Oracle开发专题之：分析函数总结 7 Oracle开发专题之：26个分析函数 8 分析函数简述">1 Oracle开发专题之：分析函数 OVER 2 Oracle开发专题之：分析函数 Rank Dense rank row number 3 Oracle开发专题之：分析...
分析函数是 Oracle 专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据行分组然后计算基于组...本文总结了若干分析函数的使用方式，供大家学习参考，有问题可以随时私信沟通。十分乐意和大家学习交流。谢谢！
目录 Oracle开发专题之：分析函数(OVER) Oracle开发专题之：分析函数2(Rank, ...报表函数 Oracle开发专题之：分析函数总结 Oracle开发专题之：26个分析函数 PLSQL开发笔记和小结 分析函数简述
Oracle分析函数基本概念和语法总结及Regexp_***(regexp_substr,regexp_instr, regexp_like, regexp_replace, regexp_count)用法
oracle分析函数,总结的挺全面,以后备用
oracle connect by 和 分析函数总结.doc
分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile) 窗口函数 报表函数 ...分析函数总结 26个分析函数 PLSQL开发笔记和小结 分析函数简述
数据统计及报表展示需要的必须要了解的Oracle的分析函数，功能强大、效率高、上手简单，个人的总结分享，希望能有所帮助···
包含有3个文档：（1）oracle转mysql问题解决。（主要分事务处理，游标处理，存储过程方法调用，数组处理，异常处理等。） （2）oracle与mysql区别比较。 （主要包含：语法及结构区别，函数区别，数据类型区别等。） ...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
本文是对Oracle中分析函数做的总结，主要总结了前几篇关于分析函数、窗口函数、报表函数文章的内容，需要的朋友可以参考下。
3.6 Oracle常用函数 3.6.1 字符类函数 3.6.2 数字类函数 3.6.3 日期类函数 3.6.4 转换类函数 3.6.5 聚集类函数 第4章 Oracle PL/SQL语言及编程 4.1 PL/SQL简介 4.1.1 PL/SQL的基本结构 4.1.2 ...