Oracle开发之：窗口函数

1,测试环境：
SQL> create table win_order(
     month number(2),
     total_sales number);

 2,输入数据:
insert into win_order values(1,623141);
insert into win_order values(2,423124);
insert into win_order values(3,323214);
insert into win_order values(4,212314);
insert into win_order values(5,654314);
insert into win_order values(6,122134);
insert into win_order values(7,859234);
insert into win_order values(8,752314);
insert into win_order values(9,365314);
insert into win_order values(10,265314);
insert into win_order values(11,563114);
insert into win_order values(12,595314); 

 3,测试语句:

      我们前面使用了sum(sum(sal)) over (partition by deptno) 来统计每个部门的总额。现在我们要统计的不单是每个部门，而是所有分区，partition by region_id在这里不起作用了。

Oracle为这种情况提供了一个子句：rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：

SQL> select month,
            sum(total_sales) month_sales,
            sum(sum(total_sales))over (order by month
            rows between unbounded preceding and unbounded following) total_sale
         from win_order group by month;
 
MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141    5758845
    2      423124    5758845
    3      323214    5758845
    4      212314    5758845
    5      654314    5758845
    6      122134    5758845
    7      859234    5758845
    8      752314    5758845
    9      365314    5758845
   10      265314    5758845
   11      563114    5758845
   12      595314    5758845
 
12 rows selected

高亮处的代码在这里发挥了关键作用，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。

    unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：

SQL> select month,
              sum(total_sales) month_sales,
              sum(sum(total_sales))over (order by month
              rows between 1 preceding and unbounded following) total_sale
          from win_order group by month ;

MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141    5758845
    2      423124    5758845
    3      323214    5135704
    4      212314    4712580
    5      654314    4389366
    6      122134    4177052
    7      859234    3522738
    8      752314    3400604
    9      365314    2541370
   10      265314    1789056
   11      563114    1423742
   12      595314    1158428
 
12 rows selected
     很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。

三、窗口函数进阶－－滚动统计（累积/均值）

     考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。

    很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: current row就可以了。

SQL> select month,
            sum(total_sales) month_sales,
            sum(sum(total_sales)) over (order by month
                    rows between unbounded preceding and current row ) total_sale
         from win_order group by month;    

MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141     623141
    2      423124    1046265
    3      323214    1369479
    4      212314    1581793
    5      654314    2236107
    6      122134    2358241
    7      859234    3217475
    8      752314    3969789
    9      365314    4335103
   10      265314    4600417
   11      563114    5163531
   12      595314    5758845
 
12 rows selected

下面的结果一样：

SQL> select month,
  2  sum(total_sales) month_sales,
  3  sum(sum(total_sales)) over (order by month) total_sale
  4  from win_order group by month
  5  ;
 
MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141     623141
    2      423124    1046265
    3      323214    1369479
    4      212314    1581793
    5      654314    2236107
    6      122134    2358241
    7      859234    3217475
    8      752314    3969789
    9      365314    4335103
   10      265314    4600417
   11      563114    5163531
   12      595314    5758845
 
12 rows selected

因此，rows between unbounded preceding and current row 为窗口默认值！

   现在我们能得到滚动的销售总额了！下面这个统计结果看起来更加完美，它展现了所有我们需要的数据

SQL> select month,
             sum(total_sales) month_sales,
             sum(sum(total_sales)) over(order by month
                rows between unbounded preceding and current row) total_sales,
             sum(sum(total_sales)) over(order by month
             rows between unbounded preceding and unbounded following) total_sales
         from win_order group by month
  8  ;
 
MONTH MONTH_SALES TOTAL_SALES TOTAL_SALES
----- ----------- ----------- -----------
    1      623141      623141     5758845
    2      423124     1046265     5758845
    3      323214     1369479     5758845
    4      212314     1581793     5758845
    5      654314     2236107     5758845
    6      122134     2358241     5758845
    7      859234     3217475     5758845
    8      752314     3969789     5758845
    9      365314     4335103     5758845
   10      265314     4600417     5758845
   11      563114     5163531     5758845
   12      595314     5758845     5758845
 
12 rows selected

在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。

SQL> select month,
  2         avg(total_sales) month_avg_sales,
  3         avg(avg(total_sales)) over(order by month
  4         rows between unbounded preceding and current row) current_avg_sales,
  5         avg(avg(total_sales)) over(order by month
  6         rows between unbounded preceding and unbounded following) avg_sale
  7  from win_order
  8  group by month
  9  ;
 
MONTH MONTH_AVG_SALES CURRENT_AVG_SALES   AVG_SALE
----- --------------- ----------------- ----------
    1          623141            623141      479903.75
    2          423124          523132.5     479903.75
    3          323214            456493      479903.75
    4          212314         395448.25     479903.75
    5          654314          447221.4      479903.75
    6          122134  393040.166666667  479903.75
    7          859234  459639.285714286  479903.75
    8          752314        496223.625       479903.75
    9          365314  481678.111111111   479903.75
   10          265314          460041.7        479903.75
   11          563114  469411.909090909  479903.75
   12          595314         479903.75       479903.75
 

以下内容转载

四、窗口函数进阶－根据时间范围统计：

前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：
  select trunc(order_dt) day,
              sum(sale_price) daily_sales,
              avg(sum(sale_price)) over (order by trunc(order_dt)
                       range between interval '2' day preceding
                                      and interval '2' day following) five_day_avg
    from cust_order
  where sale_price is not null
      and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
      and to_date('31-jul-2001','dd-mon-yyyy')
为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。

五、窗口函数进阶－first_value/last_value：

Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。
 select month,
              first_value(sum(tot_sales)) over (order by month
                                     rows between 1 preceding and 1 following) prev_month,
 
              sum(tot_sales) monthly_sales,
 
              last_value(sum(tot_sales)) over (order by month
                                   rows between 1 preceding and 1 following) next_month,
 
              avg(sum(tot_sales)) over (order by month
                                  rows between 1 preceding and 1 following) rolling_avg
     from orders
  where year = 2001
       and region_id = 6
   group by month
  order by month;
首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！

六、窗口函数进阶－比较相邻记录：

通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？

从第五部分的介绍我们可以知道，利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。

leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。
 select  month,           
           sum(tot_sales) monthly_sales,
           lag(sum(tot_sales), 1) over (order by month) prev_month_sales
    from orders
  where year = 2001
       and region_id = 6
   group by month
  order by month;
lag(sum(tot_sales),1)中的1表示以1月为基准。