select id,area,stu_type,sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;
//--------理解rollupselect 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个扩展分析函数:
ROLLUP,CUBE,GROUPING
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里学习,这里只是简要的介绍一下作用。
掌握这些函数的用法,可以在程序开发中减少很多弯路,并且还可以大大提高运行效率。
------------------------------------------------------------------------------------------