Oracle分析函数

分析函数通常用在一个比较复杂的需求的时候

比如每个专业成绩排名前十的同学，又比如按区域查找上一年度订单总额占区域订单总额20%以上的客户

与平常遇到的普通查询相比，这些需求：

①需要对同样的数据进行不同级别的聚合操作

②需要在表内将多条数据和同一条数据进行多次的比较

③需要在排序完的结果集上进行额外的过滤操作

分析函数的语法：

语法 FUNCTION_NAME(<参数>,…) OVER ( <order by 表达式  > )</order by 表达式 

看起来有少许复杂 
下面我们便来介绍几种常用的分析函数

分析函数排名

下列几个分析函数是用来进行数据进行排名的，它们的区别：

dense_rank()

有重复的数字不跳着排列,比如，两个并列第一，后面是第二名

? rank()

有重复的数字跳着排列,比如，两上并列第一，后面是第三名

? row_number()

不管重复

没有使用partition by

SQL> select major,name,score,dense_rank() over(order by score desc) dr, rank() over(order by score desc) r, row_number() over(order by score desc) rn from text;

首先可以明确看出,dense_rank()，rank()，row_number()的区别

表中分数为85和99的各有两个相同的人

排名时候，dense_rank()将两个99的同时定位第1名，两个85的定位第2名

rank()将两个85的同时定位第5名，因为rank() 认为99有两个人，所以接下来应该是第3名了，两个85的定位第3名。ps：若是这里99有三个人，那么85就应该是第4名了。

row_number()的排名不会有重复的，即使分数相同，row_number()会将先找到的99排名为第一，接下来找到的另一个99为第二。

使用partition by

SQL> select major,name,score,dense_rank() over(partition by major order by score desc) dr, rank() over(partition by major order by score desc) r,row_number() over(partition by major order by score desc) rn from text;

我们可以看到若是使用了partition by major，根据每个专业自己排名。就是成绩只跟同个专业的人比，不跟其他专业的人比了。

sum()分析函数

partition by和order by 都不使用

SQL> select major,name,score,sum(score) over() count from text;

COUNT列只是把所有的行的score列结果加起来

只是用了order by

SQL> select major,name,score,sum(score) over(order by score) count from text;

COUNT列是使用累加的形式进行统计。但是若是有两行的数是相同的，会同时加上。

partition by和order by都使用

SQL> select major,name,score,sum(score) over(partition by major order by score) count from text;

8 rows selected.

COUNT列经过按自己专业从小到大排序，然后对自己专业的成绩进行累加统计。

只使用partition by

SQL>  select major,name,score,sum(score) over(partition by major) count from text;

8 rows selected.

将数据按major区分开来，然后COUNT是每个专业成绩总和

也可以用分析函数rand()来计算成绩百分比

SQL> select major,name,score,sum(score) over() count,100*round(score/sum(score) over(),5) percent from text;

8 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31386161/viewspace-2131408/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31386161/viewspace-2131408/