DB2 在线分析处理OLAP函数的使用.docx
- 文档编号:29610395
- 上传时间:2023-07-25
- 格式:DOCX
- 页数:11
- 大小:17.77KB
DB2 在线分析处理OLAP函数的使用.docx
《DB2 在线分析处理OLAP函数的使用.docx》由会员分享,可在线阅读,更多相关《DB2 在线分析处理OLAP函数的使用.docx(11页珍藏版)》请在冰豆网上搜索。
DB2在线分析处理OLAP函数的使用
----start
说起DB2在线分析处理,可以用很好很强大来形容。
这项功能特别适用于各种统计查询,这些查询用通常的SQL很难实现,或者根本就无发实现。
首先,我们从一个简单的例子开始,来一步一步揭开它神秘的面纱,请看下面的SQL:
SELECT
ROW_NUMBER()OVER(ORDERBYSALARY)AS序号,
NAMEAS姓名,
DEPTAS部门,
SALARYAS工资
FROM
(
--姓名部门工资
VALUES
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
序号姓名部门工资
1赵红技术部2000
2张三市场部4000
3王蓝技术部4000
4李四市场部5000
5李白技术部5000
6王五市场部(null)
看到上面的ROW_NUMBER()OVER()了吗?
很多人非常不理解,怎么两个函数能这么写呢?
甚至有人怀疑上面的SQL语句是不是真的能执行。
其实,ROW_NUMBER是个函数没错,它的作用从它的名字也可以看出来,就是给查询结果集编号。
但是,OVER并不是一个函数,而是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),OVER前面的函数只对OVER定义的结果集起作用。
怎么样,不明白?
没关系,我们后面还会详细介绍。
从上面的SQL我们可以看出,典型的DB2在线分析处理的格式包括两部分:
函数部分和OVER表达式部分。
那么,函数部分可以有哪些函数呢?
如下:
ROW_NUMBER
RANK
DENSE_RANK
FIRST_VALUE
LAST_VALUE
LAG
LEAD
COUNT
MIN
MAX
AVG
SUM
上面这些函数的作用,我会在后面逐步给大家介绍,大家可以根据函数名猜测一下函数的作用。
假设我想在不改变上面语句的查询结果的情况下,追加对部门员工的平均工资和全体员工的平均工资的查询,怎么办呢?
用通常的SQL很难查询,但是用OLAP函数则非常简单,如下SQL所示:
SELECT
ROW_NUMBER()OVER()AS序号,
ROW_NUMBER()OVER(PARTITIONBYDEPTORDERBYSALARY)AS部门序号,
NAMEAS姓名,
DEPTAS部门,
SALARYAS工资,
AVG(SALARY)OVER(PARTITIONBYDEPT)AS部门平均工资,
AVG(SALARY)OVER()AS全员平均工资
FROM
(
--姓名部门工资
VALUES
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
序号部门序号姓名部门工资部门平均工资全员平均工资
11张三市场部400045004000
22李四市场部500045004000
33王五市场部(null)45004000
41赵红技术部200036664000
52王蓝技术部400036664000
63李白技术部500036664000
请注意序号和部门序号之间的区别,我们在查询部门序号的时候,在OVER表达式中多了两个子句,分别是PARTITIONBY和ORDERBY。
它们有什么作用呢?
在介绍它们的作用之前,我们先来回顾一下OVER的作用,还记得吗?
OVER是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),OVER前面的函数只对OVER定义的结果集起作用。
ORDERBY的作用大家应该非常熟悉,用来对结果集排序。
PARTITIONBY的作用其实也很简单,和GROUPBY的作用相同,用来对结果集分组。
到此为止,大家应该对OLAP函数的套路有一定的了解和体会了吧。
大家看一下上面SQL的结果集,发现王五的工资是null,当我们按工资排序时,null被放到最后,我们想把null放在前边该怎么办呢?
使用NULLSFIRST关键字即可,默认是NULLSLAST,请看下面的SQL:
SELECT
ROW_NUMBER()OVER(ORDERBYSALARYdescNULLSFIRST)ASRN,
RANK()OVER(ORDERBYSALARYdescNULLSFIRST)ASRK,
DENSE_RANK()OVER(ORDERBYSALARYdescNULLSFIRST)ASD_RK,
NAMEAS姓名,
DEPTAS部门,
SALARYAS工资
FROM
(
--姓名部门工资
VALUES
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
RNRKD_RK姓名部门工资
111王五市场部(null)
222李四市场部5000
322李白技术部5000
443张三市场部4000
543王蓝技术部4000
664赵红技术部2000
请注意ROW_NUMBER和RANK之间的区别,RANK是等级,排名的意思,李四和李白的工资都是5000,他们并列排名第二。
张三和王蓝的工资都是4000,怎么RANK函数的排名是第四,而DENSE_RANK的排名是第三呢?
这正是这两个函数之间的区别。
由于有两个第二名,所以RANK函数默认没有第三名。
现在又有个新问题,假设让你查询一下每个员工的工资以及工资小于他的所有员工的平均工资,该怎么办呢?
怎么?
没听明白问题?
不要紧,请看下面的SQL:
SELECT
NAMEAS姓名,
SALARYAS工资,
SUM(SALARY)OVER(ORDERBYSALARYNULLSFIRSTROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AS小于本人工资的总额,
SUM(SALARY)OVER(ORDERBYSALARYNULLSFIRSTROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING)AS大于本人工资的总额,
SUM(SALARY)OVER(ORDERBYSALARYNULLSFIRSTROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)AS工资总额1,
SUM(SALARY)OVER()AS工资总额2
FROM
(
--姓名部门工资
VALUES
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
姓名工资小于本人工资的总额大于本人工资的总额工资总额1工资总额2
王五(null)(null)200002000020000
赵红20002000200002000020000
张三40006000180002000020000
王蓝400010000140002000020000
李四500015000100002000020000
李白50002000050002000020000
上面SQL中的OVER部分出现了一个ROWS子句,我们先来看一下ROWS子句的结构:
ROWSBETWEEN<上限条件>AND<下限条件>
其中“上限条件”可以是如下关键字:
UNBOUNDEDPRECEDING
CURRENTROW
“下线条件”可以是如下关键字:
CURRENTROW
UNBOUNDEDFOLLOWING
注意,以上关键字都是相对当前行的,UNBOUNDEDPRECEDING表示当前行前面的所有行,也就是说没有上限;
至于其它两个关键字,我想,不用我说,你也应该知道了吧。
如果你还不明白,请仔细分析上面SQL的查询结果。
OVER表达式还可以有个子句,那就是RANGE,它的使用方式和ROWS十分相似,或者说一模一样,作用也差多不,不过有点区别,如下所示:
RANGEBETWEEN<上限条件>AND<下限条件>
其中的<上限条件>、<下限条件>和ROWS一模一样,如下的SQL演示它们之间的区别:
SELECT
NAMEAS姓名,
DEPTAS部门,
SALARYAS工资,
FIRST_VALUE(SALARY,'IGNORENULLS')OVER(PARTITIONBYDEPT)AS部门最低工资,
LAST_VALUE(SALARY,'RESPECTNULLS')OVER(PARTITIONBYDEPT)AS部门最高工资,
SUM(SALARY)OVER(ORDERBYSALARYROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASROWS,
SUM(SALARY)OVER(ORDERBYSALARYRANGEBETWEEN500PRECEDINGAND500FOLLOWING)ASRANGE
FROM
(
--姓名部门工资
VALUES
('张三','市场部',2000),
('赵红','技术部',2400),
('李四','市场部',3000),
('李白','技术部',3200),
('王五','市场部',4000),
('王蓝','技术部',5000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
姓名部门工资部门最低工资部门最高工资ROWSRANGE
张三市场部20002000400044004400
赵红技术部24002400500074004400
李四市场部30002000400086006200
李白技术部320024005000102006200
王五市场部400020004000122004000
王蓝技术部50002400500090005000
上面SQL的RANGE子句的作用是定义一个工资范围,这个范围的上限是当前行的工资-500,下限是当前行工资+500。
例如:
李四的工资是3000,所以上限是3000-500=2500,下限是3000+500=3500,那么有谁的工资在2500-3500这个范围呢?
只有李四和李白,所以RANGE列的值就是3000(李四)+3200(李白)=6200。
以上就是ROWS和RANGE得区别。
上面的SQL还用到了FIRST_VALUE和LAST_VALUE两个函数,它们的作用也非常简单,用来求OVER定义集合的最小值和最大值。
值得注意的是这两个函数有个参数,'IGNORENULLS'或'RESPECTNULLS',它们的作用正如它们的名字一样,用来忽略NULL值和考虑NULL值。
还有两个函数我们没有介绍,LAG和LEAD,这两个函数的功能非常强大,请看下面SQL:
SELECT
NAMEAS姓名,
SALARYAS工资,
LAG(SALARY,0)OVER(ORDERBYSALARY)ASLAG0,
LAG(SALARY)OVER(ORDERBYSALARY)ASLAG1,
LAG(SALARY,2)OVER(ORDERBYSALARY)ASLAG2,
LAG(SALARY,3,0,'IGNORENULLS')OVER(ORDERBYSALARY)ASLAG3,
LAG(SALARY,4,-1,'RESPECTNULLS')OVER(ORDERBYSALARY)ASLAG4,
LEAD(SALARY)OVER(ORDERBYSALARY)ASLEAD
FROM
(
--姓名部门工资
VALUES
('张三','市场部',2000),
('赵红','技术部',2400),
('李四','市场部',3000),
('李白','技术部',3200),
('王五','市场部',4000),
('王蓝','技术部',5000)
)ASEMPLOY(NAME,DEPT,SALARY);
查询结果如下:
姓名工资LAG0LAG1LAG2LAG3LAG4LEAD
张三20002000(null)(null)0-12400
赵红240024002000(null)0-13000
李四30003000240020000-13200
李白32003200300024002000-14000
王五4000400032003000240020005000
王蓝500050004000320030002400(null)
我们先来看一下LAG和LEAD函数的声明,如下:
LAG(表达式或字段,偏移量,默认值,IGNORENULLS或RESPECTNULLS)
LAG是向下偏移,LEAD是想上偏移,大家看一下上面SQL的查询结果就一目了然了。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 在线分析处理OLAP函数的使用 在线 分析 处理 OLAP 函数 使用