当前位置: 传客网 > DB2数据库OLAP函数使用

DB2数据库OLAP函数使用

2016-11-01 作者:luosw

说起DB2在线分析处理,真是功能很强大,适用于各种统计查询,这些查询用通常的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);

查询结果:

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

从上面的SQL可以看出,典型的DB2在线分析处理的格式包括两部分:函数部分(ROW_NUMBER、RANK、DENSE_RANK、COUNT、MIN、MAX、AVG、SUM)和OVER(PARTITIONBY子句 ORDER BY 子句 ROWS或RANGE子句)表达式部分,接下来一一举例说明。

假如现在需要对结果集追加对部门员工的平均工资和全体员工的平均工资,用通常的SQL很难查询,但是用OLAP函数则非常简单:
  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);

查询结果:


  请注意序号和部门序号之间的区别,在查询部门序号的时候OVER表达式中多了两个子句,分别是PARTITION BY和ORDER BY。在介绍它们作用之前先来回顾一下OVER的作用:OVER是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),OVER前面的函数只对OVER定义的结果集起作用。ORDER BY子句此处是用来对OVER结果集排序。PARTITION BY和GROUP BY的作用相同,在此处是用于对OVER结果集分组。

从上面SQL结果集还能发现王五的工资是null,当按工资排序时被放到最后,如果想把null值排序时放前边该怎么办?使用NULLS FIRST关键字即可(默认是NULLS LAST),请看例子:
  SELECT
      ROW_NUMBER() OVER(ORDER BY SALARY desc NULLS FIRST) AS RN,
      RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS RK,
      DENSE_RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS D_RK,
      NAME AS 姓名,DEPT AS 部门,SALARY AS 工资
  FROM
  (
      --姓名    部门  工资
      VALUES
      ('张三','市场部',4000),
      ('赵红','技术部',2000),
      ('李四','市场部',5000),
      ('李白','技术部',5000),
      ('王五','市场部',NULL),
      ('王蓝','技术部',4000)
  ) AS EMPLOY(NAME,DEPT,SALARY);

查询结果:


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

接下来看了解ROWS子句,ROWS子句的结构:ROWS BETWEEN <上限条件> AND <下限条件>。
其中“上限条件”可以是如下关键字:UNBOUNDED PRECEDING、  PRECEDING、CURRENT ROW,
“下线条件”可以是如下关键字:CURRENT ROW、 FOLLOWING、UNBOUNDED FOLLOWING。
上述关键字都是相对当前行的,CURRENT ROW表示当前行;UNBOUNDED PRECEDING表示当前行前面的所有行,也就是说没有上限; PRECEDING表示从当前行开始到它前面的行为止,例如,number=2,表示的是当前行前面的2行;至于其它两个关键字就不再阐述了。请看例子:
  SELECT
      NAME AS 姓名,SALARY AS 工资,
      SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 小于本人工资的总额,
      SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS 大于本人工资的总额,
      SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 工资总额1,
      SUM(SALARY) OVER() AS 工资总额2
  FROM
  (
      --姓名    部门  工资
      VALUES
      ('张三','市场部',4000),
      ('赵红','技术部',2000),
      ('李四','市场部',5000),
      ('李白','技术部',5000),
      ('王五','市场部',NULL),
      ('王蓝','技术部',4000)
  ) AS EMPLOY(NAME,DEPT,SALARY);

查询结果:


  OVER表达式还有个RANGE子句,使用方式和ROWS十分相似,其作用也差不多:RANGE BETWEEN <上限条件> AND <下限条件> (其<上限条件>、<下限条件>与ROWS子句一样)。请看例子:
  SELECT
      NAME AS 姓名,DEPT AS 部门,SALARY AS 工资,
      SUM(SALARY) OVER(ORDER BY SALARY ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ROWS,
      SUM(SALARY) OVER(ORDER BY SALARY RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING) AS RANGE
  FROM
  (
      --姓名    部门  工资
      VALUES
      ('张三','市场部',2000),
      ('赵红','技术部',2400),
      ('李四','市场部',3000),
      ('李白','技术部',3200),
      ('王五','市场部',4000),
      ('王蓝','技术部',5000)
  ) AS EMPLOY(NAME,DEPT,SALARY);


   RANGE子句的作用是定义一个工资范围,这个范围的上限是当前行的工资-500,下限是当前行工资+500。例如:李四的工资是3000,所以上限是3000-500=2500,下限是3000+500=3500,那么谁的工资在2500-3500这个范围呢?只有李四和李白,所以RANGE列的值就是3000(李四)+3200(李白)=6200。而此处rows子句,李四的工资是3000,他前一条,后一条分别为赵红2400、李白3200,所以ROWS列的值:3000(李四)+2400(赵红)+3200(李白)=8600 。以上就是ROWS和RANGE得区别。



相关推荐

  • Twitter 开源其使用的 MySQL数据库

    湖北指数武汉PHP实训MySQL是被广泛使用的一个数据库,但是Web开发人员会在大规模访问时的数据库扩展以及性能方面遇到问题。这也是近年来NoSQL数据库开始出现并逐渐繁荣的一个原因。对于关注MySQL可扩展性的DBA人员来说,当然非常希望

  • PHP使用 MySQL 数据库

    用PHP使用MySQL数据库在ASP中,如果是ACCESS数据库你可以直接打开ACCESS来编辑MDB文件,如果是SQLSERVER你可以打开企业管理器来编辑SQLSERVER数据库,但是在PHP中,MYSQL的命令行编辑可能会令初学者感到

  • Bugzilla使用远程mysql数据库

    1、设置mysql,使其支持远程访问见:Mysql数据库远程访问2、配置bugzilla,使其使用远程数据库2.1修改localconfig文件(/var/www/bugzilla)注:如是用命令安装localconfig文件是在/etc/

  • VC中使用ADO访问数据库技术程序实现方法

    一,数据库访问技术目前数据库接口问技术主要有DAO,RDO,ODBC,OLEDB和ADOODBC,开发数据库互连。为数据库开发提供统一的接口,可以与任何具有ODBC驱动程序的数据库通信。和其他数据库访问技术相比,属于比较底层的数据库接口。只

  • Android Studio平台使用GreenDao操作数据库

    一.GreenDao是什么?简单说就是一个可以方便操作SQLite数据库的第三方库;二.使用流程1.创建一个java程序;2.在java程序中书写代码,运行后会生成一系列实体类和其他类(详细下面会说),代码中指定生成的类的路径、数据库表的字

  • vb使用adodb操作数据库常用方法

    ADO常用方法下面是我所掌握的使用ADO对数据库操作的一些常用方法,主要是提供给初学者作为参考,有不对的地方请指正。如有补充不胜荣幸准备工作========DimconnAsNewADODB.Connection'创建一个Connectio

  • 使用POWERDESIGNER设计数据库的20条技巧(转)

    使用POWERDESIGNER设计数据库的20条技巧1、PowerDesigner使用MySQL的auto_increment◇问题描述:PD怎样能使主键id使用MySQL的auto_increment呢?◇解决方法

  • vc6.0 中使用ADO连接数据库

    今天尝试使用ADO连接数据库时,死活连接不上,一直运行异常。开始以为是打开连接方法写的有问题,百度上各种方法都试了也不行。还好有万能的师兄!!!解决办法:原来我没有加载COM库,加载方法为在xxxAPP类的InitialInstance方法

  • 使用asm创建数据库

    自动存储管理(ASM)ASM是Oracle数据库10g中一个非常出色的新特性,它以平台无关的方式提供了文件系统、逻辑卷管理器以及软件RAID等服务。ASM可以条带化和镜像磁盘,从而实现了在数据库被加载的情况下添加或移除磁盘以及自动平衡I/O

  • statspack 使用实例_数据库教程_编程技术

      攻力擅于长吻煤泥查夜死力故意品种;古圣四明勒戒水利保宁联众华商股息statspack使用实例_数据库教程_编程技术。波澜柴墙东面东楼新行隆成博施莫明;俊丽落草怪兽赅博苦菜炉条乱射欠产。行馆清谈类群留心闪过赤子光身喧闹!启母毛虾棚车配戏安

  • 使用JavaBean进行数据库的连接

    本例中使用的数据库是SQLServer2000Java源文件:packagejdbc;importjava.sql.*;publicclassJDBCBean{StringdbDriver="com.microsoft.jdbc.sqlse

  • vc使用ODBC读excel数据库错误,找不到表的解决办法(转)

    前一阵子学习了徐景周老师的用ODBC读写excel的代码。原封不动抄下来,的确很好用,但是真正以应用到自己的需要,就出现问题了。只能按照原程序的顺序,先写一个excel再读取。当读取自己的excel时候,总是出现,数据库错误,找不到表的提示

  • WinccV7.3 使用VBS判断数据库Mydatabase下是否存在数据表Mytable

    SubOnClick(ByvalItem)Dimors,conn,con,ssql,ocomDimors1,conn1,con1,ssql1,ocom1Dimocatalog,otableDimi,TableExistDimPCNamePC

  • VC++下使用ADO编写数据库程序

    C++连接数据库代码可以考虑一下IntegratedSecurity=SSPI好像是windows身份验证吧,你之后的"UserID=sa;pwd=;"则是SQLServer身份验证,试一下去除"IntegratedSecurity=SSP

  • [推荐]Java中使用JDBC连接数据库例程与注意事项

    importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.util.ArrayList;importjava

  • 使用JMeter创建数据库测试

    好吧!我一直觉得我不聪明,所以,我用最详细,最明了的方式来书写这个文章。我相信,我能明白的,你们一定能明白。我的环境:MySQL:mysql-essential-5.1.51-win32     jdbc驱动:我已经上传到csdn上一个:h

  • VC++下使用ADO编写数据库程序

    准备:(1)、引入ADO类#import"c:\programfiles\commonfiles\system\ado\msado15.dll"\no_namespace\rename("EOF","adoEOF")(2)、初始化COM在M

  • 使用JMeter创建数据库(Mysql)测试

    我的环境:MySQL:mysql-essential-5.1.51-win32     jdbc驱动:我已经上传到csdn上一个:http://download.csdn.net/source/3451945     JMeter:jmet

  • Python 使用函数默认值来实现函数静态变量的功能

    =====python使用函数默认值来实现函数静态变量的功能=====Python中是不支持静态变量的,但是我们可以通过函数的默认值来实现静态变量的功能。\\**这是因为python中函数的默认值只会被执行一次,(和静态变量一样,静态变量初

  • 使用SQL Server数据转换服务升迁Access数据库

    开发者常常以Access作为原型或者用Access来开发不是很关键的应用程序。但是,随着公司业务的增长,要解决的问题会变得越来越复杂,Access环境可能无法满足需要。目前,Access2002的.mdb和.adp文件都将一个数据库的长度限

返回
顶部