Oracle开窗函数over（）的用法

OVER的定义
OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

OVER的语法
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )
PARTITION BY 子句进行分组；
ORDER BY 子句进行排序。
窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。
开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

OVER的用法
OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。
OVER在聚合函数中使用的示例
我们以SUM和COUNT函数作为示例来给大家演示。
–建立测试表和测试数据

CREATE TABLE Employee  
(  
ID INT  PRIMARY KEY,  
Name VARCHAR(20),  
GroupName VARCHAR(20),
Salary INT
)    
INSERT INTO  Employee  
VALUES(1,'小明','开发部',8000),  
      (4,'小张','开发部',7600),  
      (5,'小白','开发部',7000),    
      (8,'小王','财务部',5000),  
      (9, null,'财务部',NULL),  
      (15,'小刘','财务部',6000),  
      (16,'小高','行政部',4500),  
      (18,'小王','行政部',4000),  
      (23,'小李','行政部',4500),  
      (29,'小吴','行政部',4700);  

SUM后的开窗函数

SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee  ;

其中开窗函数的每个含义不同，我们来具体解读一下：
SUM(Salary) OVER (PARTITION BY Groupname)
只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。
SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。
SUM(Salary) OVER (ORDER BY ID)
只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。
SUM(Salary) OVER ()
对Salary进行汇总处理

窗口函数进阶－根据时间范围统计：range … interval day
前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个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条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！