oracle 分析函数 使用技巧,Oracle 分析函数使用介绍

今天我主要给大家介绍一下以下几个函数的使用方法

1.  自动汇总函数rollup，cube，

2.  rank 函数， rank，dense_rank，row_number

3.  lag，lead函数

4.  sum，avg，的移动增加，移动平均数

5.  ratio_to_report报表处理函数

6.  first，last取基数的分析函数

基础数据

insert into t values ('200405',          5761,       'G',              7393344.04);

insert into t values ('200405',          5762       ,'G',               6315075.96);

insert into t values ('200405',          5762       ,'J',              6328716.15);

insert into t values ('200405',          5763       ,'G',               8861742.59);

insert into t values ('200405',          5763       ,'J',               7788036.32);

insert into t values ('200405',          5764       ,'G',               6028670.45);

insert into t values ('200405',          5764       ,'J',               6459121.49);

insert into t values ('200405',          5765       ,'G',              13156065.77);

insert into t values ('200405',          5765       ,'J',              11901671.70);

insert into t values ('200406',          5761       ,'G',               7614587.96);

insert into t values ('200406',          5761       ,'J',               5704343.05);

insert into t values ('200406',          5762       ,'G',               6556992.60);

insert into t values ('200406',          5762       ,'J',               6238068.05);

insert into t values ('200406',          5763       ,'G',               9130055.46);

insert into t values ('200406',          5763       ,'J',               7990460.25);

insert into t values ('200406',          5764       ,'G',               6387706.01);

insert into t values ('200406',          5764       ,'J',               6907481.66);

insert into t values ('200406',          5765       ,'G',              13562968.81);

insert into t values ('200406',          5765       ,'J',              12495492.50);

insert into t values ('200407',          5761       ,'G',               7987050.65);

insert into t values ('200407',          5761       ,'J',               5723215.28);

insert into t values ('200407',          5762       ,'G',               6833096.68);

insert into t values ('200407',          5762       ,'J',               6391201.44);

insert into t values ('200407',          5763       ,'G',               9410815.91);

insert into t values ('200407',          5763       ,'J',               8076677.41);

insert into t values ('200407',          5764       ,'G',               6456433.23);

insert into t values ('200407',          5764       ,'J',               6987660.53);

insert into t values ('200407',          5765       ,'G',             14000101.20);

insert into t values ('200407',          5765       ,'J',              12301780.20);

insert into t values ('200408',          5761       ,'G',               8085170.84);

insert into t values ('200408',          5761       ,'J',               6050611.37);

insert into t values ('200408',          5762       ,'G',               6854584.22);

insert into t values ('200408',          5762       ,'J',               6521884.50);

insert into t values ('200408',          5763       ,'G',               9468707.65);

insert into t values ('200408',          5763       ,'J',              8460049.43);

insert into t values ('200408',          5764       ,'G',               6587559.23);

insert into t values ('200408',          5764       ,'J',               7342135.86);

insert into t values ('200408',          5765       ,'G',              14450586.63);

insert into t values ('200408',          5765       ,'J',              12680052.38);

1. 使用rollup函数的介绍

下面是直接使用普通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