- 浏览: 4406406 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
DB2 olap函数概述
在线分析处理函数(OLAP)是DB2中扩展了关系模型、使关系模型能够理解行集合内的排序方式的重要函数之一。典型的 DB2在线分析处理的格式包括两部分:函数部分和OVER表达式部分。
函数部分包括:ROW_NUMBER、RANK、DENSE_RANK、FIRST_VALUE、LAST_VALUE、LAG、LEAD、COUNT、MIN、MAX、AVG、SUM
over表达式部分在示例中进行介绍,这里不具体介绍语法
OLAP函数介绍
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);
查询结果如下:
序号 姓名 部门 工资
1 赵红 技术部 2000
2 张三 市场部 4000
3 王蓝 技术部 4000
4 李四 市场部 5000
5 李白 技术部 5000
6 王五 市场部 (null)
很多人非常不理解,怎么两个函数能这么写呢?甚至有人怀疑上面的SQL语句是不是真的能执行。其实,ROW_NUMBER是个函数没错,它的作用从它的名字也可以看出来,就是给查询结果集编号。但是,OVER并不是一个函数,而是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),
注意OVER前面的函数只对OVER定义的结果集起作用。
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);
查询结果如下
:
序号 部门序号 姓名 部门 工资 部门平均工资 全员平均工资
1 1 张三 市场部 4000 4500 4000
2 2 李四 市场部 5000 4500 4000
3 3 王五 市场部 (null) 4500 4000
4 1 赵红 技术部 2000 3666 4000
5 2 王蓝 技术部 4000 3666 4000
6 3 李白 技术部 5000 3666 4000
OVER是一个表达式,它的作用是定义一个作用域(或者可以说是结果集),OVER前面的函数只对OVER定义的结果集起作用。
ORDER BY的作用大家应该非常熟悉,用来对结果集排序。PARTITION BY的作用其实也很简单,和GROUP BY 的作用相同,用来对结果集分组。
到此为止,大家应该对OLAP函数的套路有一定的了解和体会了吧。如果结果集的列中存在null,当我们该列排序时,null会被作为无限大处理,如果我们想把控制null排序的位置呢?使用NULLS FIRST或者NULLS LAST即可,请看下面的SQL:
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);
查询结果如下:
RN RK D_RK 姓名 部门 工资
1 1 1 王五 市场部 (null)
2 2 2 李四 市场部 5000
3 2 2 李白 技术部 5000
4 4 3 张三 市场部 4000
5 4 3 王蓝 技术部 4000
6 6 4 赵红 技术部 2000
请注意ROW_NUMBER和RANK之间的区别,RANK是等级,排名的意思,李四和李白的工资都是5000,他们并列排名第二。张三和王蓝的工资都是4000,怎么RANK函数的排名是第四,而DENSE_RANK的排名是第三呢?这正是这两个函数之间的区别。由于有两个第二名,所以RANK函数默认没有第三名。
现在又有个新问题,假设让你查询一下每个员工的工资以及工资小于他的所有员工的平均工资,该怎么办呢?请看下面的SQL:
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);
查询结果如下:
姓名 工资 小于本人工资的总额 大于本人工资的总额 工资总额1 工资总额2
王五 (null) (null) 20000 20000 20000
赵红 2000 2000 20000 20000 20000
张三 4000 6000 18000 20000 20000
王蓝 4000 10000 14000 20000 20000
李四 5000 15000 10000 20000 20000
李白 5000 20000 5000 20000 20000
上面SQL 中的OVER部分出现了一个ROWS子句,我们先来看一下ROWS子句的结构:
ROWS BETWEEN <上限条件> AND <下限条件>
其中“上限条件”可以是如下关键字:
UNBOUNDED PRECEDING
<number> PRECEDING
CURRENT ROW
“下线条件”可以是如下关键字:
CURRENT ROW
<number> FOLLOWING
UNBOUNDED FOLLOWING
注意,以上关键字都是相对当前行的,UNBOUNDED PRECEDING表示当前行前面的所有行,也就是说没有上限;<number> PRECEDING表示从当前行开始到它前面的<number>行为止,例如,number=2,表示的是当前行前面的2行;CURRENT ROW表示当前行。
OVER表达式还可以有个子句,那就是RANGE,它的使用方式和ROWS 十分相似,或者说一模一样,作用也差多不,不过有点区别,如下所示:
RANGE BETWEEN <上限条件> AND <下限条件>
其中的<上限条件> 、<下限条件>和ROWS一模一样,如下的SQL演示它们之间的区别:
SELECT
NAME AS 姓名,
DEPT AS 部门,
SALARY AS 工资,
FIRST_VALUE(SALARY, 'IGNORE NULLS') OVER(PARTITION BY DEPT) AS 部门最低工资,
LAST_VALUE(SALARY, 'RESPECT NULLS') OVER(PARTITION BY DEPT) 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);
查询结果如下:
姓名 部门 工资 部门最低工资 部门最高工资 ROWS RANGE
张三 市场部 2000 2000 4000 4400 4400
赵红 技术部 2400 2400 5000 7400 4400
李四 市场部 3000 2000 4000 8600 6200
李白 技术部 3200 2400 5000 10200 6200
王五 市场部 4000 2000 4000 12200 4000
王蓝 技术部 5000 2400 5000 9000 5000
上面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 定义集合的最小值和最大值。值得注意的是这两个函数有个参数,'IGNORE NULLS' 或 'RESPECT NULLS',它们的作用正如它们的名字一样,用来忽略NULL值和考虑NULL值。
还有两个函数我们没有介绍,LAG和LEAD,这两个函数的功能非常强大,请看下面SQL:
SELECT
NAME AS 姓名,
SALARY AS 工资,
LAG(SALARY,0) OVER(ORDER BY SALARY) AS LAG0,
LAG(SALARY) OVER(ORDER BY SALARY) AS LAG1,
LAG(SALARY,2) OVER(ORDER BY SALARY) AS LAG2,
LAG(SALARY,3,0,'IGNORE NULLS') OVER(ORDER BY SALARY) AS LAG3,
LAG(SALARY,4,-1,'RESPECT NULLS') OVER(ORDER BY SALARY) AS LAG4,
LEAD(SALARY) OVER(ORDER BY SALARY) AS LEAD
FROM
(
--姓名 部门 工资
VALUES
('张三','市场部',2000),
('赵红','技术部',2400),
('李四','市场部',3000),
('李白','技术部',3200),
('王五','市场部',4000),
('王蓝','技术部',5000)
) AS EMPLOY(NAME,DEPT,SALARY);
查询结果如下:
姓名 工资 LAG0 LAG1 LAG2 LAG3 LAG4 LEAD
张三 2000 2000 (null) (null) 0 -1 2400
赵红 2400 2400 2000 (null) 0 -1 3000
李四 3000 3000 2400 2000 0 -1 3200
李白 3200 3200 3000 2400 2000 -1 4000
王五 4000 4000 3200 3000 2400 2000 5000
王蓝 5000 5000 4000 3200 3000 2400 (null)
我们先来看一下LAG 和 LEAD 函数的声明,如下:
LAG(表达式或字段, 偏移量, 默认值, IGNORE NULLS或RESPECT 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
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
-
学习DB2中的锁---锁基础(原创)
2012-02-06 17:37 3307锁的工作原理 ... -
db2与oracle不同的常用sql写法记录(原创)
2012-02-06 16:51 59501、查看前3行数据,列 ... -
编目DB2数据库(原创)
2012-01-30 14:57 3939为什么要对数据库进行编目 ... -
DB2的权限管理(原创)
2012-01-30 11:33 29964DB2 授权简介 ... -
DB2安全管理的相关概念(原创)
2012-01-29 11:45 1946数据库安全涉及的问题 ... -
DB2表空间管理详解(原创)
2012-01-05 23:21 4574create tablespace语法树 ... -
DB2表空间理论基础(原创)
2012-01-05 11:29 2672在DB2 V9版本中表空间 ... -
DB2创建数据库常用参数详解(原创)
2012-01-04 14:09 19425本文只介绍DB2 create database语法中的常用参 ...
相关推荐
### IBM DB2 OLAP 安装与使用指南详解 #### 一、IBM DB2 OLAP 概述 **IBM DB2 OLAP** 是一款由IBM公司开发的数据仓库解决方案,它结合了关系数据库管理系统(DBMS)的优势与在线分析处理(OLAP)的功能。此文档主要...
DB2中OLAP函数。电子文档里面有详细介绍!对学习开窗函数有很好的帮助。联机分析处理OLAP是一种软件技术,它使分析人员能够迅速、一致、交互地从各个方面观察信息,以达到深入理解数据的目的。
### DB2 OLAP Server - 理论与实践:深入解析 #### 一、DB2 OLAP Server概览 DB2 OLAP Server是由IBM开发的一款先进的在线分析处理(OLAP)系统,旨在提供高效的数据分析能力。OLAP,即Online Analytical Processing...
### DB2中的OLAP函数详解 #### 一、引言 在数据库管理领域,特别是针对IBM的DB2 Universal Database,OLAP(Online Analytical Processing,在线分析处理)函数的引入极大地提高了数据分析的能力和效率。本文将...
### ORACLE OLAP函数语法详解 #### 一、概述 Oracle OLAP(Online Analytical Processing)函数主要用于执行复杂的分析计算,如聚合、排名、趋势分析等。这些函数可以帮助用户更高效地处理大型数据集,特别是在数据...
db2 olap服务器理论与实践part1 很有用的东东
oracle olap函数可以实现很多累计功能,前段时间学习了一下。在网上搜集了一些资料。
#### 一、DB2 OLAP Server V8.1概述 ##### 1.1 高级分析功能 ###### 1.1.1 结合OLAP与关系型数据库:混合分析 混合分析是DB2 OLAP Server V8.1中的一个重要特性,它允许用户在一个查询中同时访问OLAP数据和关系型...
在本实验中,我们将深入探讨SQLServer在在线分析处理(OLAP)以及数据挖掘中的应用。这个实验基于一个名为FoodMart的数据集,该数据集是一个典型的企业级零售业务数据库,适用于展示多维数据分析和数据仓库的功能。...
Mondrian开源OLAP引擎详解 Mondrian 是一个基于 Java 语言的开源 OLAP 引擎,它通过 MDX 语句执行查询,从关系型数据库 RDBMS 中读取数据,然后经过 Java API 以多维度的形式展示查询结果。Mondrian 是一个 Open...
#### 三、MDX函数详解 ##### 1. 数组函数 - SetToArray - **功能**:将一个或多个集合转换为数组形式,便于在自定义函数中使用。 - **示例**:假设有一个包含不同地理位置的集合`SampleSet`,我们可以通过`Set...
- SQL谓词、临时表、CASE表达式、列函数、OLAP函数、标量函数等SQL组件的差异。 - ORDER BY、GROUP BY、HAVING子句的使用方法。 - 动态游标、连接操作、子查询的处理方式。 - UNION和UNION ALL的用法。 - 动态SQL的...
1. 北京移动:北京移动的经营分析系统采用IBM全套解决方案,包括DB2 UDB EEE作为数据仓库,DB2 OLAPServer用于多维分析,DB2 Intelligent Miner for Data进行数据挖掘,以及DB2 Warehouse Manager作为ETL工具和元...
### SQL开窗函数详解 #### 一、开窗函数的基本概念和语法 开窗函数,又称分析函数或OLAP函数,是SQL中一种高级特性,它允许用户在保持与原始数据行关联的同时执行复杂的计算和聚合操作。这些功能极大地提高了SQL...
在IBM Software Group的DB2 OLAP Server中,OLAP技术被集成在信息管理软件中,提供了强大的分析能力。例如,该系统支持读写操作,这对于支持规划和预算编制至关重要。DB2 OLAP Server利用了多种概念来组织和理解数据...
【Teradata OLAP函数详解】 在线分析处理(OLAP)函数是Teradata数据库中用于高效进行数据分析的关键工具,尤其适用于复杂的数据挖掘和统计分析。这些函数允许用户在标准SQL环境中执行高级运算,如排队、分位数计算...
**olap4j 0.9.7.309-JS-3 jar包详解** 在Java编程领域,jar(Java Archive)包是用于存储Java类、资源文件和元数据的压缩格式,它使得开发者可以将相关的类组织在一起,便于分发和使用。"olap4j 0.9.7.309-JS-3 jar...
olap工具 帮助大家了解olap工具操作