Chinaunix首页 | 论坛 | 博客
  • 博客访问: 748551
  • 博文数量: 105
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3099
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(105)

文章存档

2020年(70)

2014年(3)

2013年(32)

分类: Oracle

2020-06-22 15:33:55

接PART3:http://blog.chinaunix.net/uid-7655508-id-5834821.html

1.3 分析函数的使用

1.3.1 分析函数概览

 

我们浏览下所有的分析函数,其中带(*)的表示可以带window子句。其中黄色的表示常用分析函数。

--可以带window

    AVG  *

    CORR *

    COVAR_POP *

    COVAR_SAMP *

    COUNT *

    FIRST_VALUE *

    LAST_VALUE *

    MAX *

    MIN *

    REGR(Linear Regressions) Functions *

    STDDEV *

    STDDEV_POP *

    STDDEV_SAMP *

    SUM *

    VAR_POP *

    VAR_SAMP *

    VARIANCE *

--不可以带window

   CUME_DIST

    DENSE_RANK

    FIRST

    LAST

    LAG

    LEAD

    NTILE

    PERCENT_RANK

    PERCENTILE_COUNT

    PERCENTILE_DISC

    RANK

    ROW_NUMBER

    RATIO_TO_REPORT

常用分析函数使用说明

  分析函数可以分为四类:排名函数,聚合函数,行比较函数,统计函数。
  
很多分析函数也可以当作聚合函数使用,这里我们只讨论当作分析函数的使用方法,聚合函数可以参考oracle sql指南,这里不另作说明。另外oracle还支持user-defined aggregate function,可以使用分析函数的语法,这样可以自定义扩展分析函数的功能,本章也会讨论如何写子句的分析函数。

1.3.2排名函数(Ranking)

  排名函数包括ROW_NUMBER, RANK ,DENSE_RANK, PERCENT_RANK, CUME_DIST和NTILE。(常用的是黄色标志的),排名函数的最大用途就是做TOP-N分析和BOTTOM-N分析,常用于分页查询,当然row_number伪列也类似

1.3.2.1 ROW_NUMBER, RANK ,DENSE_RANK
  
其中row_number只能用于分析函数,rank和dense_rank可以用于组函数和分析函数。排名函数都没有windows子句,3个分析函数必须使用order by,当然如果不要排序,也可以写order by null,order by 常量或多个常量,直接写NULL就行了,事实还会做一定sort动作的,只不过相当于增加几个伪列,都是一样的值

用于分析函数他们的语法是:

Row_number() Over ([Partition by 

ROW_NUMBER,RANK,DENSE_RANK是在查询结果集排序的基础上,将结果集(必须包含order by子句,没有window子句,可以有partition子句,无参数)的每行从1到n标上行号,这个n小于等于查询出的行的数目。规则如下:


ROW_NUMBER

Row_number函数为每行分配唯一值,首行为1,依次递增,当排序之后有相同数据时,排名任意分配(这时不保证排序,如果要排序,比如额外加排序键值,一般加rowid)。总体是递增顺序。


DENSE_RANK
Dense_rank函数为每行分配一个唯一的值,首行为1,依次递增,除了排序之后遇到相同数据时,此时所有相同数据的排名都是一样的。但是总体是递增的,中间没有跳号。 

RANK
Rank函数为每行分配一个唯一的值,首行为1,依次递增,除了排序之后遇到相同的数据时,此时所有相同数据的排名是一样的,并且相同的排名与下一个不同排名之间会产生跳号现象,但是总体递增。

  下面,我们看这个实例,比如要求查询出所有的
2001年的订单,按照区域划分,计算总金额,分别用上面三种分析函数来计算排名:相同的排名相同,并且算上相同的名词,下一个不同的按排名数量递增(如排名是1 1,那么下一个是排名为3,使用rank实现。相同的排名相同,并且不算上相同的排名,如排名是1 1,那么下一个排名是2,用dense_rank实现。最后是依次递增,用row_number实现,语句如下:

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank,

DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 6;





由上图的结果我们可以看出:

1.order by后面的排序可以使用组函数(因为此处按group by分组),可以是一个指定列的表达式。

2.对于区分分析函数的执行结果,是按照order by后面指定的表达式值是否有重复,而不是行是否重复。如这里值要SUM(tot_sales)重复,就算重复值,然后应用规则。

     3.可以看出,是跳号的,sum一样的行号一样,而dense rank没有跳号,sum重复的行号一样。          Row_number是确定的。


  Rank和dense_rank可能会产生重复的排名,为了解决这个问题,需要在order by之后指定多个排序键值,如果没有重复的话,那么就是唯一。

只需要将上面的查询语句稍作修改,如下:

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC,CUST_NBR) sales_rank, --增加红色部分

  DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC,CUST_NBR ) sales_dense_rank,

  ROW_NUMBER(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr
ORDER BY 6;


可以看出,已经没有重复的等级号码了。
当然,我们也可以使用partition子句,在组内排名,把上面的语句稍作修改为:

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  RANK(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_rank,
--分析函数中可以使用组函数

  DENSE_RANK(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr
ORDER BY 1;
--按照region_id排序。如果不指定这句,那么最终结果顺序会以sum为主,结果集不变。



未完待续,见PART5:http://blog.chinaunix.net/uid-7655508-id-5834869.html


阅读(6416) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~