ITPub博客

首页 > 数据库 > Oracle > oracle的sql查询分析函数-高级部分-分析函授over()子句

oracle的sql查询分析函数-高级部分-分析函授over()子句

原创 Oracle 作者:jinqibingl 时间:2015-10-25 17:47:54 0 删除 编辑
oracle的分析函数,应该是有一个格式的
function(argu1,argu2...)
over(partition by 
      order by 
      windowing-clause)
这是一个完整的分析函数的格式。
我之前用的分析函数,实际是省略了后面的over部分,也就是高级部分。
over分为3个部分:分组子句,partition by,这是指定对整体分析数据的分组依据;排序子句,order by,也就是对每个分组内的数据进行排序,同时也是一个默认的windowing函数;开窗子句,windowing-clause,这是一个数据窗口确定子句。
一个一个说明:
1、分组子句,partition by,这个分组部分,有点类似于group by子句,就是将整体数据,按照by后面提供的分组依据,按照依据值的不同,分成很多组,让分析函数在每个组内工作,比如计算每个组的组内成员的sum值,这样说,其实就是group by。
2、排序子句,order by,在order by之前,如果有分组子句,那么就在每个分组内按照排序依据进行排序,如果没有分组子句,那就是整个数据集进行排序。order by,是一个默认的开窗函数,也就是默认的windowing子句,其表示的意思就是:从分组或者整个数据集的第一行开始到当前行,所以排序子句,可以写成这样,order by 1,根据数字1进行排序,其实际就是不用排序,我只要确定每一个分组里面有一个顺序就可以了,不用确定按照那个字段进行排序,这样拿到的数据,估计是oracle按照内在的rowid给的排序。
3、窗口子句,windowing子句,这也是重点,也是难点。窗口子句,是指定每个分组内,分析函数要处理的数据行的范围,也就是数据窗口,也就是说,用这个窗口子句,来确定在每个分组内,那些数据行要用分析函数来处理,比如sum函数,就是每个分组内,那些数据行的数值加入到sum函数计算的范围内。再强调一次,窗口子句,就是指定那些数据行进入分析函数。窗口子句,通过两种方式来指定进行分析的数据行,1是range,数值范围,2是rows,数据行的偏移量,就是当前行的前面几行到当前行的后面几行。
窗口子句,必须和排序子句一起使用,也就是必须跟在order by子句后面,这样,同时也指定了窗口子句语句中计算的依据字段是那个,也就是窗口子句,开始时前面的那个指定字段。
窗口子句具体写法:
当前行的前面行   preceding 
当前行的后面行 following
rows和range的写法一样,只是标记不同
range between 1 proceding and 1 following
rows between 1 proceding and 1 following
写法是一样的,只是一个range,一个rows。但是意识差别很大。
order by name desc,age desc range between 1 proceding and 1 following,指的是当前行的age字段的数值,加1和减1之间的范围,比如当前行的age字段数值是3,那么这个窗口指定的范围应该是2到4,这样说,也就是当前行的分析函数,要计算这个分组里面,age字段在2和4之间的数据行。而且,窗口子句的依据字段,可以是日期,但是写法不一样,要这样写,range between interval '2' day proceding and interval '2' day following,这样就是计算当前行的日期字段的前两天和后两天之间的范围,也就是5天,也就是当前天加上前两天再加上后两天。
order by name desc,age desc rows between 1 proceding and 1 following
是指,当前行的前面一行和后面一行之间的。注意这里的两个1,实际是指定了偏移量,也可以用其他写法:
(1)、rows between UNBOUNDED  proceding and UNBOUNDED  following     :UNBOUNDED PRECEDING:从当前分组的每一行开始到最后一行
(2)、rows between UNBOUNDED  proceding and  CURRENT ROW    :CURRENT ROW:从当前分组的每一行开始,到当前行
(并结束)
(3)、rows between CURRENT ROW and  UNBOUNDED  following      :CURRENT ROW:从当前分组的当前行开始,到最后一行(并结束)

从上面的表述,可以看出,windowing子句,是最复杂的,要是觉得烦的,也可以不这样算,可以用pl方式来做,只是需要一张临时表而已,相对来说,可能PL方式更为复杂一些。

over函数,实际,就是给分析函数,确定分组,确定计算范围的一个函数,是分析函数的附加部件。
加上over函数,可以完成一些超级复杂的计算要求。
下面我举一个例子:
票据管理
表1,是每个票据的票面记录,比如票据号,出票人,金额等,一个票据对应一个记录。
表2,是每个票据的详细记录,比如每个票据内包含了那些商品,记录商品的代码,数量,金额等。

题目1:我有个商品,代码是AB,要求计算出上个月出售这个商品(AB)的票据和票据总量比率,也就是占比。
表1和表2,是一对多的关系,表1和表2,交叉配对,加入条件:表2代码=AB,这时候,还是一对多的关系,很难得出表1中的唯一记录,就是,等到的表1记录,表1.*这样的写法,得到的是很多重复项,可能有人想到distinct的写法,如果票据的唯一依据是多个字段,那么很难用distinct来做,那么我们就用over函数来做。
想法:
表1和表2的卡迪尔乘积,加上表2代码=AB的条件,表1.*得到的记录,是很多的重复项,但是按照原定设计,表1里面应该有一个唯一的依据字段,也就是PK字段,而且肯定是2个字段以上,或者多个字段,那么我们就以表1的PK字段,为partition by的分组依据,也就是partition by 表1.PK字段1,表1.PK字段2,这样,表1.*得到的重复项,重复的数据行,就被分组到同一分组内,然后随便用order by排序一下,用row_number给出一个序号,然后在外面一层的sql中,指定序号=1,这样就直接所有重复项全部去除掉了。
实际:
select * from (select row_number()over(partition by a.PK字段1,a.PK字段2 order by 1) as item_rows,a.* from 表1 a,表2 b where a.no=b.no and b.code='AB') where item_rows=1;

我再做一个更复杂,同时也更为符合实际的需求。
题目2:要求查询上个月购买了特定商品的顾客,占比情况,要求统计出特定商品,特定商品是全部商品的占比。
题目再说明:计算出,购买了商品AB的顾客占比,这个简单,但是我要求,不是单单一个商品AB,我要求一次性统计出,所有商品,商品A购买的顾客占比,商品B购买的顾客占比。
想法:
还是从表1和表2的卡迪尔乘积开始,只是和上面的过程不同,上面的分组依据是表1,现在我们以表2为分组依据,我们以表2中的商品代码为分组依据,这样每个分组内,同样有表1.*的数据,然后将表1.*的数据进行排序,同样的操作,但是得到的结果不一样。
实际:





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9606200/viewspace-1816189/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-08-19

  • 博文量
    154
  • 访问量
    268243