Oracle分析函数over及开窗函数

55e0bd563s

　　来源：http://www.365master.com/manage/database/oracle/20080730/36178.shtml
　　
　　
　　分析函数over 及开窗函数
　　一：分析函数over
　　Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
　　下面通过几个例子来说明其应用。
　　1：统计某商店的营业额。
　　date    sale
　　1          20
　　2          15
　　3          14
　　4          18
　　5           30
　　规则：按天统计：每天都统计前面几天的总额
　　得到的结果：
　　DATE   SALE   SUM
　　----- -------- ------
　　1        20 20     --1天
　　2        15 35     --1天+2天
　　3        14 49     --1天+2天+3天
　　4        18            67 .
　　5        30            97 .
　　2:统计各班成绩第一名的同学信息
　　NAME   CLASS    S
　　----- ----- ----------------------
　　fda        1          80
　　ffd         1          78
　　dss        1          95
　　cfe         2          74
　　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  --

　　
　　得到结果：
　　NAME  CLASS  S  MM
　　----- ----- ---------------------- ----------------------
　　dss     1        95   1
　　gds     2        92   1
　　gf        3        99   1
　　ddd     3        99   1
　　注意：
　　1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
　　2.rank()和dense_rank()的区别是：
　　--rank()是跳跃排序，有两个第二名时接下来就是第四名
　　--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
　　3.分类统计 (并显示信息)
　　A   B   C
　　-- -- ----------------------
　　m   a  2
　　n   a  3
　　m  a   2
　　n   b   2
　　n   b   1
　　x   b   3
　　x   b   2
　　x   b   4
　　h   b   3
　　

select a,c,sum(c)over(partition by a) from t2

　　
　　得到结果：
　　A B C    SUM(C)OVER(PARTITIONBYA)
　　-- -- ------- ------------------------
　　h b 3     3
　　m a 2    4
　　m a 2    4
　　n a 3     6
　　n b 2     6
　　n b 1     6
　　x b 3     9
　　x b 2     9
　　x b 4     9
　　如果用sum，group by 则只能得到
　　A SUM(C)
　　-- ----------------------
　　h   3
　　m  4
　　n   6
　　x   9
　　无法得到B列值
　　=====
　　二：开窗函数
　　开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
　　1.
　　

over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数  over(partition by deptno)按照部门分区

　　
　　2.
　　

over(order by salary range between 5 preceding and 5 following)

　　
　　每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
　　例如:对于以下列
　　aa
　　1
　　2
　　2
　　2
　　3
　　4
　　5
　　6
　　7
　　9
　　得出的结果是
　　A  A   SUM
　　---------------------- -------------------------------------------------------
　　1   1  0
　　2   1  4
　　2   1  4
　　2   1  4
　　3   1  8
　　4   1  8
　　5   2  2
　　6   1  8
　　7   2  2
　　9   9
　　就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和
　　对于aa=2来说 ，sum=1+2+2+2+3+4=14 ;
　　又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ;
　　3：其它：
　　

over(order by salary rows between 2 preceding and 4 following)

　　
　　每行对应的数据窗口是之前2行，之后4行　
　　

over(order by salary rows between 2 preceding and 4 following)

　　
　　每行对应的数据窗口是之前2行，之后4行
　　4：下面三条语句等效：
　　

over(order by salary rows between unbounded preceding and unbounded following)

　　
　　每行对应的数据窗口是从第一行到最后一行，等效：
　　

over(order by salary range between unbounded preceding and unbounded following)

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://easypm.cn/resources/img/logo.png EasyPM 让项目管理更简单 团队协作更高效