窗口函数

 

 

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

前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个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 月为基准。