Oracle over()函数

语法：                                                                                                                                          

over()函数是Oracle的分析函数，其语法如下：

函数名（ [ 参数 ] ） over（ [ 分区子句 ]  [ 排序子句 [ 滑动窗口子句 ] ]）

分区子句类似于聚组函数所需要的group by，排序子句类似于SQL语句中的order by，只不过在此语句中还可指定null值排前（nulls first）还是排后（nulls last）。滑动窗口语句这里暂不作说明。

示例1： 删除重复记录                                                                                                                 

SQL> select * from testOverFunc_deweight;
 
     STU_ID STU_NAME             STU_NO               STU_SEX
----------- -------------------- -------------------- -------
          1 张三                 s001                       1
          1 张三                 s002                       0
          3 李四                 s003                       1
          5 王五                 s004                       0
          6 赵六                 s005                       1

SQL> select * from (select stu_id,stu_name,stu_no,stu_sex,row_number()over(partition by stu_name order by 1) rn from testOverFunc_deweight) t where t.rn=1;
 
     STU_ID STU_NAME             STU_NO               STU_SEX         RN
----------- -------------------- -------------------- ------- ----------
          3 李四                 s003                       1          1
          5 王五                 s004                       0          1
          1 张三                 s001                       1          1
          6 赵六                 s005                       1          1

示例2：分组排序                                                                                                                       

SQL> select stu_name,attatched_class,score from testOverFunc_groupby;
 
STU_NAME             ATTATCHED_CLASS       SCORE
-------------------- --------------- -----------
张三                 A                        80
李四                 B                        82
王五                 A                        70
赵六                 A                        90
王朝                 B                        60
马汉                 B                        80

SQL> select stu_name,attatched_class,score,dense_rank()over(order by score) s from testOverFunc_groupby;
 
STU_NAME             ATTATCHED_CLASS       SCORE          S
-------------------- --------------- ----------- ----------
王朝                 B                        60          1
王五                 A                        70          2
马汉                 B                        80          3
张三                 A                        80          3
李四                 B                        82          4
赵六                 A                        90          5
 
6 rows selected

SQL> select stu_name,attatched_class,score,rank()over(order by score) s from testOverFunc_groupby;
 
STU_NAME             ATTATCHED_CLASS       SCORE          S
-------------------- --------------- ----------- ----------
王朝                 B                        60          1
王五                 A                        70          2
马汉                 B                        80          3
张三                 A                        80          3
李四                 B                        82          5
赵六                 A                        90          6
 

函数dense_rank()是连续排序，有两个第三名时仍然跟着第四名。
函数rank()是跳跃排序，有两个第三名时接下来就是第五名。

示例3：分组统计                                                                                                                     

有order by排序子句的是连续求和。

SQL> select stu_name,attatched_class,score,sum(score)over(partition by attatched_class order by score) s from testOverFunc_groupby;
 
STU_NAME             ATTATCHED_CLASS       SCORE          S
-------------------- --------------- ----------- ----------
王五                 A                        70         70
张三                 A                        80        150
赵六                 A                        90        240
王朝                 B                        60         60
马汉                 B                        80        140
李四                 B                        82        222

SQL> select stu_name,attatched_class,score,sum(score)over(partition by attatched_class) s from testOverFunc_groupby;
 
STU_NAME             ATTATCHED_CLASS       SCORE          S
-------------------- --------------- ----------- ----------
张三                 A                        80        240
王五                 A                        70        240
赵六                 A                        90        240
王朝                 B                        60        222
马汉                 B                        80        222
李四                 B                        82        222