DB2 OLAP函数的使用

转载 2009年12月02日 15:12:00

----start

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

 

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

 

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

 

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

 

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

 

请注意序号和部门序号之间的区别,我们在查询部门序号的时候,在OVER表达式中多了两个子句,分别是PARTITION BY ORDER BY。它们有什么作用呢?在介绍它们的作用之前,我们先来回顾一下OVER的作用,还记得吗?

 

OVER是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),OVER前面的函数只对OVER定义的结果集起作用。

 

ORDER BY的作用大家应该非常熟悉,用来对结果集排序。PARTITION BY的作用其实也很简单,和GROUP BY 的作用相同,用来对结果集分组。

 

  到此为止,大家应该对OLAP函数的套路有一定的了解和体会了吧。大家看一下上面SQL的结果集,发现王五的工资是null,当我们按工资排序时,null被放到最后,我们想把null放在前边该怎么办呢?使用NULLS FIRST关键字即可,默认是NULLS LAST请看下面的SQL

 

请注意ROW_NUMBERRANK之间的区别,RANK是等级,排名的意思,李四和李白的工资都是5000,他们并列排名第二。张三和王蓝的工资都是4000,怎么RANK函数的排名是第四,而DENSE_RANK的排名是第三呢?这正是这两个函数之间的区别。由于有两个第二名,所以RANK函数默认没有第三名。

 

  现在又有个新问题,假设让你查询一下每个员工的工资以及工资小于他的所有员工的平均工资,该怎么办呢?怎么?没听明白问题?不要紧,请看下面的SQL

 

上面SQL 中的OVER部分出现了一个ROWS子句,我们先来看一下ROWS子句的结构:

 

注意,以上关键字都是相对当前行的,UNBOUNDED PRECEDING表示当前行前面的所有行,也就是说没有上限;<number>  PRECEDING表示从当前行开始到它前面的<number>行为止,例如,number=2,表示的是当前行前面的2行;CURRENT ROW表示当前行。至于其它两个关键字,我想,不用我说,你也应该知道了吧。如果你还不明白,请仔细分析上面SQL的查询结果。

 

  OVER表达式还可以有个子句,那就是RANGE,它的使用方式和ROWS 十分相似,或者说一模一样,作用也差多不,不过有点区别,如下所示:

 

RANGE BETWEEN <上限条件> AND <下限条件>

 

其中的<上限条件> <下限条件>ROWS一模一样,如下的SQL演示它们之间的区别:

 

上面SQLRANGE 子句的作用是定义一个工资范围,这个范围的上限是当前行的工资-500,下限是当前行工资+500。例如:李四的工资是3000,所以上限是3000-500=2500,下限是3000+500=3500,那么有谁的工资在2500-3500这个范围呢?只有李四和李白,所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWSRANGE得区别。

 

  上面的SQL 还用到了FIRST_VALUELAST_VALUE两个函数,它们的作用也非常简单,用来求OVER 定义集合的最小值和最大值。值得注意的是这两个函数有个参数,'IGNORE NULLS' 'RESPECT NULLS',它们的作用正如它们的名字一样,用来忽略NULL值和考虑NULL值。

 

  还有两个函数我们没有介绍,LAGLEAD这两个函数的功能非常强大,请看下面SQL

 

  我们先来看一下LAG LEAD 函数的声明,如下:

 

LAG(表达式或字段, 偏移量, 默认值, IGNORE NULLSRESPECT NULLS)

 

LAG是向下偏移,LEAD是想上偏移,大家看一下上面SQL的查询结果就一目了然了。

 

  到此为止,有关DB2 OLAP 函数的所有知识都介绍给大家了,下面我们再次回顾一下 DB2 在线分析处理 的组成部分,如下:

 

函数 OVER(PARTITION BY 子句 ORDER BY 子句 ROWSRANGE子句)

 

要想熟练掌握这些知识还需要一定的时间和练习,一旦你掌握了,你将拥有一项绝世武学,可以纵横DB2

 

相关文章推荐

DB2中的ROW_NUMBER() OVER()函数用法

row_number() over()分组排序功能:      在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by  ord...

db2 row_number() over( partition by col1 order by col2 ) 和关于fetch first n row only 的诡异

1.db2  row_number() over() 中 partition by  用于给结果集分组 seletc row_number() over(partition by log_user_i...

DB2 在线分析处理(OLAP函数的使用)

之前用过Oracle的分析函数 row_number() over(partition by column order by column) rn 来进行数据分析,没想到DB2也是支持的,转载一篇详细...

使用 DB2 UDB OLAP 函数

在线分析处理(online analytical processing,OLAP)函数非常灵活,也很强大。通过使用这些函数,您可以为一些问题找到简单的解决方案,而这些问题本来要么是要迭代一个或者多个游...

DB2中OLAP函数

  • 2011-09-13 09:01
  • 57KB
  • 下载

DB2的olap函数详解(转)

DB2的olap函数详解 转载出处 http://czmmiao.iteye.com/blog/1387755  作者czmmiao DB2 olap函数概述  在线分析处理...

DB2 OLAP Server - 理论与实践

  • 2009-03-20 14:26
  • 3.10MB
  • 下载

DB2数值函数简介及使用

  • 2013-06-27 08:54
  • 12KB
  • 下载

DB2日期时间函数简介及使用

1、year(exp) :取exp的year部分。  参数:date、timestamp类型,日期间隔,时间戳间隔,        或者一个有效的date或者timestamp字符串(非CLOB类...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)