over partition by ... order by ...用法汇总

原题：---学生成绩表
create table t_student(
  ID number(10),
  name varchar2(100),
  score number(10),
  class_id number(10)
);

insert into t_student values (1,'A',75,1);
insert into t_student values (2,'B',78,2);
insert into t_student values (3,'C',74,1);
insert into t_student values (4,'D',85,2);
insert into t_student values (5,'E',80,1);
insert into t_student values (6,'F',82,2);
insert into t_student values (7,'G',98,1);
insert into t_student values (8,'H',90,2);

insert into t_student values (9,'I',90,2);

---班级表
create t_class(
  ID number(10),
  name varchar2(100)
);

insert into t_class values (1,'一班');
insert into t_class values (2,'二班');

问题：查询两个班前三名的学生成绩?

select * from (select name,score,class_id,rank() over (partition by class_id order by score desc) sm from t_student) where sm<4;

关于over函数说明：

开窗函数，Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

over用法：

RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。

1、over函数的写法：

　　over（partition by class order by sroce） 按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

2、开窗的窗口范围：

　　over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

　　over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

3、与over()函数结合的函数的介绍

   （1）、查询每个班的第一名的成绩

     SELECT * FROM (select t.name,t.class_id,t.score,rank() over(partition by t.class_id order by t.score desc) mm from t_student t) where mm = 1;

SELECT * FROM (select t.name,t.class_id,t.score,row_number() over(partition by t.class_id order by t.score desc) mm from t_student t) where mm = 1;

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

  （2）、rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

select * from (select name,score,class_id,rank() over (partition by class_id order by score desc) sm from t_student) where sm<4;

     （3）、sum()over（）的使用      根据班级进行分数求和

select t.name,t.class_id,t.score,sum(t.score) over(partition by t.class_id order by t.score desc) mm from t_student t;

(4)、first_value() over()和last_value() over()的使用    分别求出第一个和最后一个成绩。

select t.name,t.class_id,t.score,first_value(t.score) over(partition by t.class_id order by t.score desc) mm from t_student t;

select t.name,t.class_id,t.score,last_value(t.score) over(partition by t.class_id order by t.score desc) mm from t_student t;

 

其他函数 用法类似：

　　count() over(partition by ... order by ...)：求分组后的总数。
　　max() over(partition by ... order by ...)：求分组后的最大值。
　　min() over(partition by ... order by ...)：求分组后的最小值。
　　avg() over(partition by ... order by ...)：求分组后的平均值。
　　lag() over(partition by ... order by ...)：取出前n行数据。　　

　lead() over(partition by ... order by ...)：取出后n行数据。

　　ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。

　　percent_rank() over(partition by ... order by ...)：