Posted on 2008-07-04 00:10
追風(fēng)舞者 閱讀(176)
評(píng)論(0) 編輯 收藏 所屬分類:
Database
窗口函數(shù)
Oracle從8.1.6開始提供分析函數(shù)，分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，舉例如下：
1) over(order by salary)
按照salary排序進(jìn)行累計(jì)，order by是個(gè)默認(rèn)的開窗函數(shù)
2) over(partition by deptno)
按照部門分區(qū)
3) over(order by salary range between 50 preceding and 150 following)
每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過50，之后行幅度值不超過150
4) over(order by salary rows between 50 preceding and 150 following)
每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前50行，之后150行
5) over(order by salary rows between unbounded preceding and unbounded following)
每行對(duì)應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：
over(order by salary range between unbounded preceding and unbounded following)