轉(zhuǎn)自：

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html


OVER(PARTITION BY)函數(shù)介紹

      開(kāi)窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，舉例如下：
1：over后的寫(xiě)法：    
   over（order by salary） 按照salary排序進(jìn)行累計(jì)，order by是個(gè)默認(rèn)的開(kāi)窗函數(shù)
   over（partition by deptno）按照部門(mén)分區(qū)

 

 

2：開(kāi)窗的窗口范圍：
over（order by salary range between 5 preceding and 5 following）：窗口范圍為當(dāng)前行數(shù)據(jù)幅度減5加5后的范圍內(nèi)的。

舉例：

 

 

 

 

舉例：

 

 

 

 

3、與over函數(shù)結(jié)合的幾個(gè)函數(shù)介紹

下面以班級(jí)成績(jī)表t2來(lái)說(shuō)明其應(yīng)用

t2表信息如下：
cfe        2        74
dss        1        95
ffd        1        95
fda        1        80
gds        2        92
gf         3        99
ddd        3        99
adf        3        45
asdf       3        55
3dd        3        78

select * from                                                                      
    (                                                                           
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )                                                                           
    where mm=1；
得到的結(jié)果是:
dss        1        95        1
ffd        1        95        1
gds        2        92        1
gf         3        99        1
ddd        3        99        1 

注意：
    1.在求第一名成績(jī)的時(shí)候，不能用row_number()，因?yàn)槿绻嘤袃蓚€(gè)并列第一，row_number()只返回一個(gè)結(jié)果;
select * from                                                                      
    (                                                                           
    select name,class,s,row_number()over(partition by class order by s desc) mm from t2
    )                                                                           
    where mm=1；
1        95        1  --95有兩名但是只顯示一個(gè)
2        92        1
3        99        1 --99有兩名但也只顯示一個(gè)

    2.rank()和dense_rank()可以將所有的都查找出來(lái)：
如上可以看到采用rank可以將并列第一名的都查找出來(lái)；
     rank()和dense_rank()區(qū)別：
     --rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名；
select name,class,s,rank()over(partition by class order by s desc) mm from t2
dss        1        95        1
ffd        1        95        1
fda        1        80        3 --直接就跳到了第三
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5
     --dense_rank()l是連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名
select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
dss        1        95        1
ffd        1        95        1
fda        1        80        2 --連續(xù)排序（仍為2）
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4

--sum()over（）的使用
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根據(jù)班級(jí)進(jìn)行分?jǐn)?shù)求和
dss        1        95        190  --由于兩個(gè)95都是第一名，所以累加時(shí)是兩個(gè)第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

first_value() over()和last_value() over()的使用  

--找出這三條電路每條電路的第一條記錄類型和最后一條記錄類型

 

注：rows BETWEEN unbounded preceding AND unbounded following 的使用

--取last_value時(shí)不使用rows BETWEEN unbounded preceding AND unbounded following的結(jié)果

 

 

 

數(shù)據(jù)如下：

 

 

取出該電路的第一條記錄，加上ignore nulls后，如果第一條是判斷的那個(gè)字段是空的，則默認(rèn)取下一條，結(jié)果如下所示：

 

 

lead(expresstion,<offset>,<default>)
with a as 
(select 1 id,'a' name from dual
 union
 select 2 id,'b' name from dual
 union
 select 3 id,'c' name from dual
 union
 select 4 id,'d' name from dual
 union
 select 5 id,'e' name from dual
) 
select id,name,lead(id,1,'')over(order by name) from a;

--ratio_to_report(a)函數(shù)用法 Ratio_to_report() 括號(hào)中就是分子，over() 括號(hào)中就是分母
with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over(partition by a) b from a 
order by a; 

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over() b from a --分母缺省就是整個(gè)占比
order by a; 

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over() b from a
group by a order by a;--分組后的占比