oracle分析函数学习

一、分析函数的语法

FUNCTION_NAME（<参数>，…）

OVER （<PARTITION BY 表达式，…> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>）

 

1、FUNCTION_NAME（<参数>，…）

在后面介绍各个分析函数的用途。

2、OVER关键字

over只是一个关键字，标识这是一个分析函数。

3、PARTITION子句

分析函数以什么条件分组计算，相当于group by的作用，但是分析函数不会把结果集聚合，而是以原始记录方式显示每行的计算结果。缺省该子句表示整个记录集作为一组计算。

4、ORDER BY子句

分析函数中的order by 子句和标准SQL中的order by 子句类似，表示组内以什么条件排序，asc和desc表示排序的方向，nulls first和nulls last表示空值的排序位置。

5、WINDOWING子句

默认的窗口是：当有ORDER BY子句的时候表示从当前分区的第一行到当前行；当没有ORDER BY子句的时候表示整个分组。

窗口函数有2种方式，但是必须有ORDER BY子句时才能使用窗口函数。

a、  值域窗（RANGE WINDOW），逻辑偏移

       RANGE 表达式 PRECEDING ，当前组中当前行的前N行开始到当前行的记录集。排序列和表达式都只能是数值或间隔日期，选定窗为排序后当前行之前，排序列（使用这种窗口函数时只能有一个排序列）值大于/小于（当前行该列值 –/+表达式）的所有行，因此与ORDER BY子句有关系。是以排序列计算窗口范围。

       以下2种情况可以有多个排序列：

       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

b、  行窗（ROW WINDOW），物理偏移

ROWS 表达式 PRECEDING，表达式必须是一个正的数字类型。以排序的结果顺序计算偏移当前行的起始行。

除了上面的PRECEDING关键字外，还有CURRENT ROW表示当前行，FOLLOWING表示当前行之后N行，还可以用BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING表示前m行到后n行的记录集计算。如果不是BETWEEN AND形式，则oracle会认为窗口函数只写了起始行，而当前行默认是终止行。所以FOLLOWING关键字只能用在BETWEEN AND中。

 

二、分析函数的简介

AVG （<distinct | all> expr）	一组或选定窗中表达式的平均值,添加distinct去重取平均值。
CORR （expr， expr）	即COVAR_POP（exp1，exp2） / （STDDEV_POP（expr1） * STDDEV_POP（expr2）），两个表达式的互相关，-1（反相关） ~ 1（正相关），表示不相关
COUNT （<distinct> <*> <expr>）	计数,添加distinct去重计数。
COVAR_POP （expr， expr）	总体协方差
COVAR_SAMP （expr， expr）	样本协方差
CUME_DIST	累积分布，即行在组中的相对位置，返回0 ~ 1
DENSE_RANK	行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数
FIRST_VALUE	一个组的第一个值
LAG （expr, <offset>，<default>）	访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）
LAST_VALUE	一个组的最后一个值
LEAD （expr，<offset>，<default>）	访问之后的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如最后行不存在之前行）
MAX (expr）	最大值
MIN （expr）	最小值
NTILE （expr）	按表达式的值和行在组中的位置编号，如表达式为4，则组分4份，分别为1 ~ 4的值，而不能等分则多出的部分在值最小的那组
PERCENT_RANK	类似CUME_DIST，1/（行的序数 - 1）
RANK	相对序数，允许并列，并空出随后序号
RATIO_TO_REPORT （expr）	表达式值 / SUM（表达式值），当前值占当前分组的比例。
REGR_ xxxx （expr， expr）	线性回归函数
ROW_NUMBER	排序的组中行的偏移
STDDEV （expr）	标准差
STDDEV_POP （expr）	总体标准差
STDDEV_SAMP （expr）	样本标准差
SUM （expr）	合计
VAR_POP （expr）	总体方差
VAR_SAMP （expr）	样本方差
VARIANCE （expr）	方差

 

三、聚合函数的特殊关键字KEEP

聚合函数 MIN, MAX, SUM, AVG, COUNT, VARIANCE,和 STDDEV, 当使用 KEEP 时和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可以接over（）分析函数部分。

Min（col2）keep（dense_rank first order by col1）保留按col1排名第一的col2的最小值。

Min（col2）keep（dense_rank first order by col1）over (partition by col3) 按col3分组保留按col1排名各组第一的col2的最小值。