Oracle 分析函数(Analytic Functions) 说明
Oracle中分析函数用法小结
Oracle中分析函数用法小结一.分析函数适用场景:○1需要对同样的数据进行不同级别的聚合操作○2需要在表内将多条数据和同一条数据进行多次的比较○3需要在排序完的结果集上进行额外的过滤操作二.分析函数语法:FUNCTION_NAME(<argument>,<argument>...)OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函数名(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm) over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.1)FUNCTION子句ORACLE提供了26个分析函数,按功能分5类分析函数分类等级(ranking)函数:用于寻找前N种查询开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上例:sum(t.sal) over (order by t.deptno,t.ename) running_total,sum(t.sal) over (partition by t.deptno order by t.ename) department_total制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列例:sum(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno) department_total2制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值2)PARTITION子句按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组3)ORDER BY子句分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区在Order by 子句后可以添加nulls last,如:order by comm desc nulls last 表示排序时忽略comm列为空的行.4)WINDOWING子句用于定义分析函数将在其上操作的行的集合Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDER BY子句根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量.5)Rang窗口Range 5 preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合ANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中例:avg(t.sal) over(order by t.hiredate asc range 100 preceding) 统计前100天平均工资6)Row窗口利用ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列7)Specifying窗口UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行CURRENT ROW:该窗口从当前行开始(并结束)Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从行序值小于数字表达式的当前行的值开始. Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)range between 100 preceding and 100 following:当前行100前, 当前行100后注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的.除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用。
Oracle分析函数用法详解
Oracle分析函数Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
Oracle从8.1.6开始提供分析函数。
一、基本语法oracle分析函数的语法：function_name(arg1,arg2,...)over(<partition-clause> <order-by-clause ><windowing clause>)说明：1.partition-clause 数据记录集分组2.order-by-clause 数据记录集排序3.windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。
有三种开窗方式: range、row、specifying。
二、常用分析函数1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t;DEPTNO EMPNO SAL AVG_SAL---------- ---------- ---------- ----------10 7782 2450 2916.666677839 5000 2916.666677934 1300 2916.6666720 7566 2975 21757902 3000 21757876 1100 21757369 800 21757788 3000 217530 7521 1250 1566.666677844 1500 1566.666677499 1600 1566.666677900 950 1566.666677698 2850 1566.666677654 1250 1566.666672.count(<distinct><*><expression>) 对组内数据进行计数3．rank() 和dense_rank()dense_rank()根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。
Oracle 分析函数的使用
Oracle 分析函数的使用Oracle 分析函数使用介绍分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1. 自动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. lag,lead函数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据Code: [Copy to clipboard]06:34:23 SQL> select * from t;BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- --------------200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.Elapsed: 00:00:00.001. 使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:50 2 from t06:43:51 3 group by area_code06:43:57 4 union all06:44:00 5 select '合计' area_code,sum(local_fare) local_fare06:44:06 6 from t06:44:08 7 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计 333157065.316 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=24884)1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)4 1 SORT (AGGREGATE)5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=17017)Statistics----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size561 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed下面是使用分析函数rollup得出的汇总数据的例子06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合计'))06:45:50 4 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19333157065.316 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309Bytes=24871)1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size557 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.1. 使用cube函数的介绍Quote:为了介绍cube函数我们再来看看另外一个使用rollup的例子06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare06:53:37 2 from t06:53:38 3 group by rollup(area_code,bill_month)06:53:49 4 /---------- --------------- --------------5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19333157065.3126 rows selected.Elapsed: 00:00:00.00系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.下面,让我们看看使用cube函数的结果06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:58:30 2 from t06:58:32 3 group by cube(area_code,bill_month)06:58:42 4 order by area_code,bill_month nulls last06:58:57 5 /---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72200405 79899.53200406 82588.15200407 84168.03200408 86501.34333157.0530 rows selected.Elapsed: 00:00:00.01可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果1 rollup 和cube函数的再深入Quote:从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,3 sum(local_fare) local_fare4 from t5 group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL> /AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.2. rank函数的介绍介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.问题2.我想查出这几个月份中各个地区的总话费的排名.Quote:为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.1 update t t1 set local_fare = (2 select local_fare from t t23 where t1.bill_month = t2.bill_month4 and _type = _type5 and t2.area_code = '5761'6* ) where area_code = '5763'07:19:18 SQL> /8 rows updated.Elapsed: 00:00:00.01我们先使用rank函数来计算各个地区的话费排名.07:34:19 SQL> select area_code,sum(local_fare) local_fare,07:35:25 2 rank() over (order by sum(local_fare) desc) fare_rank07:35:44 3 from t07:35:45 4 group by area_codee07:35:50 507:35:52 SQL> select area_code,sum(local_fare) local_fare,07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank07:36:20 3 from t07:36:21 4 group by area_code07:36:25 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 45762 52039.62 5Elapsed: 00:00:00.01我们可以看到红色标注的地方出现了,跳位,排名3没有出现下面我们再看看dense_rank查询的结果.07:36:26 SQL> select area_code,sum(local_fare) local_fare,07:39:16 2 dense_rank() over (order by sum(local_fare) desc ) fare_rank 07:39:39 3 from t07:39:42 4 group by area_code07:39:46 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4Elapsed: 00:00:00.00在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处1 select area_code,sum(local_fare) local_fare,2 row_number() over (order by sum(local_fare) desc ) fare_rank3 from t4* group by area_code07:44:50 SQL> /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.2. rank函数的介绍a. 取出数据库中最后入网的n个用户select user_id,tele_num,user_name,user_status,create_datefrom (select user_id,tele_num,user_name,user_status,create_date,rank() over (order by create_date desc) add_rankfrom user_info)where add_rank <= :n;b.根据object_name删除数据库中的重复记录create table t as select obj#,name from sys.obj$;再insert into t1 select * from t1 数次.delete from t1 where rowid in (select row_id from (select rowid row_id,row_number() over (partition by obj# order by rowid ) rn ) where rn <> 1);c. 取出各地区的话费收入在各个月份排名.SQL> select bill_month,area_code,sum(local_fare) local_fare,2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank3 from t4 group by bill_month,area_code5 /BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK--------------- --------------- -------------- ----------200405 5765 25057.74 1200405 5761 13060.43 2200405 5763 13060.43 2200405 5762 12643.79 4200405 5764 12487.79 5200406 5765 26058.46 1200406 5761 13318.93 2200406 5763 13318.93 2200406 5764 13295.19 4200406 5762 12795.06 5200407 5765 26301.88 1200407 5761 13710.27 2200407 5763 13710.27 2200407 5764 13444.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2200408 5764 13929.69 4200408 5762 13376.47 520 rows selected.SQL>3. lag和lead函数介绍取出每个月的上个月和下个月的话费总额1 select area_code,bill_month, local_fare cur_local_fare,2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,4 lead(local_fare,1,0) over (partition by area_code order by bill_month )next_local_fare,5 lead(local_fare,2,0) over (partition by area_code order by bill_month )post_local_fare6 from (7 select area_code,bill_month,sum(local_fare) local_fare8 from t9 group by area_code,bill_month10* )SQL> /AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARELAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE--------- ---------- -------------- -------------- --------------- --------------- ---------------5761 200405 13060.433 0 0 13318.93 13710 .2655761 200406 13318.93 0 13060.433 13710.265 14 135.7815761 200407 13710.265 13060.433 13318.93 14135.7815761 200408 14135.781 13318.93 13710.265 05762 200405 12643.791 0 0 12795.06 13224 .2975762 200406 12795.06 0 12643.791 13224.297 13 376.4685762 200407 13224.297 12643.791 12795.06 13376.4685762 200408 13376.468 12795.06 13224.297 05763 200405 13060.433 0 0 13318.93 13710 .2655763 200406 13318.93 0 13060.433 13710.265 14 135.7815763 200407 13710.265 13060.433 13318.93 14135.7815763 200408 14135.781 13318.93 13710.265 05764 200405 12487.791 0 0 13295.187 1344 4.0935764 200406 13295.187 0 12487.791 13444.093 1 3929.6945764 200407 13444.093 12487.791 13295.187 13929.6945764 200408 13929.694 13295.187 13444.093 05765 200405 25057.736 0 0 26058.46 26301 .8815765 200406 26058.46 0 25057.736 26301.881 27 130.6385765 200407 26301.881 25057.736 26058.46 27130.6385765 200408 27130.638 26058.46 26301.881 020 rows selected.利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.4. sum,avg,max,min移动计算数据介绍计算出各个连续3个月的通话费用的平均数1 select area_code,bill_month, local_fare,2 sum(local_fare)3 over ( partition by area_code4 order by to_number(bill_month)5 range between 1 preceding and 1 following ) "3month_sum",6 avg(local_fare)7 over ( partition by area_code8 order by to_number(bill_month)9 range between 1 preceding and 1 following ) "3month_avg",10 max(local_fare)11 over ( partition by area_code12 order by to_number(bill_month)13 range between 1 preceding and 1 following ) "3month_max",14 min(local_fare)15 over ( partition by area_code16 order by to_number(bill_month)17 range between 1 preceding and 1 following ) "3month_min"18 from (19 select area_code,bill_month,sum(local_fare) local_fare20 from t21 group by area_code,bill_month22* )SQL> /AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg3month_max 3month_min--------- ---------- ---------------- ---------- ---------- ---------- ----------5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5761 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5761 200407 13710.265 41164.976 13721.6587 14135.781 13318.93 40089.628 = 13060.433 + 13318.930 + 13710.26513363.2093 = (13060.433 + 13318.930 + 13710.265) / 313710.265 = max(13060.433 + 13318.930 + 13710.265)13060.433 = min(13060.433 + 13318.930 + 13710.265)5761 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5762 200405 12643.791 25438.851 12719.4255 12795.06 12643.791 5762 200406 12795.060 38663.148 12887.716 13224.297 12643.791 5762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06 5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.297 5763 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5763 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5763 200407 13710.265 41164.976 13721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5764 200405 12487.791 25782.978 12891.489 13295.187 12487.791 5764 200406 13295.187 39227.071 13075.6903 13444.093 12487.791 5764 200407 13444.093 40668.974 13556.3247 13929.694 13295.187 5764 200408 13929.694 27373.787 13686.8935 13929.694 13444.093 5765 200405 25057.736 51116.196 25558.098 26058.46 25057.736 5765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736 5765 200407 26301.881 79490.979 26496.993 27130.638 26058.46 5765 200408 27130.638 53432.519 26716.2595 27130.638 26301.88120 rows selected.5. ratio_to_report函数的介绍Quote:1 select bill_month,area_code,sum(local_fare) local_fare,2 ratio_to_report(sum(local_fare)) over3 ( partition by bill_month ) area_pct4 from t5* group by bill_month,area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on bill_monthSQL> compute sum of area_pct on bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT---------- --------- ---------------- ----------200405 5761 13060.433 .1711492795762 12643.791 .1656894315763 13060.433 .1711492795764 12487.791 .1636451435765 25057.736 .328366866********** ---------------- ----------sum 76310.184 1200406 5761 13318.930 .1690507725762 12795.060 .1624015425763 13318.930 .1690507725764 13295.187 .1687494145765 26058.460 .330747499********** ---------------- ----------sum 78786.567 1200407 5761 13710.265 .1705451975762 13224.297 .1645001275763 13710.265 .1705451975764 13444.093 .1672342215765 26301.881 .327175257********** ---------------- ----------sum 80390.801 1200408 5761 14135.781 .1709111475762 13376.468 .1617305395763 14135.781 .1709111475764 13929.694 .1684194165765 27130.638 .328027751********** ---------------- ----------sum 82708.362 120 rows selected.6 first,last函数使用介绍Quote:取出每月通话费最高和最低的两个用户.1 select bill_month,area_code,sum(local_fare) local_fare,2 first_value(area_code)3 over (order by sum(local_fare) desc4 rows unbounded preceding) firstval,5 first_value(area_code)6 over (order by sum(local_fare) asc7 rows unbounded preceding) lastval8 from t9 group by bill_month,area_code10* order by bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL ---------- --------- ---------------- --------------- ---------------200405 5764 12487.791 5765 5764200405 5762 12643.791 5765 5764200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 **** ****200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 576420 rows selected.。
Oracle分析函数的使用（主要是rollup用法）
Oracle分析函数的使⽤（主要是rollup⽤法）分析函数是oracle 8.1.6中就引⼊的⼀个全新的概念,为我们分析数据提供了⼀种简单⾼效的处理⽅式.在分析函数出现以前,我们必须使⽤⾃联查询,⼦查询或者内联视图,甚⾄复杂的存储过程实现的语句,现在只要⼀条简单的sql语句就可以实现了,⽽且在执⾏效率⽅⾯也有相当⼤的提⾼.分析函数的使⽤⽅法1. ⾃动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. 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)，…… 这样任意按⾃⼰想要的形式结合统计数据，⾮常⽅便。
Oracle分析函数（10G）语法详解（转）
Oracle分析函数（10G）语法详解（转）Oracle 分析函数（10G）一、Oracle分析函数简介1、分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的是为了解决诸如“累计计算”，“找出分组内百分比”，“前-N条查询”，“移动平均数计算”"等问题。
其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。
分析函数是SQL言语的一种扩充，它并不是仅仅试代码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快。
现在这些扩展已经被纳入了美国国家标准化组织SQL委员会的SQL规范说明书中。
2、在日常的生产环境中，我们接触得比较多的是OLTP系统(即OnlineTransaction Process），这些系统的特点是具备实时要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。
比如我们经常接触到的电子商城。
在这些系统之外，还有一种称之为OLAP的系统(即Online Aanalyse Process），这些系统一般用于系统决策使用。
通常和数据仓库、数据分析、数据挖掘等概念联系在一起。
这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。
Oracle分析函数，主要用于OLAP的系统中二、Oracle分析函数原理1、分析函数通过将行分组后，再计算这些分组的值。
它们与聚集函数不同之处在于能够对每一个分组返回多行值。
分析函数根据analytic claues(分析子句）将行分组，一个分组称为：一个窗口（可通过Windowsing Clause子句进行控制），并通过分析语句定义，对于每一行都对应有一个在行上滑动的窗口。
该窗口确定当前行的计算范围。
窗口大小可以用多个物理行（例如：rowid实际编号）进行度量，也可以使用逻辑区间进行度量，比如时间。
2、分析函数是查询中除需要在最终处理的order by 子句之外最后执行的操作。
常用Oracle分析函数详解
常⽤Oracle分析函数详解学习步骤：1. 拥有Oracle EBS demo 环境或者 PROD 环境2. copy以下代码进 PL/SQL3. 配合解释分析结果4. 如果⽹页有点乱请复制到TXT中查看/*假设⼀个经理代表了⼀个部门*/SELECT emp.full_name,emp.salary,emp.manager_id,row_number() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) row_number_dept, --部门排⾏rownum row_number, --⾏号round((rownum + 1) / 4) page_number, --每4⾏⼀页ntile(2) over(ORDER BY emp.salary DESC) page_number_nt, --平均分成两类AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department, --该部门薪⽔均值SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, --该部门薪⽔总额COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department, --部门所有的员⼯dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, --该⼈员的部门薪⽔排⾏dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company, --该⼈员的全公司排⾏MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept, --部门的最低薪⽔MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, --部门的最低薪⽔first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, --部门的最低薪⽔MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept, --部门的最⾼薪⽔MAX(emp.salary) keep(dense_rank LAST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept_last, --部门的最⾼薪⽔last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv, --部门的最⾼薪⽔lag(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) last_persion, --薪⽔在⾃⼰前⼀位的⼈lead(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) next_persion --薪⽔在⾃⼰后⼀位的⼈FROM fwk_tbx_employees empORDER BY emp.salary DESC1. 基本概念理解分析函数1. 顾名思义，分析函数是在主查询结果的基础上进⾏⼀定的分析，如分部门汇总，分部门求均值等等。
Oracle之分析函数
Oracle之分析函数⼀、分析函数 1、分析函数 分析函数是Oracle专门⽤于解决复杂报表统计需求的功能强⼤的函数，它可以在数据中进⾏分组然后计算基于组的某种统计值，并且每⼀组的每⼀⾏都可以返回⼀个统计值。
2、分析函数和聚合函数的区别 普通的聚合函数⽤group by分组，每个分组返回⼀个统计值，⽽分析函数采⽤partition by分组，并且每组每⾏都可以返回⼀个统计值。
3、分析函数的形式 分析函数带有⼀个开窗函数over()，包含分析⼦句。
分析⼦句⼜由下⾯三部分组成： partition by ：分组⼦句，表⽰分析函数的计算范围，不同的组互不相⼲； ORDER BY：排序⼦句，表⽰分组后，组内的排序⽅式； ROWS/RANGE：窗⼝⼦句，是在分组(PARTITION BY)后，组内的⼦分组（也称窗⼝），此时分析函数的计算范围窗⼝，⽽不是PARTITON。
窗⼝有两种，ROWS和RANGE； 使⽤形式如下：OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd) 注：窗⼝⼦句在这⾥我只说rows⽅式的窗⼝,range⽅式和滑动窗⼝也不提。
⼆、OVER() 函数 1、sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执⾏顺序 分析函数是在整个sql查询结束后(sql语句中的order by的执⾏⽐较特殊)再进⾏的操作, 也就是说sql语句中的order by也会影响分析函数的执⾏结果： [1] 两者⼀致：如果sql语句中的order by满⾜分析函数分析时要求的排序，那么sql语句中的排序将先执⾏,分析函数在分析时就不必再排序； [2] 两者不⼀致：如果sql语句中的order by不满⾜分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执⾏排序。
2、分析函数中的分组/排序/窗⼝分析函数包含三个分析⼦句：分组(partition by)，排序(order by)，窗⼝(rows/range)窗⼝就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗⼝中的记录⽽不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗⼝指定到该分组中的第⼀⾏数据到当前⾏, 如果你指定该窗⼝从该分组中的第⼀⾏到最后⼀⾏,那么该组中的每⼀个sum值都会⼀样，即整个组的总和。
Oracle开发之分析函数简介Over用法
Oracle开发之分析函数简介Over⽤法⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
我们来看看下⾯的⼏个典型例⼦：①查找上⼀年度各个销售区域排名前10的员⼯②按区域查找上⼀年度订单总额占区域订单总额20%以上的客户③查找上⼀年度销售最差的部门所在的区域④查找上⼀年度销售最好和最差的产品我们看看上⾯的⼏个例⼦就可以感觉到这⼏个查询和我们⽇常遇到的查询有些不同，具体有：①需要对同样的数据进⾏不同级别的聚合操作②需要在表内将多条数据和同⼀条数据进⾏多次的⽐较③需要在排序完的结果集上进⾏额外的过滤操作⼆、Oracle分析函数简单实例：下⾯我们通过⼀个实际的例⼦：按区域查找上⼀年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应⽤。
【1】测试环境：复制代码代码如下:SQL> desc orders_tmp;Name Null? Type----------------------- -------- ----------------CUST_NBR NOT NULL NUMBER(5)REGION_ID NOT NULL NUMBER(5)SALESPERSON_ID NOT NULL NUMBER(5)YEAR NOT NULL NUMBER(4)MONTH NOT NULL NUMBER(2)TOT_ORDERS NOT NULL NUMBER(7)TOT_SALES NOT NULL NUMBER(11,2)【2】测试数据：复制代码代码如下:SQL> select * from orders_tmp;CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES---------- ---------- -------------- ---------- ---------- ---------- ----------11 7 11 2001 7 2 122044 5 4 2001 10 2 378027 6 7 2001 2 3 375010 6 8 2001 1 2 2169110 6 7 2001 2 3 4262415 7 12 2000 5 6 2412 7 9 2000 6 2 506581 52 20003 2 444941 5 1 2000 92 748642 5 4 20003 2 350602 5 4 2000 4 4 64542 5 1 2000 10 4 355804 5 4 2000 12 2 3919013 rows selected.【3】测试语句：复制代码代码如下:SQL> select o.cust_nbr customer,o.region_id region,sum(o.tot_sales) cust_sales,sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales from orders_tmp owhere o.year = 2001group by o.region_id, o.cust_nbr;CUSTOMER REGION CUST_SALES REGION_SALES---------- ---------- ---------- ------------4 5 37802 378027 6 3750 6806510 6 64315 6806511 7 12204 12204三、分析函数OVER解析：请注意上⾯的绿⾊⾼亮部分，group by的意图很明显：将数据按区域ID，客户进⾏分组，那么Over这⼀部分有什么⽤呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式.官方对分析函数的说明如下：Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bytheanalytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time.Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause.Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates.From：Analytic Functions/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。
区别是在调用分析函数时，后面加上了开窗子句over()。
聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。
分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。
产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。
运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。
Oracle 聚合函数（Aggregate Functions）说明/tianlesoftware/article/details/7057249分析函数的over()部分的分析字句有3部分构成，分区语句，排序语句和窗口语句。
（1）分区语句（partition by）：将查询结果分为不同的组，功能类似于group by 语句,是分析函数工作的基础。
默认是将所有结果作为一个分组。
（2）排序语句(order by)：将每个分区进行排序。
（3）窗口语句：定义当前窗口，具体说是对每一个分组，按照给定的排序规则排序后，从分组的顶部到底部依次迭代，每次针对当前的行可以定义一个包含若干行的窗口。
如果省略了窗口语句，默认使用从分组第一行到当前行的分组。
Analyticfunctions are commonly used in data warehousing environments. In the list ofanalytic functions that follows, functions followed by an asterisk (*) allowthe full syntax, including the windowing_clause.--分析函数通常在数据仓库环境下使用，下表列出了所有的分析函数，其中加星号的支持全部语法，包括开窗选项。
AVG *CORR *COUNT *COVAR_POP *COVAR_SAMP *CUME_DISTDENSE_RANKFIRSTFIRST_VALUE *LAGLASTLAST_VALUE *LEADLISTAGGMAX *MIN *NTH_VALUE*NTILEPERCENT_RANKPERCENTILE_CONTPERCENTILE_DISCRANKRATIO_TO_REPORTREGR_ (Linear Regression) Functions *ROW_NUMBERSTDDEV *STDDEV_POP *STDDEV_SAMP *SUM *VAR_POP *VAR_SAMP *VARIANCE *二．Analytic Functions 使用示例下面主要介绍一下以下几个函数的使用方法1. Over()开窗函数2. Nvl()函数3. Rollup,Cube自动汇总函数4. Rank,Dense_rank,Row_number函数5. Lag , Lead函数6. Sum,Avg, Count, Max函数7. Ratio_to_report报表处理函数8. First,Last，First_value，Last_value取基数的分析函数9. Greatest,Least 函数10. Trunc, Round,Decode, Substr函数2.1 Over() 开窗函数Over() 开窗函数是Oracle的分析函数，其语法如下：函数名([ 参数]) over( [ 分区子句] [ 排序子句[ 滑动窗口子句] ])分区子句类似于聚组函数所需要的group by，排序子句可看成是SQL语句中的orderby，只不过在此语句中还可指定null值排前（nullsfirst）还是排后（nulls last）。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：over（order by salary）按照salary排序进行累计，orderby是个默认的开窗函数over（partition by deptno）按照部门分区over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行，之后150行over（order by salary rows between unboundedpreceding and unbounded following）每行对应的数据窗口是从第一行到最后一行，等效：over（order by salary range between unboundedpreceding and unbounded following）2.2 Nvl() 函数NVL（EXP1,EXP2），函数返回exp1和exp2中第一个不为空的值。
如果exp1为空则返回exp2，否则返回exp1。
注意：如果exp1不是字符串，那么返回的数据类型和exp1的数据类型相同，否则返回的数据类型为varchar2型。
SQL> select nvl('This is not null',7) Frist, nvl(null, 'My Oracle') Secondfrom dual;FRIST SECOND------------------ -------------This is not null My Oracle2.3 自动汇总函数rollup,cubeRollup:表示的意思是：除了分组的功能外，还进行累加的的，多了一个汇总。
如果是GROUP BYROLLUP(A, B, C) 的话，GROUP BY 顺序(A 、B 、C)(A 、B)(A)最后对全表进行GROUP BY 操作。
Cube提供了按照多字段汇总的功能。
如果是GROUP BY CUBE(A,B, C) ，GROUP BY 顺序(A 、B 、C)(A 、B)(A 、C)(A) ，(B 、C)(B)(C) ，最后对全表进行GROUP BY 操作。
示例：1. CREATE TABLE studentscore2. (3. student_name varchar2(20),4. subjects varchar2(20),5. score number6. )7.8.9. INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);10. INSERT INTO studentscore VALUES('WBQ','MATHS',95);11. INSERT INTO studentscore VALUES('WBQ','CHINESE',88);12. INSERT INTO studentscore VALUES('CZH','ENGLISH',80);13. INSERT INTO studentscore VALUES('CZH','MATHS',90);14. INSERT INTO studentscore VALUES('CZH','HISTORY',92);15. INSERT INTO studentscore VALUES('CB','POLITICS',70);16. INSERT INTO studentscore VALUES('CB','HISTORY',75);17. INSERT INTO studentscore VALUES('LDH','POLITICS',80);18. INSERT INTO studentscore VALUES('LDH','CHINESE',90);19. INSERT INTO studentscore VALUES('LDH','HISTORY',95);20.21. select * from studentscore;22.23. /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */24. SELECT student_name, subjects, SUM (score)25. FROM studentscore26. GROUP BY CUBE (student_name, subjects)27. ORDER BY 1;28.29. <span style="color: rgb(255, 0, 0);">等同于以下标准SQL</span>30.31. /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */32. SELECT NULL, subjects, SUM (score)33. FROM studentscore34. GROUP BY subjects35. UNION36. SELECT student_name, NULL, SUM (score)37. FROM studentscore38. GROUP BY student_name39. UNION40. SELECT NULL, NULL, SUM (score)41. FROM studentscore42. UNION43. SELECT student_name, subjects, SUM (score)44. FROM studentscore45. GROUP BY student_name, subjects46.47.48. /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */49. SELECT student_name, subjects, SUM (score)50. FROM studentscore51. GROUP BY ROLLUP (student_name, subjects);52.53.54. /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */55. SELECT student_name, NULL, SUM (score)56. FROM studentscore57. GROUP BY student_name58. UNION59. SELECT NULL, NULL, SUM (score)60. FROM studentscore61. UNION62. SELECT student_name, subjects, SUM (score)63. FROM studentscore64. GROUP BY student_name, subjects65.66.67.68.69. /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */70. SELECT GROUPING (student_name), GROUPING (subjects),student_name, subjects,71. SUM (score)72. FROM studentscore73. GROUP BY CUBE (student_name, subjects)74. ORDER BY 1, 2;75.76.77.78. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */79. SELECT GROUPING (student_name), GROUPING (subjects),student_name, subjects,80. SUM (score)81. FROM studentscore82. GROUP BY ROLLUP (student_name, subjects)83. ORDER BY 1, 2;84.85.86.87. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */88. SELECT GROUPING_ID (student_name, subjects), student_name,subjects,89. SUM (score)90. FROM studentscore91. GROUP BY CUBE (student_name, subjects)92. ORDER BY 1;93.94.95.96. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */97. SELECT GROUPING_ID (student_name, subjects), student_name,subjects,98. SUM (score)99. FROM studentscore100. G ROUP BY ROLLUP (student_name, subjects)101. O RDER BY 1;102.103.104. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */105. S ELECT GROUPING (student_name), GROUPING (subjects),106. C ASE107. W HEN GROUPING (student_name) = 0108. A ND GROUPING (subjects) = 1109. T HEN ' 学生成绩合计'110. W HEN GROUPING (student_name) = 1111. A ND GROUPING (subjects) = 0112. T HEN ' 课目成绩合计'113. W HEN GROUPING (student_name) = 1114. A ND GROUPING (subjects) = 1115. T HEN ' 总计'116. E LSE ''117. E ND summary,118. s tudent_name, subjects, SUM (score)119. F ROM studentscore120. G ROUP BY CUBE (student_name, subjects)121. O RDER BY 1, 2;2.4． rank, dense_rank,row_number函数Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。