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:502; from t06:43:513; group by area_code06:43:574; union all06:44:005; select '合计' area_code,sum(local_fare) local_fare06:44:066; from t06:44:087; /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;0UNION-ALL2;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_fare06:45:262; from t06:45:303; group by rollup(nvl(area_code,'合计'))06:45:504; /AREA_CODE;;;LOCAL_FARE---------- --------------5761; 54225413.045762; 52039619.605763; 69186545.025764; 53156768.465765 104548719.19;;;;;;;;;;333157065.316 rows selected.Elapsed: 00:00:00.00Execution 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_fare06:53:372; from t06:53:383; group by rollup(area_code,bill_month)06:53:494; /AREA_CODE; BILL_MONTH; LOCAL_FARE---------- --------------- --------------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.19;;333157065.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_fare06:58:302; from t06:58:323; group by cube(area_code,bill_month)06:58:424; order by area_code,bill_month nulls last06:58:575; /AREA_CODE; BILL_MONTH; LOCAL_FARE---------- --------------- --------------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.34;;;;;333157.0530 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 last07:07:29 SQL> /AREA_CODE; BILL_MONTH; LOCAL_FARE---------- --------------- --------------5761;;;;200405;;;;;13060.43 5761;;;;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.49 5763;;;;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.72 all area200405;;;;;79899.53all area200406;;;;;82588.15all area200407;;;;;84168.03all area200408;;;;;86501.34all areaall month;333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.