Oracle分析函数使用总结

最新推荐文章于 2024-10-11 17:07:10 发布

comba8020

最新推荐文章于 2024-10-11 17:07:10 发布

阅读量76

点赞数

文章标签：数据库

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

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

上一篇：没有了~

下一篇： rman在归档模式下的备份与恢复练习

请登录后发表评论登录

全部评论

<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>

<%for(var j=0;j

<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>

还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看

<%}%>

<%}%> <%}%>

Hero--008

注册时间：2008-01-30

博文量
2
访问量

5406