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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9606200/viewspace-1816189/