一分析函数(OVER)
目录
===============================================
Oracle分析函数简介
Oracle分析函数简单实例
分析函数OVER解析
一Oracle分析函数简介
在日常的生产环境中我们接触得比较多的是OLTP系统(即Online Transaction Process)这些系统的特点是具备实时要求或者至少说对响应的时间多长有一定的要求其次这些系统的业务逻辑一般比较复杂可能需要经过多次的运算比如我们经常接触到的电子商城
在这些系统之外还有一种称之为OLAP的系统(即Online Aanalyse Process)这些系统一般用于系统决策使用通常和数据仓库数据分析数据挖掘等概念联系在一起这些系统的特点是数据量大对实时响应的要求不高或者根本不关注这方面的要求以查询统计操作为主
我们来看看下面的几个典型例子
①查找上一年度各个销售区域排名前的员工
②按区域查找上一年度订单总额占区域订单总额%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同具体有
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作
二Oracle分析函数简单实例
下面我们通过一个实际的例子按区域查找上一年度订单总额占区域订单总额%以上的客户来看看分析函数的应用
【】测试环境
SQL> desc orders_tmp;
Name Null? Type
CUST_NBR NOT NULL NUMBER()
REGION_ID NOT NULL NUMBER()
SALESPERSON_ID NOT NULL NUMBER()
YEAR NOT NULL NUMBER()
MONTH NOT NULL NUMBER()
TOT_ORDERS NOT NULL NUMBER()
TOT_SALES NOT NULL NUMBER()
【】测试数据
SQL> select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
rows selected
【】测试语句
SQL> select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
三分析函数OVER解析
请注意上面的绿色高亮部分group by的意图很明显将数据按区域ID客户进行分组那么Over这一部分有什么用呢？假如我们只需要统计每个区域每个客户的订单总额那么我们只需要 group by oregion_idocust_nbr就够了但我们还想在每一行显示该客户所在区域的订单总额这一点和前面的不同需要在前面分组的基础上按区域累加很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样前者是对一个客户后者是对一批客户)
这就是over函数的作用了！它的作用是告诉SQL引擎按区域对数据进行分区然后累积每个区域每个客户的订单总额(sum(sum(otot_sales)))
现在我们已经知道年度每个客户及其对应区域的订单总额那么下面就是筛选那些个人订单总额占到区域订单总额%以上的大客户了
SQL> select *
from (select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr) all_sales
where all_salescust_sales > all_salesregion_sales * ;
CUSTOMER REGION CUST_SALES REGION_SALES
SQL>
现在我们已经知道这些大客户是谁了！哦不过这还不够如果我们想要知道每个大客户所占的订单比例呢？看看下面的SQL语句只需要一个简单的Round函数就搞定了 SQL> select all_sales*
* round(cust_sales / region_sales ) || % Percent
from (select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr) all_sales
where all_salescust_sales > all_salesregion_sales * ;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
%
%
%
SQL>
总结
①Over函数指明在那些字段上做分析其内跟Partition by表示对数据进行分组注意Partition by可以有多个字段
②Over函数可以和其它聚集函数分析函数搭配起到不同的作用例如这里的SUM还有诸如RankDense_rank等
一分析函数(rank\dense_rank\row_number)
目录
===============================================
使用rownum为记录排名
使用分析函数来为记录排名
使用分析函数为记录进行分组排名
一使用rownum为记录排名
在前面一篇《Oracle开发专题之分析函数》我们认识了分析函数的基本应用现在我们再来考虑下面几个问题
①对所有客户按订单总额进行排名
②按区域和客户订单总额进行排名
③找出订单总额排名前位的客户
④找出订单总额最高最低的客户
⑤找出订单总额排名前%的客户
按照前面第一篇文章的思路我们只能做到对各个分组的数据进行统计如果需要排名的话那么只需要简单地加上rownum不就行了吗？事实情况是否如此想象般简单我们来实践一下
【】测试环境
SQL> desc user_order;
Name Null? Type
REGION_ID NUMBER()
CUSTOMER_ID NUMBER()
CUSTOMER_SALES NUMBER
【】测试数据
SQL> select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
rows selected
注意这里有条记录的订单总额是一样的假如我们现在需要筛选排名前位的客户如果使用rownum会有什么样的后果呢？ SQL> select rownum t*
from (select *
from user_order
order by customer_sales desc) t
where rownum <=
order by customer_sales desc;
ROWNUM REGION_ID CUSTOMER_ID CUSTOMER_SALES
rows selected
很明显假如只是简单地按rownum进行排序的话我们漏掉了另外两条记录(参考上面的结果)
二使用分析函数来为记录排名
针对上面的情况Oracle从i开始就提供了个分析函数randdense_rankrow_number来解决诸如此类的问题下面我们来看看这个分析函数的作用以及彼此之间的区别
RankDense_rankRow_number函数为每条记录产生一个从开始至N的自然数N的值可能小于等于记录的总数这个函数的唯一区别在于当碰到相同数据时的排名策略
①ROW_NUMBER
Row_number函数返回一个唯一的值当碰到相同数据时排名按照记录集中记录的顺序依次递增
②DENSE_RANK
Dense_rank函数返回一个唯一的值除非当碰到相同数据时此时所有相同数据的排名都是一样的
③RANK
Rank函数返回一个唯一的值除非遇到相同的数据时此时所有相同数据的排名是一样的同时会在最后一条相同记录和下一条不同记录的排名之间空出排名
这样的介绍有点难懂我们还是通过实例来说明吧下面的例子演示了个不同函数在遇到相同数据时不同排名策略
SQL> select region_id customer_id sum(customer_sales) total
rank() over(order by sum(customer_sales) desc) rank
dense_rank() over(order by sum(customer_sales) desc) dense_rank
row_number() over(order by sum(customer_sales) desc) row_number
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
rows selected
请注意上面的绿色高亮部分这里生动的演示了种不同的排名策略
①对于第一条相同的记录种函数的排名都是一样的
②当出现第二条相同的记录时Rank和Dense_rank依然给出同样的排名而row_number则顺延递增为依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时可以看到Rank函数在和之间空出了的排名因为这个排名实际上已经被第二三条相同的记录占了而Dense_rank则顺序递增row_number函数也是顺序递增
比较上面种不同的策略我们在选择的时候就要根据客户的需求来定夺了
①假如客户就只需要指定数目的记录那么采用row_number是最简单的但有漏掉的记录的危险
②假如客户需要所有达到排名水平的记录那么采用rank或dense_rank是不错的选择至于选择哪一种则看客户的需要选择dense_rank或得到最大的记录
三使用分析函数为记录进行分组排名
上面的排名是按订单总额来进行排列的现在跟进一步假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作对记录按地区分组然后进行排名幸亏Oracle也提供了这样的支持我们所要做的仅仅是在over函数中order by的前面增加一个分组子句partition by region_id
SQL> select region_id customer_id
sum(customer_sales) total
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
dense_rank() over(partition by region_id
order by sum(customer_sales) desc) dense_rank
row_number() over(partition by region_id
order by sum(customer_sales) desc) row_number
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
rows selected
现在我们看到的排名将是基于各个地区的而非所有区域的了！Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分这样排列函数就能够应用于这各个子集
三分析函数（top\bottom nfirst\lastntile）
目录
===============================================
带空值的排列
Top/Bottom N查询
First/Last排名查询
按层次查询
一带空值的排列
在前面《Oracle开发专题之分析函数(RankDense_rankrow_number)》一文中我们已经知道了如何为一批记录进行全排列分组排列假如被排列的数据中含有空值呢？
SQL> select region_id customer_id
sum(customer_sales) cust_sales
sum(sum(customer_sales)) over(partition by region_id) ran_total
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK
我们看到这里有一条记录的CUST_TOTAL字段值为NULL但居然排在第一名了！显然这不符合情理所以我们重新调整完善一下我们的排名策略看看下面的语句
SQL> select region_id customer_id
sum(customer_sales) cust_total
sum(sum(customer_sales)) over(partition by region_id) reg_total
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
绿色高亮处NULLS LAST/FIRST告诉Oracle让空值排名最后后第一
注意是NULLS不是NULL
二Top/Bottom N查询
在日常的工作生产中我们经常碰到这样的查询找出排名前位的订单客户找出排名前位的销售人员等等现在这个对我们来说已经是很简单的问题了下面我们用一个实际的例子来演示
【】找出所有订单总额排名前的大客户
SQL> select *
SQL> from (select region_id
SQL> customer_id
SQL> sum(customer_sales) cust_total
SQL> rank() over(order by sum(customer_sales) desc NULLS LAST) rank
SQL> from user_order
SQL> group by region_id customer_id)
SQL> where rank <= ;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
SQL>
【】找出每个区域订单总额排名前的大客户
SQL> select *
from (select region_id
customer_id
sum(customer_sales) cust_total
sum(sum(customer_sales)) over(partition by region_id) reg_total
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id customer_id)
where rank <= ;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
rows selected
三First/Last排名查询
想象一下下面的情形找出订单总额最多最少的客户按照前面我们学到的知识这个至少需要个查询第一个查询按照订单总额降序排列以期拿到第一名第二个查询按照订单总额升序排列以期拿到最后一名是不是很烦？因为Rank函数只告诉我们排名的结果却无法自动替我们从中筛选结果
幸好Oracle为我们在排列函数之外提供了两个额外的函数firstlast函数专门用来解决这种问题还是用实例说话 SQL> select min(customer_id)
keep (dense_rank first order by sum(customer_sales) desc) first
min(customer_id)
keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;
FIRST LAST
这里有几个看起来比较疑惑的地方
①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同能换成rank吗？
首先解答一下第一个问题min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录假如我们去掉会有什么样的后果呢？ SQL> select keep (dense_rank first order by sum(customer_sales) desc) first
keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first
*
ERROR at line :
ORA: missing right parenthesis
接下来看看第个问题keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只保留条数据这就是keep的作用告诉Oracle只保留符合keep条件的记录
那么什么才是符合条件的记录呢？这就是第个问题了dense_rank是告诉Oracle排列的策略first/last则告诉最终筛选的条件
第个问题如果我们把dense_rank换成rank呢？ SQL> select min(region_id)
keep(rank first order by sum(customer_sales) desc) first
min(region_id)
keep(rank last order by sum(customer_sales) desc) last
from user_order
group by region_id;
select min(region_id)
*
ERROR at line :
ORA: missing DENSE_RANK
四按层次查询
现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N第一个最后一个记录有时我们会收到类似下面这样的需求找出订单总额排名前/的客户
很熟悉是不？我们马上会想到第二点中提到的方法可是rank函数只为我们做好了排名并不知道每个排名在总排名中的相对位置这时候就引入了另外一个分析函数NTile下面我们就以上面的需求为例来讲解一下
SQL> select region_id
customer_id
ntile() over(order by sum(customer_sales) desc) til
from user_order
group by region_id customer_id;
REGION_IDCUSTOMER_ID TILE
Ntil函数为各个记录在记录集中的排名计算比例我们看到所有的记录被分成个等级那么假如我们只需要前/的记录则只需要截取TILE的值为的记录就可以了假如我们需要排名前%的记录(也就是/)那么我们只需要设置ntile()就可以了
