orcale分析函数（一）

最新推荐文章于 2025-12-03 15:16:00 发布

最新推荐文章于 2025-12-03 15:16:00 发布 · 92 阅读

文章标签：

本文介绍了Oracle分析函数的基本概念及其在数据处理中的应用。通过对比传统方法与使用rollup和cube函数进行汇总统计的过程，展示了分析函数的优势：简化SQL语句并提高执行效率。此外，还介绍了如何使用grouping函数来区分不同维度的汇总结果。

分析函数是 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.