`
you
  • 浏览: 10827 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

DB2 OLAP函数的使用(转)

阅读更多

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

  1. SELECT  
  2.     ROW_NUMBER() OVER(ORDER BY SALARY) AS 序号,  
  3.     NAME AS 姓名,  
  4.     DEPT AS 部门,  
  5.     SALARY AS 工资  
  6. FROM  
  7. (  
  8.     --姓名    部门  工资  
  9.     VALUES  
  10.     ('张三','市场部',4000),  
  11.     ('赵红','技术部',2000),  
  12.     ('李四','市场部',5000),  
  13.     ('李白','技术部',5000),  
  14.     ('王五','市场部',NULL),  
  15.     ('王蓝','技术部',4000)  
  16. ) AS EMPLOY(NAME,DEPT,SALARY);  
  17.    
  18. 查询结果如下:  
  19.    
  20. 序号       姓名       部门       工资  
  21. 1     赵红       技术部    2000  
  22. 2     张三       市场部    4000  
  23. 3     王蓝       技术部    4000  
  24. 4     李四       市场部    5000  
  25. 5     李白       技术部    5000  
  26. 6     王五       市场部    (null)  

 

 

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

 

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

  1. ROW_NUMBER  
  2. RANK  
  3. DENSE_RANK  
  4. FIRST_VALUE  
  5. LAST_VALUE  
  6. LAG  
  7. LEAD  
  8. COUNT  
  9. MIN  
  10. MAX  
  11. AVG  
  12. SUM  

 

 

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

 

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

  1. SELECT  
  2.     ROW_NUMBER() OVER() AS 序号,  
  3.     ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARY) AS 部门序号,  
  4.     NAME AS 姓名,  
  5.     DEPT AS 部门,  
  6.     SALARY AS 工资,  
  7.     AVG(SALARY) OVER(PARTITION BY DEPT) AS 部门平均工资,  
  8.     AVG(SALARY) OVER() AS 全员平均工资  
  9. FROM  
  10. (  
  11.     --姓名    部门  工资  
  12.     VALUES  
  13.     ('张三','市场部',4000),  
  14.     ('赵红','技术部',2000),  
  15.     ('李四','市场部',5000),  
  16.     ('李白','技术部',5000),  
  17.     ('王五','市场部',NULL),  
  18.     ('王蓝','技术部',4000)  
  19. ) AS EMPLOY(NAME,DEPT,SALARY);  
  20.    
  21.    
  22. 查询结果如下:  
  23.    
  24. 序号       部门序号       姓名       部门       工资       部门平均工资       全员平均工资  
  25. 1            1          张三       市场部    4000       4500                     4000  
  26. 2            2          李四       市场部    5000       4500                     4000  
  27. 3            3          王五       市场部    (null)     4500                     4000  
  28. 4            1          赵红       技术部    2000       3666                     4000  
  29. 5            2          王蓝       技术部    4000       3666                     4000  
  30. 6            3          李白       技术部    5000       3666                     4000  

 

 

请注意序号和部门序号之间的区别,我们在查询部门序号的时候,在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:

  1. SELECT  
  2.     ROW_NUMBER() OVER(ORDER BY SALARY desc NULLS FIRST) AS RN,  
  3.     RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS RK,  
  4.     DENSE_RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS D_RK,  
  5.     NAME AS 姓名,  
  6.     DEPT AS 部门,  
  7.     SALARY AS 工资  
  8. FROM  
  9. (  
  10.     --姓名    部门  工资  
  11.     VALUES  
  12.     ('张三','市场部',4000),  
  13.     ('赵红','技术部',2000),  
  14.     ('李四','市场部',5000),  
  15.     ('李白','技术部',5000),  
  16.     ('王五','市场部',NULL),  
  17.     ('王蓝','技术部',4000)  
  18. ) AS EMPLOY(NAME,DEPT,SALARY);  
  19.    
  20. 查询结果如下:  
  21.    
  22. RN  RK   D_RK     姓名       部门       工资  
  23. 1     1     1     王五       市场部    (null)  
  24. 2     2     2     李四       市场部    5000  
  25. 3     2     2     李白       技术部    5000  
  26. 4     4     3     张三       市场部    4000  
  27. 5     4     3     王蓝       技术部    4000  
  28. 6     6     4     赵红       技术部    2000  

 

 

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

 

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

  1. SELECT  
  2.     NAME AS 姓名,  
  3.     SALARY AS 工资,  
  4.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 小于本人工资的总额,  
  5.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS 大于本人工资的总额,  
  6.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 工资总额1,  
  7.     SUM(SALARY) OVER() AS 工资总额2  
  8. FROM  
  9. (  
  10.     --姓名    部门  工资  
  11.     VALUES  
  12.     ('张三','市场部',4000),  
  13.     ('赵红','技术部',2000),  
  14.     ('李四','市场部',5000),  
  15.     ('李白','技术部',5000),  
  16.     ('王五','市场部',NULL),  
  17.     ('王蓝','技术部',4000)  
  18. ) AS EMPLOY(NAME,DEPT,SALARY);  
  19.    
  20. 查询结果如下:  
  21.    
  22. 姓名       工资       小于本人工资的总额    大于本人工资的总额    工资总额1     工资总额2  
  23. 王五       (null)     (null)             20000              20000            20000  
  24. 赵红       2000       2000               20000              20000            20000  
  25. 张三       4000       6000               18000              20000            20000  
  26. 王蓝       4000       10000              14000              20000            20000  
  27. 李四       5000       15000              10000              20000            20000  
  28. 李白       5000       20000              5000               20000            20000  

 

 

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

  1. ROWS BETWEEN <上限条件> AND <下限条件>  
  2.    
  3. 其中“上限条件”可以是如下关键字:  
  4. UNBOUNDED PRECEDING  
  5. <number>  PRECEDING  
  6. CURRENT ROW  
  7.    
  8. “下线条件”可以是如下关键字:  
  9. CURRENT ROW  
  10. <number> FOLLOWING  
  11. UNBOUNDED FOLLOWING  

 

 

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

 

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

 

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

 

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

  1. SELECT  
  2.     NAME AS 姓名,  
  3.     DEPT AS 部门,  
  4.     SALARY AS 工资,  
  5.     FIRST_VALUE(SALARY, 'IGNORE NULLS') OVER(PARTITION BY DEPT) AS 部门最低工资,  
  6.     LAST_VALUE(SALARY, 'RESPECT NULLS') OVER(PARTITION BY DEPT) AS 部门最高工资,  
  7.     SUM(SALARY) OVER(ORDER BY SALARY ROWS BETWEEN 1 PRECEDING  AND 1 FOLLOWING) AS ROWS,  
  8.     SUM(SALARY) OVER(ORDER BY SALARY RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING) AS RANGE  
  9. FROM  
  10. (  
  11.     --姓名    部门  工资  
  12.     VALUES  
  13.     ('张三','市场部',2000),  
  14.     ('赵红','技术部',2400),  
  15.     ('李四','市场部',3000),  
  16.     ('李白','技术部',3200),  
  17.     ('王五','市场部',4000),  
  18.     ('王蓝','技术部',5000)  
  19. ) AS EMPLOY(NAME,DEPT,SALARY);  
  20.    
  21. 查询结果如下:  
  22.    
  23. 姓名       部门       工资       部门最低工资       部门最高工资       ROWS    RANGE  
  24. 张三       市场部    2000       2000              4000             4400       4400  
  25. 赵红       技术部    2400       2400              5000             7400       4400  
  26. 李四       市场部    3000       2000              4000             8600       6200  
  27. 李白       技术部    3200       2400              5000             10200     6200  
  28. 王五       市场部    4000       2000              4000             12200     4000  
  29. 王蓝       技术部    5000       2400              5000             9000       5000  

 

 

上面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值。

 

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

  1. SELECT  
  2.     NAME AS 姓名,  
  3.     SALARY AS 工资,  
  4.     LAG(SALARY,0) OVER(ORDER BY SALARY) AS LAG0,  
  5.     LAG(SALARY) OVER(ORDER BY SALARY) AS LAG1,  
  6.     LAG(SALARY,2) OVER(ORDER BY SALARY) AS LAG2,  
  7.     LAG(SALARY,3,0,'IGNORE NULLS') OVER(ORDER BY SALARY) AS LAG3,  
  8.     LAG(SALARY,4,-1,'RESPECT NULLS') OVER(ORDER BY SALARY) AS LAG4,  
  9.     LEAD(SALARY) OVER(ORDER BY SALARY) AS LEAD  
  10. FROM  
  11. (  
  12.     --姓名    部门  工资  
  13.     VALUES  
  14.     ('张三','市场部',2000),  
  15.     ('赵红','技术部',2400),  
  16.     ('李四','市场部',3000),  
  17.     ('李白','技术部',3200),  
  18.     ('王五','市场部',4000),  
  19.     ('王蓝','技术部',5000)  
  20. ) AS EMPLOY(NAME,DEPT,SALARY);  
  21.    
  22. 查询结果如下:  
  23.    
  24. 姓名       工资       LAG0      LAG1      LAG2      LAG3      LAG4      LEAD  
  25. 张三       2000       2000      (null)   (null)       0       -1        2400  
  26. 赵红       2400       2400       2000    (null)       0       -1        3000  
  27. 李四       3000       3000       2400     2000       0        -1        3200  
  28. 李白       3200       3200       3000     2400       2000     -1        4000  
  29. 王五       4000       4000       3200     3000       2400     2000      5000  
  30. 王蓝       5000       5000       4000     3200       3000     2400      (null)  

 

 

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

 

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

 

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

 

函数 OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句)

OLAP函数的应用

上文已经介绍了OLAP函数的基本使用方法,这里介绍OLAP函数的相关应用

    SELECT 
        date AS 日期, 
        tax AS 税收, 
        sum(tax) OVER(partition BY year(date) order by month(date)) AS 当年累计税收, 
        sum(tax) OVER() AS 全部税收
  FROM 
    ( 
        --时间       税收 
        VALUES 
        ('01/01/2000',4000), 
        ('02/01/2000',2000), 
        ('03/01/2000',5000), 
        ('04/01/2000',5000), 
        ('01/01/2001',NULL), 
        ('02/01/2001',4000) 
    ) AS EMPLOY(date,tax); 

 

查询结果如下: 

日期     税收    当年累计税收    全部税收
01/01/2000    4000    4000    20000
02/01/2000    2000    6000    20000
03/01/2000    5000    11000    20000
04/01/2000    5000    16000    20000
01/01/2001    [NULL]    [NULL]    20000
02/01/2001    4000    4000    20000 

 

可以看到在进行聚集的集合中引入一个排序方式,DB2将不处理一个报告( reporting ) 函数,而是处理一个累加( cumulative )函数。累加函数是一种标量-聚集函数,它对当前行以及集合中当前行之前(相对排序方式而言)的所有行进行操作。这里,计算了以年份为界限每月税收的累加和。观察上面数据可以看到,计算到2011-01-01时,累计税收以2011-01-01的税收为起点,重新开始计算。
SELECT *
FROM ( select row_Number() over(order by id asc) as row_next,id ,name from staff ) AS A
WHERE ROW_next BETWEEN 3 AND 5;
 
取第三行到第五行

 

这种方法有如下,特点
对事务和性能的影响 
在这种方法中,开发人员需要以编程的方式处理事务。如果去掉 row_next 子句( ROW_NEXT BETWEEN ? and ? ),那么将返回所有匹配选择标准的行。 上面使用的 SELECT * FROM 子句可以看作一个临时表,里面存有匹配选择标准的整个结果集,然后从这个临时表中返回落在给定行范围内的结果集。使用 rownumber() 功能时对系统会有额外的性能影响,因为数据库首先要获取所有匹配选择标准的行,然后再每次从结果集获取下一行记录时都得访问数据库,直到获得了给定范围内的所有行为止。因此,如果再使用这种方法的进行排序的同时有人进行了dml操作,则有可能出现取重的情况。
代码的可移植性 几乎所有的 RDBMS 系统都支持从结果集的开始部分或结尾部分获取任意数量的行,但是所使用的 SQL 语法却有所差异。因此,如果我们要更换数据库供应商,就不得不修改相应的查询。
提供无缝的后退按钮功能 
如果我们的设计需要具有浏览器后退按钮功能,并且用户希望每次都能够查看最近的来自数据库的信息,那么这种设计可以提供一个可行的解决方案。其缺点是,对于每个结果页面,都可能需要访问数据库,这样就大大地影响了性能。
基本上,URL 查询都会有内嵌的搜索参数,这些参数用于提供给 SQL Query。例如,下面就是一个可能的 URL 查询:
http://localhost:8050/ProductSearch?minprize=50&maxprice=100&pagecount=20&resultsPerpage=10 
当用户单击浏览器上的后退按钮时,将在浏览器中显示的前一个 URL 就是:
http://localhost:8050/ProductSearch?minprize=50&maxprice=100&pagecount=10&resultsPerpage=10 
我们假设,在会话中提供了最近页面的 id。因此,可以像下面这样来跟踪浏览器后退按钮:

if(!session.getAttribute("pageID).equalsIgnoreCase
      (request.getAttribute("pageCount")){
    //Then user has clicked the back button
    //Get possible search parameters, pagecount 
    //and resultsPerPage from URL and formulate 
    //a dynamic URL and post the information back 
    //to the servlet.The URL would be
    http://localhost:8050/ProductSearch?minprize=50&
    maxprice=100&pagecount=10&resultsPerpage=10
    return;
    //Done display the results screen page
}

 
通过使用这种方法,用户就可以查看数据库的最新信息。

关于olap函数的其他使用方法可以参考

http://www.searchdatabase.com.cn/ShowContent_9290.htm

 

 

 

参考至:http://www.cnblogs.com/Fskjb/archive/2011/02/28/1967429.html
   http://www.ibm.com/developerworks/cn/data/library/techarticles/0110lyle/0110lyle.html
  http://www.ibm.com/developerworks/cn/data/library/techarticles/0307balani/0307balani.html
  http://blog.csdn.net/andyxm/article/details/2795356

 

分享到:
评论

相关推荐

    DB2中OLAP函数

    DB2中OLAP函数。电子文档里面有详细介绍!对学习开窗函数有很好的帮助。联机分析处理OLAP是一种软件技术,它使分析人员能够迅速、一致、交互地从各个方面观察信息,以达到深入理解数据的目的。

    (开发人员应当读的文章)Bob Lyle 谈 DB2 中的 OLAP 函数

    ### DB2中的OLAP函数详解 #### 一、引言 在数据库管理领域,特别是针对IBM的DB2 Universal Database,OLAP(Online Analytical Processing,在线分析处理)函数的引入极大地提高了数据分析的能力和效率。本文将...

    DB2到GreenPlum/PostgreSQL的转换指南

    - SQL谓词、临时表、CASE表达式、列函数、OLAP函数、标量函数等SQL组件的差异。 - ORDER BY、GROUP BY、HAVING子句的使用方法。 - 动态游标、连接操作、子查询的处理方式。 - UNION和UNION ALL的用法。 - 动态SQL的...

    IBM OLAP redbook -- business intelligence

    混合分析是DB2 OLAP Server V8.1中的一个重要特性,它允许用户在一个查询中同时访问OLAP数据和关系型数据。这种混合访问的能力极大地提高了业务智能系统的灵活性和实用性。例如,在分析销售趋势时,除了可以从多维...

    DB2 SQL 精萃.pdf

    8. ROLLUP、CUBE、GROUPING SETS等OLAP函数的使用。 9. EXISTS与IN关键字的使用,以及SOME和ANY的使用区别。 10. EXCEPT、INTERSECT、UNION等集合操作的使用。 11. 使用MERGE语句进行数据操作。 12. 采集样本数据的...

    DB2 许可证文件

    总的来说,理解和遵循 DB2 许可证文件中的条款是使用 DB2 产品时必须注意的关键环节,而 DB2 v9.7 版本的特性则展示了 IBM 在数据库领域的持续创新和技术实力。对于企业来说,了解这些功能并有效地利用它们,可以...

    DB2 redbook(中文版)

    - 分析功能:介绍DB2的内置分析函数和OLAP(在线分析处理)支持,以及与IBM Cognos、SPSS等分析工具的集成。 - 数据安全:探讨DB2的数据加密、访问控制和审计功能,以确保数据的安全性。 5. **故障排查与恢复**:...

    DB2 SQL 精粹

    3. 数据仓库和OLAP:在DB2中构建数据仓库,支持复杂的在线分析处理(OLAP)操作。 总之,《DB2 SQL精粹》这本书全面覆盖了DB2 SQL的各个方面,无论是初学者还是经验丰富的DBA,都能从中受益,提升自己的数据库管理...

    IBM DB2通用数据库SQL入门.rar

    本教程"IBM DB2通用数据库SQL入门"将引导初学者了解如何在IBM DB2环境中有效地使用SQL。 一、IBM DB2概述 IBM DB2提供了高度可靠、安全且可扩展的数据存储解决方案,支持多种操作系统平台,如Windows、Linux、Unix...

    DB2分区数据库简介-DPF

    此时可通过重新设计哈希函数或者手动调整数据分布来解决。 3. **网络延迟:** - 分区数据库依赖于节点间的网络通信,因此需要监控网络状况,确保网络延迟不会成为性能瓶颈。 4. **故障恢复:** - 应定期备份数据...

    IBM DB2通用数据库商业智能教程

    2. **数据导出**:将数据库中的数据导出为文件,以便备份或在其他系统中使用。 3. **数据转换**:在入库前对数据进行清洗、格式化和规范化,确保数据质量。 4. **ETL过程**:提取(Extract)、转换(Transform)、...

    DB2 SQL 消息

    - **SQL0120N:** 表示无法使用`OLAP`功能。 - **SQL0121N:** 表示同一SQL语句中相同变量重复赋值。 - **SQL0122N:** 表示`SELECT`语句中缺少`GROUP BY`子句。 - **SQL0123N:** 表示表达式中缺少操作符。 - **SQL0125...

    MongoDB权威指南.pdf-PostgreSQL_to_DB2_Porting_Guide.pdf

    5. 数据仓库和分析:优化了大数据和分析查询,适合数据仓库和OLAP应用。 从PostgreSQL向DB2移植的过程中,主要关注以下几个方面: 1. 数据类型转换:两种数据库的数据类型可能有所不同,需要进行转换以确保数据...

    IBM DB2通用数据库SQL入门.pdf

    ### IBM DB2通用数据库SQL入门知识点解析 #### 一、IBM DB2简介 IBM DB2是一种功能强大的关系型数据库管理系统(RDBMS),...这些知识点对于初学者来说是非常有价值的资源,有助于他们深入了解DB2数据库的使用方法。

    DB2 数据库分区的介绍

    - **哈希分布**:使用哈希函数将数据均匀分布到各个分区中,例如基于`customer`表中的`cust_id`字段作为哈希键。 - **分区位图**:通过位图记录每个分区的数据分布情况,便于管理和查询。 #### 六、数据库分区组 -...

    DB 2数据舱

    文件还包含了对DB2的多维数据分析、特殊索引、触发器、用户定义类型(UDT)、用户定义函数(UDF)等高级特性,这些功能增强了DB2处理复杂查询和维护数据完整性的能力。 由于文档内容的复杂性和长度限制,这里无法...

Global site tag (gtag.js) - Google Analytics