ITPub博客

首页 > 数据库 > Oracle > Oracle的分析函数

Oracle的分析函数

Oracle 作者:tymezhao 时间:2011-11-04 15:13:33 0 删除 编辑

select id,area,stu_type,sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;

//--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);

等效于

select * from (
select a, b, c, sum( d ) from t group by a, b, c
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)

 

GROUP BY的3个扩展分析函数:

ROLLUPCUBEGROUPING SETS

 

SELECT T.RECEIPTTYPE FROM GFMIS_ALL.RECEIPT T GROUP BY CUBE(T.RECEIPTTYPE);

SELECT T.RECEIPTTYPE FROM GFMIS_ALL.RECEIPT T GROUP BY ROLLUP(T.RECEIPTTYPE);

SELECT T.RECEIPTTYPE FROM GFMIS_ALL.RECEIPT T GROUP BY GROUPING SETS(T.RECEIPTTYPE); 

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

Oracle 分析函数:

分析函数的使用方法
1. 自动汇总函数rollup,cube,
2. rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数

 

      本人在项目中由于用到小计、合计的统计,前面想到用union all,但这样有点麻烦并且效率也不高,就从网上查到资料说是oracle 8i、oracl 9i、oracle 10g 中已经分析函数对数据统计的处理,于是就顺便学习了一下这些函数的用法,拿出来分享给大家共同学习。

1、Oracle ROLLUP和CUBE 用法    
      Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是Group by  ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY的结果。

       也可以 Group by Rollup(A,(B,C)) ,Group by A Rollup(B,C),…… 这样任意按自己想要的形式结合统计数据,非常方便。

 2、Rank的用法

       功能描述:根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序,然后给每一行赋一个号,从而形成一个序列,该序列从1开始,往后累加。每次ORDER BY表达式的值发生变化时,该序列也随之增加。有同样值的行得到同样的数字序号(认为null时相等的)。然而,如果两行的确得到同样的排序,则序数将随后跳跃。若两行序数为1,则没有序数2,序列将给组中的下一行分配值3,DENSE_RANK则没有任何跳跃。
       rank()是跳跃排序,比如有两个第二名时接下来就是第四名(同样是在各个分组内)

       dense_rank()l是连续排序,比如有两个第二名时仍然跟着第三名。  

3、First的用法

       功能描述:从DENSE_RANK返回的集合中取出排在最前面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

4、Last的用法

     功能描述:从DENSE_RANK返回的集合中取出排在最后面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

5、Lag的用法

       功能描述:可以访问结果集中的其它行而不用进行自连接。它允许去处理游标,就好像游标是一个数组一样。在给定组中可参考当前行之前的行,这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数,其默认值为1,若索引超出窗口的范围,就返回默认值(默认返回的是组中第一行),其相反的函数是LEAD。

 

具体各个函数的使用方法可去baidu里学习,这里只是简要的介绍一下作用。

掌握这些函数的用法,可以在程序开发中减少很多弯路,并且还可以大大提高运行效率。

 

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

 

http://xsb.itpub.net/post/419/33028

 

http://blog.csdn.net/wangdongzjk/article/details/532424

<!-- 正文结束 -->

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

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-06-18