oracle 分析函数使用,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-