分析函数是Oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明. 今天我主要给大家介绍一下以下几个函数的使用方法 1.; 自动汇总函数rollup,cube, 2.; rank 函数, rank,dense_rank,row_number 3.;;;;;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.04 200405; 5761;;;;J;;;5667089.85 200405;;;;;;;5762;;;;G;;;6315075.96 200405; 5762;;;;J;;;6328716.15 200405; 5763;;;;G;;;8861742.59 200405; 5763;;;;J;;;7788036.32 200405; 5764;;;;G;;;6028670.45 200405; 5764;;;;J;;;6459121.49 200405; 5765;;;;G;;13156065.77 200405; 5765;;;;J;;11901671.70 200406; 5761;;;;G;;;7614587.96 200406; 5761;;;;J;;;5704343.05 200406; 5762;;;;G;;;6556992.60 200406; 5762;;;;J;;;6238068.05 200406; 5763;;;;G;;;9130055.46 200406; 5763;;;;J;;;7990460.25 200406; 5764;;;;G;;;6387706.01 200406; 5764;;;;J;;;6907481.66 200406; 5765;;;;G;;13562968.81 200406; 5765;;;;J;;12495492.50 200407; 5761;;;;G;;;7987050.65 200407; 5761;;;;J;;;5723215.28 200407; 5762;;;;G;;;6833096.68 200407; 5762;;;;J;;;6391201.44 200407; 5763;;;;G;;;9410815.91 200407; 5763;;;;J;;;;;;;;;;;8076677.41 200407; 5764;;;;G;;;6456433.23 200407; 5764;;;;J;;;6987660.53 200407; 5765;;;;G;;14000101.20 200407; 5765;;;;J;;12301780.20 200408; 5761;;;;G;;;8085170.84 200408; 5761;;;;J;;;6050611.37 200408; 5762;;;;G;;;6854584.22 200408; 5762;;;;J;;;6521884.50 200408; 5763;;;;G;;;9468707.65 200408; 5763;;;;J;;;8460049.43 200408; 5764;;;;G;;;6587559.23 BILL_MONTH;;;AREA_CODE; NET_TYPE;;;;LOCAL_FARE --------------- ---------- ---------- -------------- 200408; 5764;;;;J;;;7342135.86 200408; 5765;;;;G;;14450586.63 200408; 5765;;;;J;;12680052.38 40 rows selected. Elapsed: 00:00:00.00 1. 使用rollup函数的介绍 Quote: ; 下面是直接使用普通sql语句求出各地区的汇总数据的例子 06:41:36 SQL> set autot on 06:43:36 SQL> select area_code,sum(local_fare) local_fare 06:43:502; from t 06:43:513; group by area_code 06:43:574; union all 06:44:005; select '合计' area_code,sum(local_fare) local_fare 06:44:066; from t 06:44:087; / AREA_CODE;;;LOCAL_FARE ---------- -------------- 5761; 54225413.04 5762; 52039619.60 5763; 69186545.02 5764; 53156768.46 5765 104548719.19 合计 333157065.31 6 rows selected. Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- 0;;;SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes= ; 24884) 1;0UNION-ALL 2;1;;SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871) 3;2;;;;TABLE Access (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248 ; 71) 4;1;;SORT (AGGREGATE) 5;4;;;;TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170 ; 17) Statistics ---------------------------------------------------------- ;;;;;;;0; recursive calls ; 0; db block gets ; 6; consistent gets ; 0; physical reads ; 0; redo size ;;;;;561; bytes sent via SQL*Net to client ;;;;;503; bytes received via SQL*Net from client ; 2; SQL*Net roundtrips to/from client ; 1; sorts (memory) ; 0; sorts (disk) ; 6; rows processed 下面是使用分析函数rollup得出的汇总数据的例子 06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare 06:45:262; from t 06:45:303; group by rollup(nvl(area_code,'合计')) 06:45:504; / AREA_CODE;;;LOCAL_FARE ---------- -------------- 5761; 54225413.04 5762; 52039619.60 5763; 69186545.02 5764; 53156768.46 5765 104548719.19 ;;;;;;;;;;333157065.31 6 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- 0;;;SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes= ; 24871) 1;0SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871) 2;1;;TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871 ; ) Statistics ---------------------------------------------------------- ; 0; recursive calls ;;;;;;;0; db block gets ; 4; consistent gets ; 0; physical reads ; 0; redo size ;;;;;557; bytes sent via SQL*Net to client ;;;;;503; bytes received via SQL*Net from client ; 2; SQL*Net roundtrips to/from client ; 1; 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_fare 06:53:372; from t 06:53:383; group by rollup(area_code,bill_month) 06:53:494; / AREA_CODE; BILL_MONTH; LOCAL_FARE ---------- --------------- -------------- 5761;;;;200405;;13060433.89 5761;;;;200406;;13318931.01 5761;;;;200407;;13710265.93 5761;;;;200408;;14135782.21 5761; 54225413.04 5762;;;;200405;;12643792.11 5762;;;;200406;;12795060.65 5762;;;;200407;;13224298.12 5762;;;;200408;;13376468.72 5762; 52039619.60 5763;;;;200405;;16649778.91 5763;;;;200406;;17120515.71 5763;;;;200407;;17487493.32 5763;;;;200408;;17928757.08 5763; 69186545.02 5764;;;;200405;;12487791.94 5764;;;;200406;;13295187.67 5764;;;;200407;;13444093.76 5764;;;;200408;;13929695.09 5764; 53156768.46 5765;;;;200405;;25057737.47 5765;;;;200406;;26058461.31 5765;;;;200407;;26301881.40 5765;;;;200408;;27130639.01 5765;;;;;;104548719.19 ;;333157065.31 26 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:302; from t 06:58:323; group by cube(area_code,bill_month) 06:58:424; order by area_code,bill_month nulls last 06:58:575; / AREA_CODE; BILL_MONTH; LOCAL_FARE ---------- --------------- -------------- 5761;;;;200405;;;;;13060.43 5761;;;;200406;;;;;13318.93 5761;;;;200407;;;;;13710.27 5761;;;;200408;;;;;14135.78 5761;;54225.41 5762; ;;;;;200405;;;;;12643.79 5762;;;;200406;;;;;12795.06 5762;;;;200407;;;;;13224.30 5762;;;;200408;;;;;13376.47 5762;;52039.62 5763;;;;200405;;;;;16649.78 5763;;;;200406;;;;;17120.52 5763;;;;200407;;;;;17487.49 5763;;;;200408;;;;;17928.76 5763;;69186.54 5764;;;;200405;;;;;12487.79 5764;;;;200406;;;;;13295.19 5764;;;;200407;;;;;13444.09 5764;;;;200408;;;;;13929.69 5764;;53156.77 5765;;;;200405;;;;;25057.74 5765;;;;200406;;;;;26058.46 5765;;;;200407;;;;;26301.88 5765;;;;200408;;;;;27130.64 5765;104548.72 200405;;;;;79899.53 200406;;;;;82588.15 200407;;;;;84168.03 200408;;;;;86501.34 ;;;;;333157.05 30 rows selected. Elapsed: 00:00:00.01 可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果]


1 rollup 和 cube函数的再深入 Quote: 从上面的结果中我们很轻易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了. 假如当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0 ; 1; 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_fare ; 4; from t ; 5; group by cube(area_code,bill_month) ; 6* order by area_code,bill_month nulls last 07:07:29 SQL> / AREA_CODE; BILL_MONTH; LOCAL_FARE ---------- --------------- -------------- 5761;;;;200405;;;;;13060.43
5761;;;;200406;;;;;13318.93 5761;;;;200407;;;;;13710.27 5761;;;;200408;;;;;14135.78 5761;;;;all month;;54225.41 5762;;;;200405;;;;;12643.79 5762;;;;200406;;;;;12795.06 5762;;;;200407;;;;;13224.30 5762;;;;200408;;;;;13376.47 5762;;;;all month;;52039.62 5763;;;;200405;;;;;16649.78 5763;;;;200406;;;;;17120.52 5763;;;;200407;;;;;17487.49
5763;;;;200408;;;;;17928.76 5763;;;;all month;;69186.54 5764;;;;200405;;;;;12487.79 5764;;;;200406;;;;;13295.19 5764;;;;200407;;;;;13444.09 5764;;;;200408;;;;;13929.69 5764;;;;all month;;53156.77 5765;;;;200405;;;;;25057.74 5765;;;;200406;;;;;26058.46 5765;;;;200407;;;;;26301.88 5765;;;;200408;;;;;27130.64 5765;;;;all month;104548.72
all area200405;;;;;79899.53 all area200406;;;;;82588.15 all area200407;;;;;84168.03 all area200408;;;;;86501.34 all areaall month;333157.05 30 rows selected. Elapsed: 00:00:00.01 07:07:31 SQL> 可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.