语法:
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