oracle分析函数

最新推荐文章于 2024-05-16 21:28:22 发布

labty

最新推荐文章于 2024-05-16 21:28:22 发布

阅读量61

点赞数

本文链接：https://blog.csdn.net/labty/article/details/84077901

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

分析函数是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取基数的分析函数

建立一个表:

create table TEST_T
(
  BILL_MONTH VARCHAR2(10),
  AREA_CODE  VARCHAR2(10),
  NET_TYPE   CHAR(1),
  LOCAL_FARE NUMBER(14,2)
)

插入数据如下所示:
[quote]select * from latty.test_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[/quote]
============================
set autot on；

SQL> select area_code,sum(local_fare) local_fare from latty.test_t group by area_code union all select '合计' area_code,sum(local_fare) local_fare from latty.test_t;

AREA_CODE  LOCAL_FARE
---------- ----------
5764       45814632.6
5765       77418080.2
5762       52039619.6
5761         54225413
5763         69186545
合计        298684290

已选择6行。

执行计划里面显示有30个consistent gets.

下面使用分析函数对这个汇总进行测试结果如下：

SQL>  select nvl(area_code,'合计') area_code,sum(local_fare) local_fare
  2          from latty.test_t
  3          group by rollup(nvl(area_code,'合计'));

AREA_CODE  LOCAL_FARE
---------- ----------
5761         54225413
5762       52039619.6
5763         69186545
5764       45814632.6
5765       77418080.2
            298684290

已选择6行。

执行计划显示只有15个consistent gets.因而大大提高了汇总的效率。
=======================================
我们来测试一个标准的rollup的例子：

select area_code,bill_month,sum(local_fare) local_fare
    from latty.test_t
    group by rollup(area_code,bill_month)
执行结果为:
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.

系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理.

==================================================
cube函数：
测试一下SQL语句:

  select area_code,bill_month,sum(local_fare) local_fare
               from latty.test_t
               group by cube(area_code,bill_month)
               order by area_code,bill_month nulls last
    执行结果如下：
    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

可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果

=====================================================
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
我们如何来区分到底是根据那个字段做的汇总呢,
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0

 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
           decode(grouping(bill_month),1,'all month',bill_month) bill_month,
          sum(local_fare) local_fare
    from latty.test_t
  group by cube(area_code,bill_month)
   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 area   200405                79899.53
all area   200406                82588.15
all area   200407                84168.03
all area   200408                86501.34
all area   all month            333157.05

labty

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle分析函数

分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法 1. 自动汇总函数rollu...
复制链接

扫一扫