发布时间 : 星期一 文章Oracle分析函数更新完毕开始阅读3b3de16f58fafab069dc0221
Oracle 分析函数使用介绍
分析函数是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:50 2 from t
06:43:51 3 group by area_code 06:43:57 4 union all
06:44:00 5 select '合计' area_code,sum(local_fare) local_fare 06:44:06 6 from t 06:44:08 7 /
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 0 UNION-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:26 2 from t
06:45:30 3 group by rollup(nvl(area_code,'合计')) 06:45:50 4 /
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 0 SORT (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