- 浏览: 151157 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
陈碧滔:
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 4671Oracle数据导入导出imp/exp ... -
row_number over(partition by col1 order by col2)
2010-10-28 11:16 1913row_number() OVER (PARTITION BY ... -
orcle中sys_connect_by_path的用法
2010-10-28 10:21 970sys_connect_by_path的几种 ... -
使用plsql一些常见的操作
2010-09-01 23:51 1015/**创建一个访问分析表*/ create table Ac ... -
PL/SQL编程简介(转)
2010-08-12 11:24 804Oracle在数据库中引入了一种过程化编程语言,称为PL/SQ ... -
oracle函数手册(带示例)
2010-07-30 15:12 804oracle函数手册 SQL中的单记录函数 1.ASCII 返 ... -
Oracle分析函数参考手册[下][转载自JavaEye]
2010-07-30 15:08 829=============================== ... -
Oracle分析函数参考手册[上][转载自JavaEye]
2010-07-30 15:07 778=============================== ... -
Oracle 分析函数的使用(zt)
2010-07-30 15:07 6852/11/2005 09:43 FPOracle 分析函数使用 ... -
Oracle分析函数参考手册
2010-07-30 15:06 68022/06/2005 12:22 FP Oracle从8.1. ... -
Oracle函数列表速查
2010-07-30 15:03 666PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数 ... -
oracle分析函数(3)
2010-07-30 14:59 779【2】找出每个区域订单总额排名前3的大客户: SQL &g ... -
oracle分析函数(2)
2010-07-30 14:58 790注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名 ... -
用SQL语句添加删除修改字段
2010-07-27 14:19 1377增加字段alter table docdsp add dsp ... -
oracle 存储过程的基本语法
2010-07-13 18:57 6891.基本结构 CREATE OR REPLACE PROCED ... -
oracle 字符串连接
2010-06-28 15:26 893字符串连接 SQL> select 'abc' || ' ... -
系统重装后,oracle数据库中数据快速恢复
2010-06-18 15:56 14881. 准备工作把旧的ORACLE所有文件都COPY备份下来 ... -
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
2010-06-06 19:05 802Insert是T-sql中常用语句,Insert ... -
Oracle字符串处理函数
2010-06-02 19:27 1321项目中有涉及存储过程对字符串的处理,所以就将在网上查找到的资料 ...
相关推荐
功能说明: 环境说明: 开发软件:VS 2017 (版本2017以上即可,不能低于2017) 数据库:SqlServer2008r2(数据库版本无限制,都可以导入) 开发模式:mvc。。。
labview程序代码参考学习使用,希望对你有所帮助。
大米外贸商城系统 简称damishop 完全开源版,只需做一种语言一键开启全球133中语言自动翻译功能,价格实现自动汇率转换,集成微信支付宝 paypal以及国外主流支付方式,自带文章博客系统。 软件架构 基于MVC+语言包模式,增加控制台,API导入产品方便对接其他系统(带json示例数据)。 使用要求 PHP7.4+ MYSQL5.6+ REDIS(可选) 安装方法 composer install 打开安装向导安装 http://您的域名/install 特色 1、缓存层增加时间与批量like删除 2、API产品导入方便对接其他系统 3、增加控制台命令行,命令行生成语言翻译包 4、后台一键开启自动翻译模式,支持全球133中语言,由于google代理翻译需要收费,这个功能需要付费。 5、可选购物车与ajax修改购物车产品 6、一键结算checkout 7、增加网站前台自定义路由 方便seo 更新日志 v3.9.7 集成鱼码支付接口,方便个人站长即使收款到账使用 v3.9.3 更新内容 1:增加ueditor与旧编辑器切换 2:增加可视化布局插
labview程序代码参考学习使用,希望对你有所帮助。
labview程序代码参考学习使用,希望对你有所帮助。
毕设和企业适用springboot人工智能客服系统类及旅游规划平台源码+论文+视频