全部栏目

首页 行业资讯 宠物日常 宠物养护 宠物健康 宠物故事
您的当前位置：首页正文

db2 OLAP函数使用

2023-11-11 来源：画鸵萌宠网

说起 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_NUMBER和RANK之间的区别，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  

 

 

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

小编还为您整理了以下内容，可能对您也有帮助：

DB2 分组后提取前5条数据

前5次，应该是按时间排序，不区分打卡地点，对吧？！
使用OLAP函数就能解决问题啦：
select * from (
select
(row_number() over (partition by 用户ID order by 打卡时间 asc)) AS row_id
用户ID,
打卡地点ID,
打卡时间
from table_name) t
where row_id<=5

DB2 分组后提取前5条数据

前5次，应该是按时间排序，不区分打卡地点，对吧？！
使用OLAP函数就能解决问题啦：
select * from (
select
(row_number() over (partition by 用户ID order by 打卡时间 asc)) AS row_id
用户ID,
打卡地点ID,
打卡时间
from table_name) t
where row_id<=5

DB2分页查询(DB2的分页查询到底该怎么写)

提起分页查询，除了那些还不知道什么是分页的人，大多数人的都会想到一个词，那就是 LIMIT ，不过很可惜， DB2 不支持这个关键字，那么 DB2 的分页查询到底该怎么写呢？只要你学会了 OLAP 函数，分页查询是非常简单的。即使你不会 OLAP 函数，按照下面的 SQL提起分页查询，除了那些还不知道什么是分页的人，大多数人的都会想到一个词，那就是LIMIT，不过很可惜，DB2不支持这个关键字，那么DB2的分页查询到底该怎么写呢？只要你学会了OLAP 函数，分页查询是非常简单的。即使你不会OLAP 函数，按照下面的SQL 照猫画虎也可以，如下：

DB2 分页

.CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;}

.CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;}

.CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;}

.CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;}

.CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;}

.CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;}

.CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;}

.CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;}

.CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;}

.CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;}

$velocityCount-

SELECT * FROM

(

SELECT B.*, ROWNUMBER() OVER() AS RN FROM

(

SELECT * FROM

) AS B

)AS A WHERE A.RN BETWEENAND ;

显示全文