Oracle分析函数使用总结Word文档下载推荐.docx

Oracle分析函数使用总结Word文档下载推荐.docx

《Oracle分析函数使用总结Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《Oracle分析函数使用总结Word文档下载推荐.docx（17页珍藏版）》请在冰豆网上搜索。

ROW_NUMBER（）:

为每一条分组纪录返回一个数字

下面我们分别举例来说明这些函数的使用

1）RANK（）与DENSE-RANK（）

首先显示下我们的源表数据的结构及部分数据：

SQL>

descall_sales;

名称是否为空?

类型

------------------------------------------------------------

YEARNOTNULLNUMBER（38）

MONTHNOTNULLNUMBER（38）

PRD_TYPE_IDNOTNULLNUMBER（38）

EMP_IDNOTNULLNUMBER（38）

AMOUNTNUMBER（8,2）

select*fromall_saleswhererownum<

11;

YEARMONTHPRD_TYPE_IDEMP_IDAMOUNT

---------------------------------------------------

2003112110034.84

2003212115144.65

2003312120137.83

2003412125057.45

2003512117214.56

2003612115564.64

2003712112654.84

2003812117434.82

2003912119854.57

20031012121754.19

已选择10行。

好接下来我们将举例来说明上述函数的使用：

首先是RANK（）与DENSE-RANK（）的使用：

select

2prd_type_id,sum（amount）,

3RANK（）OVER（ORDERBYSUM（amount）DESC）ASrank,

4DENSE_RANK（）OVER（ORDERBYSUM（amount）DESC）ASdense_rank

5fromall_sales

6whereyear=2003

7groupbyprd_type_id

8orderbyrank;

PRD_TYPE_IDSUM（AMOUNT）RANKDENSE_RANK

------------------------------------------

511

1905081.8422

3478270.9133

4402751.1644

2186381.2255

注意：

这里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的话，那么上面语句的输出则为：

4478270.9133

2186381.2254

此外这里还有两个参数来限制空值的排序即：

NULLSFIRST和NULLSLAST

我们还以上面的例子来看：

select

3RANK（）OVER（ORDERBYSUM（amount）DESCNULLSLAST）ASrank,

4DENSE_RANK（）OVER（ORDERBYSUM（amount）DESCNULLSLAST）ASdense_rank

8*orderbyrank

1905081.8411

3478270.9122

4402751.1633

2186381.2244

555

可以看出刚才我们不使用NULLSLAST时PRD_TYPE_ID为5的空值的排序位于第一，现在则位于第五。

接下来来看分析函数与PARTITIONBY子句的结合使用：

当需要把分组划分为子分组时，那么我们便可以结合PRATITIONBY子句和分析函数同时使用。

如下例根据月份划分销量：

2prd_type_id,month,SUM（amount）,

3RANK（）OVER（PARTITIONBYmonthORDERBYSUM（amount）DESC）ASrank

4fromall_sales

5whereyear=2003

6andamountISNOTNULL

7GROUPBYprd_type_id,month

8*ORDERBYmonth,rank

PRD_TYPE_IDMONTHSUM（AMOUNT）RANK

------------------------------------------

1138909.041

3124909.042

4117398.433

2114309.044

1270567.91

4217267.92

3215467.93

2213367.94

1391826.981

4331026.982

3320626.983

2316826.984

14120344.71

3423844.72

4416144.73

2415664.74

1597287.361

4520087.362

3518687.363

2518287.364

1657387.841

4633087.842

3619887.843

2614587.844

3781589.041

1760929.042

2715689.043

4712089.044

1875608.921

3862408.922

4858408.923

2816308.924

1985027.421

4949327.422

3946127.423

2919127.424

110105305.221

41075325.142

31070325.293

21013525.144

11155678.381

31146187.382

41142178.383

21116177.844

31248209.041

11246209.042

41230409.053

21212509.044

已选择48行。

接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPINGSETS的结合使用：

SELECT

prd_type_id,SUM（amount）,

RANK（）OVER（ORDERBYSUM（amount）DESCNULLSLAST）ASrank

FROMall_sales

WHEREyear=2003

GROUPBYROLLUP（prd_type_id）

ORDERBYrank;

PRD_TYPE_IDSUM（AMOUNT）RANK

1972485.131（注：

RULLUP的总计排在了最前）

1905081.842

3478270.913

4402751.164

2186381.225

56

prd_type_id,emp_id,SUM（amount）,

GROUPBYCUBE（prd_type_id,emp_id）

ORDERBYprd_type_id,emp_id;

PRD_TYPE_IDEMP_IDSUM（AMOUNT）RANK

121197916.9612

122214216.9610

12398896.9619

124207216.9611

12593416.9621

12693417.0420

1905081.842

22120426.9633

22219826.9634

22319726.9635

22443866.9627

22532266.9631

226