学步园

原文转自：http://blog.csdn.net/tianlesoftware/article/details/4795632

一.  Analytic Functions 说明

分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式.

官方对分析函数的说明如下：

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause.
For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore,
analytic functions can appear only in the select listor ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates.

From：Analytic Functions

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174

分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。

聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。

Oracle 聚合函数（Aggregate Functions）说明

http://blog.csdn.net/tianlesoftware/article/details/7057249

分析函数的over()部分的分析字句有3部分构成，分区语句，排序语句和窗口语句。
        （1）分区语句（partition by）：将查询结果分为不同的组，功能类似于group by语句,是分析函数工作的基础。默认是将所有结果作为一个分组。
        （2）排序语句(order  by)：将每个分区进行排序。
        （3）窗口语句：定义当前窗口，具体说是对每一个分组，按照给定的排序规则排序后，从分组的顶部到底部依次迭代，每次针对当前的行可以定义一个包含若干行的窗口。如果省略了窗口语句，默认使用从分组第一行到当前行的分组。

Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allowthe full syntax, including the windowing_clause.

--分析函数通常在数据仓库环境下使用，下表列出了所有的分析函数，其中加星号的支持全部语法，包括开窗选项。

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MIN *
NTH_VALUE*
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *

二． Analytic Functions 使用示例

下面主要介绍一下以下几个函数的使用方法

1.  Over()开窗函数

2.  Nvl()函数
3.  Rollup,Cube自动汇总函数
4.  Rank,Dense_rank,Row_number函数
5.  Lag , Lead函数
6.  Sum,Avg, Count, Max函数
7.  Ratio_to_report报表处理函数
8.  First,Last，First_value，Last_value取基数的分析函数

9.  Greatest,Least 函数

10. Trunc, Round,Decode, Substr函数

2.1 Over() 开窗函数

Over() 开窗函数是Oracle的分析函数，其语法如下：

函数名([ 参数]) over( [ 分区子句 ] [ 排序子句[ 滑动窗口子句 ] ])

分区子句类似于聚组函数所需要的group by，排序子句可看成是SQL语句中的orderby，只不过在此语句中还可指定null值排前（nullsfirst）还是排后（nulls last）。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary）按照salary排序进行累计，orderby是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unboundedpreceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unboundedpreceding and unbounded following）

2.2 Nvl() 函数

NVL（EXP1,EXP2），函数返回exp1和exp2中第一个不为空的值。

如果exp1为空则返回exp2，否则返回exp1。

注意：如果exp1不是字符串，那么返回的数据类型和exp1的数据类型相同，否则返回的数据类型为varchar2型。

SQL> select nvl('This is not null',7) Frist, nvl(null, 'My Oracle')  Secondfrom dual;

 FRIST            SECOND

------------------     -------------

This is not null     My Oracle

2.3  自动汇总函数rollup,cube

Rollup:表示的意思是：除了分组的功能外，还进行累加的的，多了一个汇总。

如果是 GROUP BYROLLUP(A, B, C) 的话， GROUP BY 顺序

(A 、B 、 C)

(A 、B)

(A)

最后对全表进行 GROUP BY 操作。

Cube提供了按照多字段汇总的功能。

如果是 GROUP BY CUBE(A,B, C) ， GROUP BY 顺序

(A 、B 、 C)

(A 、B)

(A 、C)

(A) ，

(B 、C)

(B)

(C) ，

最后对全表进行 GROUP BY 操作。

示例：