分析函数是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)
)
插入数据如下所示:
引用
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
============================
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
分享到：
相关推荐
### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时极为有用的一套工具，主要用于在线分析处理（OLAP）场景。这类函数可以在多个级别上进行数据聚合，并支持复杂的排序、分组...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性，它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用，因为它们可以基于分组计算聚合值，并为每个分组返回多行，而不...
Oracle 分析函数（用法+实例） Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用，属于 Oracle 的一大亮点。分析函数可以分为四大类：排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
Oracle 分析函数是一种强大的SQL工具，它允许你在处理数据时执行复杂的分析操作，而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等，都是深入学习和理解Oracle分析函数的宝贵资源。
Oracle 分析函数详解 Oracle 分析函数是 Oracle 数据库中的一种强大功能，能够帮助用户快速进行数据分析和处理。在本文中，我们将对 Oracle 分析函数进行详细的介绍，并对其各个函数进行解释。 一、总体介绍 ...