oracle中over()分析函数_day1

分析函数用于计算基础组的某种聚合值，分析函数对于每个组返回多行，而聚合函数对于每个组只返回一行。

select day,sale,sum(sale) over (order by day asc ) as 连续求和,sum(sale) over() as 总和 from t_temp;

Sum()函数后面加了over()每个组返回多行，后面就不用加group by。

为了方便理解，可以简单的把over（）函数理解为对分析函数的一种条件解释，也就是给分析函数加条件，以下对sum()、rank()函数结合over的用法进行分析。

1、sum()结合over()

select a.stu_id 学号

,a.stu_name 姓名

,a.stu_class 班级

,a.stu_result 成绩

,sum(stu_result) over (partition by stu_class) 按班级求成绩总和

,sum(stu_result) over (partition by stu_class order by stu_result) 按班级累计成绩

from yangx.Y_CLASS a;

从结果上可以看出sum()函数对部门区分进行了求和统计，partition by 就是统计的范围条件。Order by 可以理解为对统计范围规定了一个统计顺序，一步步统计，逐步累加。

2、rank()结合over()

Rank 函数是分级函数，这个函数必须与over函数一起使用，否则会报一个“缺少窗口函数”错误。

select a.stu_id 学号 ,

a.stu_class 班级,

a.stu_result 成绩,

rank() OVER(partition by a.stu_class ORDER BY a.stu_result desc) as 班级排名

from yangx.Y_CLASS a;

select a.stu_id 学号,a.stu_class 班级,

a.stu_result 成绩,

rank() OVER(ORDER BY a.stu_result desc) as 班级排名

from yangx.Y_CLASS a;

加了partition by是把班级分块，按班级排序，如果不加partition by则对整体进行排序

1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

2.rank()和dense_rank()的区别是：

–rank()是跳跃排序，有两个第二名时接下来就是第四名

–dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

3、lag()与lead()函数

Lag 与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同已字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。

例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

获取当前记录的id，以及下一条记录的id

select t.id id,lead(t.id,1,null) over(order by t.id)next_record_id,t.cphm from yx_test t;

获取当前记录的id，以及上一条记录的id

select t.id id ,lag(t.id, 1, null) over (order by t.id)next_record_id, t.cphm from yx_test t ;

加上partition by 进行分块

select t.id id,lead(t.id, 1, null) over (partition by t.cphm order by t.id) next_record_id,t.cphm from yx_test t order by t.id asc;