DB2 在线分析处理（OLAP函数的使用）

----start

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

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

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

 

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

ROW_NUMBER RANK DENSE_RANK FIRST_VALUE LAST_VALUE LAG LEAD COUNT MIN MAX AVG SUM 

上面这些函数的作用，我会在后面逐步给大家介绍，大家可以根据函数名猜测一下函数的作用。

 

假设我想在不改变上面语句的查询结果的情况下，追加对部门员工的平均工资和全体员工的平均工资的查询，怎么办呢？用通常的SQL很难查询，但是用OLAP函数则非常简单，如下SQL所示：

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