分析函数是oracle引入的一个全新的概念为我们分析数据提供了一种简单高效的处理方式在分析函数出现以前我们必须使用自联查询子查询或者内联视图甚至复杂的存储过程实现的语句现在只要一条简单的sql语句就可以实现了而且在执行效率方面也有相当大的提高下面我将针对分析函数做一些具体的说明
今天我主要给大家介绍一下以下几个函数的使用方法
自动汇总函数rollupcube
rank 函数 rankdense_rankrow_number
laglead函数
sumavg的移动增加移动平均数
ratio_to_report报表处理函数
firstlast取基数的分析函数
基础数据
Code: [Copy to clipboard]
:: SQL> select * from t;
BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
J
G
BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE
J
G
J
rows selected
Elapsed: ::
使用rollup函数的介绍
Quote:
下面是直接使用普通sql语句求出各地区的汇总数据的例子
:: SQL> set autot on
:: SQL> select area_codesum(local_fare) local_fare
:: from t
:: group by area_code
:: union all
:: select 合计 area_codesum(local_fare) local_fare
:: from t
:: /
AREA_CODE LOCAL_FARE
合计
rows selected
Elapsed: ::
Execution Plan
SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=
)
UNIONALL
SORT (GROUP BY) (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=
)
SORT (AGGREGATE)
TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=
)
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
下面是使用分析函数rollup得出的汇总数据的例子
:: SQL> select nvl(area_code合计) area_codesum(local_fare) local_fare
:: from t
:: group by rollup(nvl(area_code合计))
:: /
AREA_CODE LOCAL_FARE
rows selected
Elapsed: ::
Execution Plan
SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=
)
SORT (GROUP BY ROLLUP) (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=
)
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
从上面的例子我们不难看出使用rollup函数系统的sql语句更加简单耗用的资源更少从个consistent gets降到个consistent gets如果基表很大的话结果就可想而知了
使用cube函数的介绍
Quote:
为了介绍cube函数我们再来看看另外一个使用rollup的例子
:: SQL> select area_codebill_monthsum(local_fare) local_fare
:: from t
:: group by rollup(area_codebill_month)
:: /
AREA_CODE BILL_MONTH LOCAL_FARE
rows selected
Elapsed: ::
系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理而没有对bill_month做汇总分析处理cube函数就是为了这个而设计的
下面让我们看看使用cube函数的结果
:: SQL> select area_codebill_monthsum(local_fare) local_fare
:: from t
:: group by cube(area_codebill_month)
:: order by area_codebill_month nulls last
:: /
AREA_CODE BILL_MONTH LOCAL_FARE
rows selected
Elapsed: ::
可以看到在cube函数的输出结果比使用rollup多出了几行统计数据这就是cube函数根据bill_month做的汇总统计结果]
rollup 和 cube函数的再深入
Quote:
从上面的结果中我们很容易发现每个统计数据所对应的行都会出现null我们如何来区分到底是根据那个字段做的汇总呢这时候oracle的grouping函数就粉墨登场了
如果当前的汇总记录是利用该字段得出的grouping函数就会返回否则返回
select decode(grouping(area_code)all areato_char(area_code)) area_code
decode(grouping(bill_month)all monthbill_month) bill_month
sum(local_fare) local_fare
from t
group by cube(area_codebill_month)
* order by area_codebill_month nulls last
:: SQL> /
AREA_CODE BILL_MONTH LOCAL_FARE
all month
all month
all month
all month
all month
all area
all area
all area
all area
all area all month
rows selected
Elapsed: ::
:: SQL>
可以看到所有的空值现在都根据grouping函数做出了很好的区分这样利用rollupcube和grouping函数我们做数据统计的时候就可以轻松很多了
