Oracle - over函数

Oracle - over函数

作用

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

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

分区子句类似于聚组函数所需要的group by，排序子句可看成是SQL语句中的order by，只不过在此语句中还可指定null值排前（nulls first）还是排后（nulls last）。滑动窗口语句就比较复杂，参见manual中的sql reference，总体说明可见：http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409

示例1:剔除重复记录

表temp_b的记录：



执行如下SQL语句：

select * from (
       select city_name, city_code, prov_id, row_number() over(partition by city_name order by 1) r from temp_a
) where r = 1;

剔除表temp_a中city_name相同的记录

示例2:分组排序

成绩表的所有记录如下：



执行如下SQL语句：

select student_name, class, score, dense_rank() over(partition by class order by score desc) 名次 from temp_b;

分班级按成绩排名次排序如下：


提示：
函数dense_rank()是连续排序，有两个第二名时仍然跟着第三名。
函数rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

示例3:分组统计

成绩表的所有记录如下：



执行如下SQL语句：

select student_name, class, score, sum(score) over(partition by class order by score desc) 累计 from temp_b;

班级成绩累计（"连续"求和）结果如下：


提示：此功能用于统计某一段时间每天累计的收入非常有用。


执行如下SQL语句：

select student_name, class, score, sum(score) over(partition by class) 班级总分 from temp_b;

班级成绩总分统计如下：