- 浏览: 146914 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
陈碧滔:
java获取工程目录路径 -
stevenjohn:
楼主能上传一下源代码吗?
ActiveMQ5.0实战三:使用Spring发送,消费topic和queue消息 -
指挥家:
讲的很好!
Foxmail6收发Web mail邮箱邮件实现原理 -
azhqiang:
谢谢你啊。呵呵
flex最全的表单验证
一.分析函数(OVER)
目录:
===============================================
1.Oracle分析函数简介
2. Oracle分析函数简单实例
3.分析函数OVER解析
一、Oracle分析函数简介:
在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。比如我们经常接触到的电子商城。
在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大,对实时响应的要求不高或者根本不关注这方面的要求,以查询、统计操作为主。
我们来看看下面的几个典型例子:
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同,具体有:
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作
二、Oracle分析函数简单实例:
下面我们通过一个实际的例子:按区域查找上一年度订单总额占区域订单总额20%以上的客户,来看看分析函数的应用。
【1】测试环境:
SQL > desc orders_tmp;
Name Null ? Type
-- --------------------- -------- ----------------
CUST_NBR NOT NULL NUMBER ( 5 )
REGION_ID NOT NULL NUMBER ( 5 )
SALESPERSON_ID NOT NULL NUMBER ( 5 )
YEAR NOT NULL NUMBER ( 4 )
MONTH NOT NULL NUMBER ( 2 )
TOT_ORDERS NOT NULL NUMBER ( 7 )
TOT_SALES NOT NULL NUMBER ( 11 , 2 )
【2】测试数据:
SQL > select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
-- -------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
15 7 12 2000 5 6 24
12 7 9 2000 6 2 50658
1 5 2 2000 3 2 44494
1 5 1 2000 9 2 74864
2 5 4 2000 3 2 35060
2 5 4 2000 4 4 6454
2 5 1 2000 10 4 35580
4 5 4 2000 12 2 39190
13 rows selected.
【3】测试语句:
SQL > select o.cust_nbr customer,
2 o.region_id region,
3 sum (o.tot_sales) cust_sales,
4 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
5 from orders_tmp o
6 where o. year = 2001
7 group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
-- -------- ---------- ---------- ------------
4 5 37802 37802
7 6 3750 68065
10 6 64315 68065
11 7 12204 12204
三、分析函数OVER解析:
请注意上面的绿色高亮部分,group by的意图很明显:将数据按区域ID,客户进行分组,那么Over这一部分有什么用呢?假如我们只需要统计每个区域每个客户的订单总额,那么我们只需要 group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。
这就是over函数的作用了!它的作用是告诉SQL引擎:按区域对数据进行分区,然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。
现在我们已经知道2001年度每个客户及其对应区域的订单总额,那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了
SQL > select *
2 from ( select o.cust_nbr customer,
3 o.region_id region,
4 sum (o.tot_sales) cust_sales,
5 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
6 from orders_tmp o
7 where o. year = 2001
8 group by o.region_id, o.cust_nbr) all_sales
9 where all_sales.cust_sales > all_sales.region_sales * 0.2 ;
CUSTOMER REGION CUST_SALES REGION_SALES
-- -------- ---------- ---------- ------------
4 5 37802 37802
10 6 64315 68065
11 7 12204 12204
SQL >
现在我们已经知道这些大客户是谁了!哦,不过这还不够,如果我们想要知道每个大客户所占的订单比例呢?看看下面的SQL语句,只需要一个简单的Round函数就搞定了。 SQL > select all_sales. * ,
2 100 * round (cust_sales / region_sales, 2 ) || ' % ' Percent
3 from ( select o.cust_nbr customer,
4 o.region_id region,
5 sum (o.tot_sales) cust_sales,
6 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
7 from orders_tmp o
8 where o. year = 2001
9 group by o.region_id, o.cust_nbr) all_sales
10 where all_sales.cust_sales > all_sales.region_sales * 0.2 ;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
-- -------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100 %
10 6 64315 68065 94 %
11 7 12204 12204 100 %
SQL >
总结:
①Over函数指明在那些字段上做分析,其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。
②Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如Rank,Dense_rank等。
一.分析函数2(rank\dense_rank\row_number)
目录
===============================================
1.使用rownum为记录排名
2.使用分析函数来为记录排名
3.使用分析函数为记录进行分组排名
一、使用rownum为记录排名:
在前面一篇《Oracle开发专题之:分析函数》,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题:
①对所有客户按订单总额进行排名
②按区域和客户订单总额进行排名
③找出订单总额排名前13位的客户
④找出订单总额最高、最低的客户
⑤找出订单总额排名前25%的客户
按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下。
【1】测试环境:
SQL > desc user_order;
Name Null ? Type
-- --------------------------------------- -------- ----------------------------
REGION_ID NUMBER ( 2 )
CUSTOMER_ID NUMBER ( 2 )
CUSTOMER_SALES NUMBER
【2】测试数据:
SQL > select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
-- -------- ----------- --------------
5 1 151162
10 29 903383
6 7 971585
10 28 986964
9 21 1020541
9 22 1036146
8 16 1068467
6 8 1141638
5 3 1161286
5 5 1169926
8 19 1174421
7 12 1182275
7 11 1190421
6 10 1196748
6 9 1208959
10 30 1216858
5 2 1224992
9 24 1224992
9 23 1224992
8 18 1253840
7 15 1255591
7 13 1310434
10 27 1322747
8 20 1413722
6 6 1788836
10 26 1808949
5 4 1878275
7 14 1929774
8 17 1944281
9 25 2232703
30 rows selected.
目录:
===============================================
1.Oracle分析函数简介
2. Oracle分析函数简单实例
3.分析函数OVER解析
一、Oracle分析函数简介:
在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。比如我们经常接触到的电子商城。
在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大,对实时响应的要求不高或者根本不关注这方面的要求,以查询、统计操作为主。
我们来看看下面的几个典型例子:
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同,具体有:
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作
二、Oracle分析函数简单实例:
下面我们通过一个实际的例子:按区域查找上一年度订单总额占区域订单总额20%以上的客户,来看看分析函数的应用。
【1】测试环境:
SQL > desc orders_tmp;
Name Null ? Type
-- --------------------- -------- ----------------
CUST_NBR NOT NULL NUMBER ( 5 )
REGION_ID NOT NULL NUMBER ( 5 )
SALESPERSON_ID NOT NULL NUMBER ( 5 )
YEAR NOT NULL NUMBER ( 4 )
MONTH NOT NULL NUMBER ( 2 )
TOT_ORDERS NOT NULL NUMBER ( 7 )
TOT_SALES NOT NULL NUMBER ( 11 , 2 )
【2】测试数据:
SQL > select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
-- -------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
15 7 12 2000 5 6 24
12 7 9 2000 6 2 50658
1 5 2 2000 3 2 44494
1 5 1 2000 9 2 74864
2 5 4 2000 3 2 35060
2 5 4 2000 4 4 6454
2 5 1 2000 10 4 35580
4 5 4 2000 12 2 39190
13 rows selected.
【3】测试语句:
SQL > select o.cust_nbr customer,
2 o.region_id region,
3 sum (o.tot_sales) cust_sales,
4 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
5 from orders_tmp o
6 where o. year = 2001
7 group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
-- -------- ---------- ---------- ------------
4 5 37802 37802
7 6 3750 68065
10 6 64315 68065
11 7 12204 12204
三、分析函数OVER解析:
请注意上面的绿色高亮部分,group by的意图很明显:将数据按区域ID,客户进行分组,那么Over这一部分有什么用呢?假如我们只需要统计每个区域每个客户的订单总额,那么我们只需要 group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。
这就是over函数的作用了!它的作用是告诉SQL引擎:按区域对数据进行分区,然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。
现在我们已经知道2001年度每个客户及其对应区域的订单总额,那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了
SQL > select *
2 from ( select o.cust_nbr customer,
3 o.region_id region,
4 sum (o.tot_sales) cust_sales,
5 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
6 from orders_tmp o
7 where o. year = 2001
8 group by o.region_id, o.cust_nbr) all_sales
9 where all_sales.cust_sales > all_sales.region_sales * 0.2 ;
CUSTOMER REGION CUST_SALES REGION_SALES
-- -------- ---------- ---------- ------------
4 5 37802 37802
10 6 64315 68065
11 7 12204 12204
SQL >
现在我们已经知道这些大客户是谁了!哦,不过这还不够,如果我们想要知道每个大客户所占的订单比例呢?看看下面的SQL语句,只需要一个简单的Round函数就搞定了。 SQL > select all_sales. * ,
2 100 * round (cust_sales / region_sales, 2 ) || ' % ' Percent
3 from ( select o.cust_nbr customer,
4 o.region_id region,
5 sum (o.tot_sales) cust_sales,
6 sum ( sum (o.tot_sales)) over (partition by o.region_id) region_sales
7 from orders_tmp o
8 where o. year = 2001
9 group by o.region_id, o.cust_nbr) all_sales
10 where all_sales.cust_sales > all_sales.region_sales * 0.2 ;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
-- -------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100 %
10 6 64315 68065 94 %
11 7 12204 12204 100 %
SQL >
总结:
①Over函数指明在那些字段上做分析,其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。
②Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如Rank,Dense_rank等。
一.分析函数2(rank\dense_rank\row_number)
目录
===============================================
1.使用rownum为记录排名
2.使用分析函数来为记录排名
3.使用分析函数为记录进行分组排名
一、使用rownum为记录排名:
在前面一篇《Oracle开发专题之:分析函数》,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题:
①对所有客户按订单总额进行排名
②按区域和客户订单总额进行排名
③找出订单总额排名前13位的客户
④找出订单总额最高、最低的客户
⑤找出订单总额排名前25%的客户
按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下。
【1】测试环境:
SQL > desc user_order;
Name Null ? Type
-- --------------------------------------- -------- ----------------------------
REGION_ID NUMBER ( 2 )
CUSTOMER_ID NUMBER ( 2 )
CUSTOMER_SALES NUMBER
【2】测试数据:
SQL > select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
-- -------- ----------- --------------
5 1 151162
10 29 903383
6 7 971585
10 28 986964
9 21 1020541
9 22 1036146
8 16 1068467
6 8 1141638
5 3 1161286
5 5 1169926
8 19 1174421
7 12 1182275
7 11 1190421
6 10 1196748
6 9 1208959
10 30 1216858
5 2 1224992
9 24 1224992
9 23 1224992
8 18 1253840
7 15 1255591
7 13 1310434
10 27 1322747
8 20 1413722
6 6 1788836
10 26 1808949
5 4 1878275
7 14 1929774
8 17 1944281
9 25 2232703
30 rows selected.
发表评论
-
Oracle导出导入dmp文件(转)
2010-11-17 13:41 4611Oracle数据导入导出imp/exp ... -
row_number over(partition by col1 order by col2)
2010-10-28 11:16 1842row_number() OVER (PARTITION BY ... -
orcle中sys_connect_by_path的用法
2010-10-28 10:21 941sys_connect_by_path的几种 ... -
使用plsql一些常见的操作
2010-09-01 23:51 957/**创建一个访问分析表*/ create table Ac ... -
PL/SQL编程简介(转)
2010-08-12 11:24 764Oracle在数据库中引入了一种过程化编程语言,称为PL/SQ ... -
oracle函数手册(带示例)
2010-07-30 15:12 730oracle函数手册 SQL中的单记录函数 1.ASCII 返 ... -
Oracle分析函数参考手册[下][转载自JavaEye]
2010-07-30 15:08 794=============================== ... -
Oracle分析函数参考手册[上][转载自JavaEye]
2010-07-30 15:07 741=============================== ... -
Oracle 分析函数的使用(zt)
2010-07-30 15:07 6452/11/2005 09:43 FPOracle 分析函数使用 ... -
Oracle分析函数参考手册
2010-07-30 15:06 65722/06/2005 12:22 FP Oracle从8.1. ... -
Oracle函数列表速查
2010-07-30 15:03 635PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数 ... -
oracle分析函数(3)
2010-07-30 14:59 746【2】找出每个区域订单总额排名前3的大客户: SQL &g ... -
oracle分析函数(2)
2010-07-30 14:58 755注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名 ... -
用SQL语句添加删除修改字段
2010-07-27 14:19 1328增加字段alter table docdsp add dsp ... -
oracle 存储过程的基本语法
2010-07-13 18:57 6581.基本结构 CREATE OR REPLACE PROCED ... -
oracle 字符串连接
2010-06-28 15:26 840字符串连接 SQL> select 'abc' || ' ... -
系统重装后,oracle数据库中数据快速恢复
2010-06-18 15:56 14511. 准备工作把旧的ORACLE所有文件都COPY备份下来 ... -
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
2010-06-06 19:05 754Insert是T-sql中常用语句,Insert ... -
Oracle字符串处理函数
2010-06-02 19:27 1285项目中有涉及存储过程对字符串的处理,所以就将在网上查找到的资料 ...
相关推荐
ORACLE分析函数1.ppt
1.2、分析函数中的分组/排序/窗口 分析函数包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows)
关于ORACLE分析函数的教程,教程描述描述清晰
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
ORACLE分析函数.pdf
Oracle分析函数.doc
oracle 分析函数 开发必备 数据库开发工程师
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
oracle分析函数(用法+实例),这属于oracle的高级应用。
Oracle分析函数,常用分析函数应有尽有
分析函数是oracle中强大的功能,附件是分析函数学习笔记
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
ORACLE分析函数大全 包括大部分常用的分析函数
主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () ...
oracle分析函数,窗口函数,报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)
Oracle分析函数.pdf,这份资料详细介绍了Oracle分析函数的使用,Oracle分析函数.pdf是一份不错的文档