southking
- 浏览: 711604 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
javenwong：
你好，我现在需要给一个Composite做圆角处理，不知道怎么 ...
SWT圆角窗口绘制 -
whao189：
回家了详细看看
Storyboard 解析 -
zhaoyubetter：
确实够麻烦
IOS 开发环境，证书和授权文件是什么？ -
datawarehouse：
一直对这两个认为是一样的，现在学习了。
Oracle DB中的Schema -
yulanfeiyang：
顶，mark
下载音频，在IOS设备上播放
ORACLE分析函数（一）
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
0 redo size
557 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函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.
1. 使用cube函数的介绍
Quote:
为了介绍cube函数我们再来看看另外一个使用rollup的例子
06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:37 2 from t
06:53:38 3 group by rollup(area_code,bill_month)
06:53:49 4 /
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.
Elapsed: 00:00:00.00
系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.
下面,让我们看看使用cube函数的结果
06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:58:30 2 from t
06:58:32 3 group by cube(area_code,bill_month)
06:58:42 4 order by area_code,bill_month nulls last
06:58:57 5 /
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
30 rows selected.
Elapsed: 00:00:00.01
可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果
1 rollup 和 cube函数的再深入
Quote:
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
我们如何来区分到底是根据那个字段做的汇总呢,
这时候,oracle的grouping函数就粉墨登场了.
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
1 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,
3 sum(local_fare) local_fare
4 from t
5 group by cube(area_code,bill_month)
6* 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
30 rows selected.
Elapsed: 00:00:00.01
07:07:31 SQL>
可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.
分享到：
发表评论
-
oracle的drop table if exists
2012-12-17 14:32 9560利用存储实现 create or replace pr ... -
Oracle PLSQL示例2
2012-11-19 11:16 552-- Created on 2010/04/16 by N ... -
Oracle PLSQL示例1
2012-11-19 11:16 755-- Created on 2010/04/16 by N ... -
Oracle定义联合数组
2012-11-19 11:16 1084-- Created on 2010/04/15 by NAN ... -
Oracle case_when
2012-11-19 11:16 868-- Created on 2010/04/17 by N ... -
Oracle date比较大小
2012-11-19 11:17 760-- Created on 2010/04/17 by N ... -
Oracle修改字段
2012-11-20 16:27 541alter table test modify col v ... -
Oracle查询表空间使用情况
2012-11-20 16:27 631select f.tablespace_name,a.to ... -
Oracle FORALL_Example_2
2012-11-20 16:27 641-- Created on 2010/05/07 by N ... -
Oracle FORALL_Example
2012-11-20 16:28 730CREATE TABLE blktest (num NUM ... -
Oracle Array_Example
2012-11-20 16:28 793-- Created on 2010/05/13 by N ... -
Oracle 利用结果集创建表
2012-11-21 11:44 893create table test_bak as sele ... -
Oracle group by 测试
2012-11-21 11:44 697CREATE TABLE test(a VARCHAR2( ... -
Oracle查询及删除重复数据
2012-11-21 11:45 776CREATE TABLE test (col VARCHA ... -
Oracle Function
2012-11-21 11:45 726SELECT substr('abc' ,2, 1) FR ... -
Oracle in和exists
2012-11-21 11:45 703in和exists in 是把外表和内表作hash 连接，而e ... -
Oracle 获得最大日期
2012-11-22 14:17 722-- Created on 2010/06/08 by N ... -
Oracle 游标小用
2012-11-22 14:17 638-- Created on 2010/06/25 by W ... -
Oracle 重置sequence2
2012-11-22 14:17 765CREATE OR REPLACE PROCEDURE R ... -
Oracle 重置sequence
2012-11-22 14:17 1582DECLARE n NUMBER(10 ) ...
相关推荐
#### 二、Oracle分析函数的基本结构 分析函数的基本结构如下： ```sql FUNCTION_NAME(,) OVER (<Partition-Clause><Order-by-Clause>) ``` - **FUNCTION_NAME**: 分析函数名称。 - ****: 函数参数，可以是表达式...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
二、分析函数的基本结构 分析函数的语法结构通常如下： ```sql FUNCTION_NAME([PARTITION BY column_list] [ORDER BY column_list]) OVER ( [RANGE | ROWS BETWEEN start AND end] ) AS alias ``` - `FUNCTION_...
Oracle 分析函数（用法+实例） Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用，属于 Oracle 的一大亮点。分析函数可以分为四大类：排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性，它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用，因为它们可以基于分组计算聚合值，并为每个分组返回多行，而不...
Oracle 分析函数是一种强大的SQL工具，它允许你在处理数据时执行复杂的分析操作，而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等，都是深入学习和理解Oracle分析函数的宝贵资源。
Oracle 分析函数详解 Oracle 分析函数是 Oracle 数据库中的一种强大功能，能够帮助用户快速进行数据分析和处理。在本文中，我们将对 Oracle 分析函数进行详细的介绍，并对其各个函数进行解释。 一、总体介绍 ...
Oracle 分析函数是一种高级SQL功能，它允许在单个查询中对数据集进行复杂的分析，无需额外的编程或多次数据库交互。分析函数处理的结果通常基于数据的分组、排序或特定窗口，为统计汇总和复杂的数据分析提供了便利。...
oracle 分析函数 开发必备 数据库开发工程师
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它自Oracle 8.1.6版本开始引入，并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算，并且不同于...
Oracle分析函数是数据库管理系统Oracle中的一种高级特性，用于处理和分析数据集，提供了一种高效的方式来执行聚合操作，而无需多次查询数据库。分析函数能够直接在单次查询中完成复杂的数据计算，包括排序、分组、...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，它允许用户在单个查询中对一组行进行计算，而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力，提高了查询性能。以下是对Oracle分析...
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
2. **Oracle分析函数简单实例** 假设我们有一个销售表，包含产品ID、销售日期和销售额，我们可以使用`ROW_NUMBER()`来为每条记录分配一个顺序编号，或者使用`SUM()`函数计算每个产品的累计销售额。 ```sql ...
Oracle 分析函数是数据库查询中的强大工具，它们允许在单个SQL语句中对结果集进行复杂的计算和分组操作，极大地简化了数据分析的过程。在Oracle 8i版本之后引入，分析函数为处理大量数据提供了高效的方法，避免了...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，它们在数据处理和分析中扮演着重要角色。分析函数允许用户在结果集的每一行上执行计算，不仅考虑当前行，还考虑了同一组内的其他行。这与传统的聚合函数...
Oracle分析函数是数据库管理系统Oracle中的一个重要特性，自8.1.6版本开始引入，它们用于执行基于组的聚合计算，并且为每个组返回多行结果，而不仅仅是单行。这使得分析函数在数据分析和报表生成方面非常有用，能够...
分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause]...
ORACLE分析函数 ORACLE分析函数是数据库管理系统中的一种功能强大且灵活的分析工具，能够对数据进行复杂的分析和处理。通过使用分析函数，开发者可以更加方便地实现业务逻辑，提高查询效率和数据处理速度。 在本...