DB2数据库OLAP函数使用

db2 olap it

说起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时候,总是出现,数据库错误,找不到表的提示

你的评论

就没有什么想说的吗?

最新博客

关于我们 免责声明 移动版

©2017传客网    琼ICP备15003173号-2    

本站部分文章来源于互联网,版权归属于原作者。
本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长(weishubao@126.com),我们收到后立即删除。
站内所有资源仅供学习与参考,请勿用于商业用途,否则产生的一切后果将由您自己承担!

X