Oracle开发之窗口函数 rows between unbounded preceding and current row

  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 月为基准。

 

本文转载自http://blog.csdn.net/cnham/article/details/6101199

Oracle开发之窗口函数 rows between unbounded preceding and current row

标签：