Oracle 分析函数

-- Start

说起 Oracle 分析函数，可以用很好很强大来形容。这项功能特别适用于各种统计查询，这些查询用通常的SQL很难实现，或者根本就无发实现。首先，我们从一个简单的例子开始，来一步一步揭开它神秘的面纱，请看下面的SQL：

CREATE TABLE EMPLOY
(
    NAME    VARCHAR2(10),  --姓名
    DEPT    VARCHAR2(10),  --部门
    SALARY  NUMBER         --工资
);

INSERT INTO EMPLOY VALUES ('张三','市场部',4000);
INSERT INTO EMPLOY VALUES ('赵红','技术部',2000);
INSERT INTO EMPLOY VALUES ('李四','市场部',5000);
INSERT INTO EMPLOY VALUES ('李白','技术部',5000);
INSERT INTO EMPLOY VALUES ('王五','市场部',NULL);
INSERT INTO EMPLOY VALUES ('王蓝','技术部',4000);

SELECT
    ROW_NUMBER() OVER(ORDER BY SALARY) AS 序号,
    NAME                               AS 姓名,
    DEPT                               AS 部门,
    SALARY                             AS 工资
FROM EMPLOY;
 
查询结果如下：
 
序号       姓名       部门      工资
1          赵红       技术部    2000
2          张三       市场部    4000
3          王蓝       技术部    4000
4          李四       市场部    5000
5          李白       技术部    5000
6          王五       市场部    (null)

看到上面的ROW_NUMBER() OVER()了吗？很多人非常不理解，怎么两个函数能这么写呢？甚至有人怀疑上面的SQL语句是不是真的能执行。其实，ROW_NUMBER是个函数没错，它的作用从它的名字也可以看出来，就是给查询结果集编号。但是，OVER并不是一个函数，而是一个分析语句，它的作用是定义一个作用域（或者可以说是结果集），OVER前面的函数只对OVER定义的结果集起作用。怎么样，不明白？没关系，我们后面还会详细介绍。

从上面的SQL我们可以看出，典型的 Oracle 在线分析处理的格式包括两部分：函数部分和OVER分析语句部分。那么，函数部分可以有哪些函数呢？如下：

ROW_NUMBER         	给查询结果集编行号
RANK               	给查询结果集编排名
DENSE_RANK         	给查询结果集编排名
MIN                	求最小值
MAX                	求最大值
AVG                	求平均值
SUM                	求总和
COUNT              	求结果集行数
FIRST_VALUE        	求最小值
LAST_VALUE         	求最大值
FIRST              	求最小值， 配合 DENSE_RANK 使用
LAST               	求最大值， 配合 DENSE_RANK 使用
LAG                	向下偏移
LEAD               	向上偏移
LISTAGG            	连接列
NTILE              	平分组
NTH_VALUE          	返回第 n 行的值
VARIANCE           	方差
VAR_POP            	总体方差
VAR_SAMP           	样本方差
STDDEV             	标准偏差
STDDEV_POP         	总体标准偏差
STDDEV_SAMP        	样本标准偏差
CORR               	协方差
COVAR_POP          	总体协方差
COVAR_SAMP         	样本协方差
CUME_DIST          	计算积分分布
PERCENT_RANK       	和 CUME_DIST 类似
PERCENTILE_CONT    	计算值的连续分布模型
PERCENTILE_DISC    	计算值的不连续分布模型
RATIO_TO_REPORT    	计算比率
REGR_SLOPE         	线性回归
REGR_INTERCEPT     	线性回归
REGR_COUNT         	线性回归
REGR_R2            	线性回归
REGR_AVGX          	线性回归
REGR_AVGY          	线性回归
REGR_SXX           	线性回归
REGR_SYY           	线性回归
REGR_SXY           	线性回归

上面这些函数的作用，我会在后面逐步给大家介绍，大家