分析函数-CSDN博客

分析函数

——杨朔

分析函数是Oracle从8.1.6开始引入的一个全新的概念，用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。在分析函数出现以前，有时我们必须使用自联查询，子查询或者内联视图，甚至复杂的存储过程实现才能得到我们需要的查询结果。自从有了分析函数，我们实现这些功能往往只要一条简单的SQL语句就可以了，同时，在执行效率方面也有相当大的提高。

一、语法：

A、PARTITION子句：用于定义分组字段

B、ORDER BY子句：用于定义排序字段

C、WINDOWING子句：缺省时相当于RANGE UNBOUNDED PRECEDING

值域窗（RANGE WINDOW）

RANGE NPRECEDING

仅对数值或日期类型有效，选定窗为排序后当前行之前，某列（即排序列）值大于/小于（当前行该列值 –/+ N）的所有行，因此与ORDERBY子句有关系。

行窗（ROW WINDOW）

ROWS NPRECEDING

选定窗为当前行及之前N行。还可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND nFOLLOWING，表示每行对应的数据窗口是之前m行与之后n行内。

二、常用分析函数及用列

1、汇总函数

A．说明

Rollup()跟Cube()都是用于生成汇总数据的，它们之间的区别在于，如果对多个字段进行分组汇总，Rollup()只是按照字段顺序进行汇总，而Cube()对所有字段组合进行汇总。

B．Rollup

说明：对参数中的字段按照顺序进行汇总。

示例（SQL）：

C．Cube

说明：对参数中的所有字段组合进行汇总。

示例（SQL）：

2、排序函数

A．说明

排序函数分为两类：分配顺序号函数、计算组内位置分布函数。

分配顺序号函数有Row_number()、Rank()、Dense_Rank()，它们之间的区别在于：Row_number()在每组内是从1开始的顺序编号（连续且不重复）；Rank()采用跳跃排序，比如说有两个并列第二名，则下一个为第四名（没有第三名）；Dense_Rank()采用连续排序，比如说存在两个第二名，下一个继续跟着第三名。

计算组内位置分布函数有Cume_Dist()、Percent_Rank()，它们之间的区别为计算方式不一样，其中Cume_Dist()计算方法为：当前组内顺序/组内总行数，如果当前组内顺序和其它数据有重复，则按照下一个顺序减一计算，其值范围为（0,1］；Percent_Rank()计算方法为：当前组内顺序减一/组内总行数减一，其值范围为［0,1）。组内顺序值参考Rank()。

B．Row_number

说明：对按照指定属性分组后的数据进行顺序排定，该函数产生的顺序号是连续不重复的，如果排序字段数值重复，则按照RowID进行排序。

示例（SQL）：

C．Rank

说明：对按照指定属性分组后的数据进行顺序排定，如果排序字段数值重复，这些重复数值对应的纪录顺序号一样，而后边的顺序号根据前边重复情况自动跳跃。

示例（SQL）：

D．Dense_Rank

说明：对按照指定属性分组后的数据进行顺序排定，如果排序字段数值重复，这些重复数值对应的纪录顺序号一样，而后边的顺序号跟前边顺序号连续。

示例（SQL）：

E．Cume_Dist、Percent_Rank

说明：Cume_Dist相当于：1 – (Rank()[Desc] – 1) / Count()

Percent_Rank相当于：(Rank() – 1) / (Count() – 1)

示例（SQL）：

3、使用其它行数据函数

A．说明

Lag()、Lead()函数都是可以取其它行数据来参与本行数据运算的分析函数，其区别在于Lag()是取当前行前边的数据，而Lead()是取当前行之后的数据。

B．Lag

说明：当前函数参数形式为Lag(express,offset,default)，其中express：表达式（字段或公式等）；offset：向前偏移行数；default：找不到值的缺省值。

示例（SQL）：

C．Lead

说明：当前函数参数形式为Lead(express,offset,default)，其中express：表达式（字段或公式等）；offset：向后偏移行数；default：找不到值的缺省值。

示例（SQL）：

4、移动计算数据函数

A．说明

B．Sum

说明：组内之前所有行数据的总和。

示例（SQL）：

关于行窗、值域窗的示例(当前行与前三行汇总)

对于行窗来说，之前的n(指定值)行前的数据对于当前行来说是不可见的，不参与当前行的聚合运算。

C．Avg

说明：组内之前所有行数据的平均值。

示例（SQL）：

D．Max、Min

说明：组内之前所有行数据的最大（小）值。

示例（SQL）：

5、 ratio_to_report报表处理函数

A．说明

B．ratio_to_report

说明：计算当前数据占组类数据之和的百分比。

示例（SQL）：

6、取基数的分析函数

A．说明

First与Last是从DENSE_RANK返回的集合中取出排序序号最小（大）的一个值的行，因为在值相同时可能造成同一序号对应多行情况，因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

First_Value与Last_Value返回组中的第（最后）一个值。

B．First、Last

说明：

示例（SQL）：

C．First_Value、Last_Value

说明：在使用分析函数的时候，缺省的WINDOWING范围是RANGE BETWEENUNBOUNDED PRECEDING AND CURRENT ROW，在使用Last_Value的时候很多人可能不能得到预期效果，其实是因为每次比较的时候从当前行向前进行比较造成的。

示例（SQL）：

7、标准差、方差、协方差函数及Corr

A．说明

标准差函数包括标准差Stddev()、总体标准差Stddev_Pop()、样本标准差Stddev_Samp()；方差函数包括方差Variance()、总体方差Var_Pop()、样本方差Var_Samp()；协方差函数包括总体协方差Covar_Pop()、样本协方差Covar_Samp()。Corr函数相当于表达式COVAR_POP(expr1,expr2)/ (STDDEV_POP(expr1) * STDDEV_POP(expr2))

B．标准差函数：Stddev()、Stddev_Pop()、Stddev_Samp()

说明：

示例（SQL）：

C．方差函数：Variance()、Var_Pop()、Var_Samp()

说明：

示例（SQL）：

D．协方差函数：Covar_Pop()、Covar_Samp()

说明：

示例（SQL）：

E．Corr()

说明：返回一对表达式的相关系数，它是如下的缩写：COVAR_POP(expr1,expr2)/(STDDEV_POP(expr1)*STDDEV_POP(expr2))。从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数, 相关系数给出了关联的强度，0表示不相关。

示例（SQL）：