Oracle开发之分析函数简介Over用法
作者:Paul Lin 发布时间:2024-01-17 08:40:45
一、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,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
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 *
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
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.*,
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
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等。
猜你喜欢
- 网络上关于各种语言和应用软件的速查手册和快速参考指南有很多很多,不幸的是当我们需要的时候,总是很难找到,所以我决定花点时间尽可能的收集更多的
- pip源配置文件可以放置的位置:Linux/Unix:/etc/pip.con~/.pip/pip.conf (每一个我都找了都没有,所以我
- 前言Hello!大家好,有好几天没有跟大家见面咯~不知道大家是否在等待《小玩意儿》专栏的更新呢上一篇的文章【老师见打系列】:我只是写了一个自
- 本文研究的主要是Python编程求质数实例,选取了几个数进行了测试,具体如下。定义:质数又称素数。一个大于1的自然数,除了1和它自身外,不能
- 使用tensorflow训练模型的时候,模型持久化对我们来说非常重要。如果我们的模型比较复杂,需要的数据比较多,那么在模型的训练时间会耗时很
- Jaslabs的Justin Silverton列出了十条有关优化MySQL查询的语句,我不得不对此发表言论,因为这个清单非常非常糟糕。另外
- 首先声明,没有什么不良动机,因为经常会用 translate.google.cn,就想着用 Python 模拟网页提交实现文档的批量翻译。据
- 列表有自己的sort方法,其对列表进行原址排序,既然是原址排序,那显然元组不可能拥有这种方法,因为元组是不可修改的。排序,数字、字符串按照A
- Git分支详解参考:分支管理组成1.1、master主干在版本管理中,代码库应该仅有一个主干。此主干是和当前生产保持一致的,是可用的、稳定的
- XML有很多相关的技术,将这些技术结合起来,才能充分发挥XML的强大功能。这些技术包括:Xlink与Xpointer(设置XML的超链接)、
- 一、numpy是什么?扩展库numpy是Python支持科学计算的重要扩展库,是数据分析和科学计算领域如scipy、pandas、sklea
- 这十则CSS技巧汇编于网络,作为老手已经司空见惯了,也没有什么新意,但温故而知新,或许阅读一遍也有一定的启发,本文主要面对CSS新手朋友,有
- 昨天同事问了我一个问题,有两个循环语句: for(i = n; i > 0; i--) { … } for(i = 0; i <
- 这篇文章主要介绍了微信小程序 云开发模糊查询实现详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友
- 本游戏程序实现的功能为本地二人对弈中国象棋,实现语言为javascript+VML,在windows 2000 pro+IE 6sp1的环境
- GIL(Global Interpreter Lock,即全局解释器锁)1.为什么有GIL设计者为了规避类似于内存管理这样的复杂的竞争风险问
- Xajax是PHP一个不用刷新或者跳到其他页面,就能通过点击组件等与后台后台数据库交互的技术Xajax是php的一个插件,要想使用Xajax
- 一.目标浏览网页的时候,看见哪个元素,就能截取哪个元素当图片,不管那个元素有多长 二.所用工具和第三方库python ,PIL,s
- 本文实例讲述了php+html5基于websocket实现聊天室的方法。分享给大家供大家参考。具体如下:html5的websocket 实现
- pyecharts是一个封装百度开源图表库echarts的包,使用pyecharts可以生成独立的网页,也可以在flask、django中集