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分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时极为有用的一套工具,主要用于在线分析处理(OLAP)场景。这类函数可以在多个级别上进行数据聚合,并支持复杂的排序、分组...
其中,Oracle的分析函数是其强大的特性之一,它允许用户在单个SQL查询中执行复杂的分析操作,而无需使用子查询或者自连接。这篇文档将深入探讨Oracle中的分析函数,帮助你更好地理解和利用这一功能。 一、什么是...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
Oracle分析函数包括许多内置函数,如AVG、COUNT、MAX、MIN、SUM等,但作为分析函数使用时,它们会在OVER()子句中指定特定的行为。例如,AVG()分析函数可以计算每个分组或窗口内的平均值,而不只是整个数据集的平均值...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它自Oracle 8.1.6版本开始引入,并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算,并且不同于...
Oracle分析函数是数据库查询中非常强大的工具,它们用于在数据集上执行聚合操作,并返回每个行的上下文信息。在Oracle数据库中,分析函数能够帮助我们处理复杂的分组和排序需求,尤其在报告和数据分析中非常有用。...
分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,旨在处理复杂的查询需求,特别是当需要对查询结果进行进一步分析时。通过使用分析函数,可以在单次查询中执行复杂的聚合操作,无需进行...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且能够返回多个结果行。这与传统的聚合函数(如`SUM`、`COUNT`等)形成鲜明对比,后者通常只针对...
6 Oracle开发专题之:分析函数总结 7 Oracle开发专题之:26个分析函数 8 分析函数简述">1 Oracle开发专题之:分析函数 OVER 2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析...
### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数,这类函数主要用于计算基于组的聚合值,并且与传统的聚合函数不同的是,分析函数可以针对每个组返回多行结果...
Oracle分析函数是数据库查询中强大的工具,它们允许对数据集进行复杂的聚合操作,提供更深入的洞察力。本文将详细介绍Oracle分析函数中的评级函数,包括RANK()、DENSE_RANK()、CUME_DIST()、PERCENT_RANK()和NTILE()...
Oracle分析函数是数据库管理系统Oracle中的一种高级查询工具,它们用于处理行集,计算基于特定窗口内的数据的聚合值。分析函数的主要特点是返回的结果不仅仅是一行,而是多行,这与传统的聚合函数(如SUM, AVG等)...
Oracle数据库是一个广泛使用的关系型数据库管理系统,它提供了丰富的内置函数来帮助用户处理和操作数据。以下是对标题和描述中提到的Oracle常用函数的详细总结: 1. ASCII 函数:这个函数返回一个字符对应的ASCII码...
### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时进行高级数据分析的强大工具,主要用于在线分析处理(OLAP)场景...希望本文能够帮助您更好地掌握Oracle分析函数的使用方法。