over(）、PARTITION BY 、ORDER BY、ROWS BETWEEN分组函数

来源：http://www.cnblogs.com/linjiqin/archive/2012/04/06/2434806.html

分析函数是什么？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

-----first_value()与last_value()：求最值对应的其他属性

FIRST_VALUE(AREA_CODE)  OVER(PARTITION BY BILL_MONTH ORDER BY SUM(LOCAL_FARE) DESC 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 

LAST_VALUE(AREA_CODE)  OVER(PARTITION BY BILL_MONTH ORDER BY SUM(LOCAL_FARE) DESC 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 

-----rank(),dense_rank()与row_number()：求排序

rank()值相同时排名相同，其后排名跳跃不连续

RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW

dense_rank()值相同时排名相同，其后排名连续不跳跃

DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW

row_number()值相同时排名不相等，其后排名连续不跳跃

ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW

-----------lag()与lead()：求之前或之后的第N行 

lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。

举例如下：
SQL> select *  from kkk;                                          
                                                                  
        ID NAME                                                   
---------- --------------------                                   
         1 1name                                                  
         2 2name                                                  
         3 3name                                                  
         4 4name                                                  
         5 5name                                                  
SQL> select id,name,lag(name,1,0) over(order by id) from kkk; 
                                                                  
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      
---------- -------------------- ----------------------------      
         1 1name                0                                 
         2 2name                1name                             
         3 3name                2name                             
         4 4name                3name                             
         5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;
                                                                  
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                2name                             
         2 2name                3name                             
         3 3name                4name                             
         4 4name                5name                             
         5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;                                                                                                               
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                3name                             
         2 2name                4name                             
         3 3name                5name                             
         4 4name                0                                 
         5 5name                0  
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;                                  
                                                                                  
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                    
---------- -------------------- ------------------------------                    
         1 1name                2name                                             
         2 2name                3name                                             
         3 3name                4name                                             
         4 4name                5name                                             
         5 5name                linjiqin  

-------------rollup()与cube()：排列组合分组 

1)、group by rollup(a, b, c)：
首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，
其后再对(a)进行group by，
最后对全表进行汇总操作。

2)、group by cube(a, b, c)：
则首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后对全表进行汇总操作。

-----------max(),min(),sum()与avg()：求移动的最值总和与平均值

SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE  ORDER BY TO_NUMBER(BILL_MONTH)  RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE ORDER BY TO_NUMBER(BILL_MONTH)  RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE  ORDER BY TO_NUMBER(BILL_MONTH)   RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE ORDER BY TO_NUMBER(BILL_MONTH)  RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 

----unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录

--unbounded：不受控制的，无限的

--preceding：在...之前

--following：在...之后

----ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总

----ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总

----ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总

----ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

----------累加

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

------------排序

ROW_NUMBER() over (PARTITION byhexuncookieid order by cycookied) order

-------------无需排序给行号

select row_number() over () as rowid, cust_idfrom dms.fund_trans_log2 limit 10;