SQL之开窗函数

SQL之开窗函数

开窗函数与聚合函数一样，也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

主要语法：over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) … )

下面是样例：
创建表：

create table Student
(
  Name  VARCHAR2(32),
  Subjects VARCHAR2(32)，
  Score VARCHAR2(32)
);

插入测试数据：

insert into Student values ('小明', 'Chiness', '90');
insert into Student values ('小芳', 'Chiness', '91');
insert into Student values ('小新', 'Chiness', '89');
insert into Student values ('小田', 'Chiness', '80');
insert into Student values ('小丽', 'Chiness', '95');
insert into Student values ('小明', 'Math', '100');
insert into Student values ('小芳', 'Math', '86');
insert into Student values ('小新', 'Math', '99');
insert into Student values ('小田', 'Math', '80');
insert into Student values ('小丽', 'Math', '60');
insert into Student values ('小明', 'English', '70');
insert into Student values ('小芳', 'English', '88');
insert into Student values ('小新', 'English', '96');
insert into Student values ('小田', 'English', '81');
insert into Student values ('小丽', 'English', '79')

实验：假如现在要查询成绩小于85的有多少人，并在每行中都显示成绩小于85的学生人数。

1、可能有人会这么写

select t.subjects,count(*) from student t where t.score<85;

但这样写语句执行肯定会报错，因为count()是聚合函数，而subjects字段没有包含分组里面。

2、那换个写法呢？

select t.subjects，count(*) from student t where t.score<85 group by t.subjects;

这样查询与我们每行中都显示成绩小于85的学生人数不太相符
3、使用子查询

select a.name,a.subjects,(select count(*) from student t where t.score<85) from  student a where a.score<85;

这么写出来的结果的确是我们想要的结果，但多了个子查询总感觉还是很麻烦。

4、使用开窗函数，它可以大大简化这一实现

select t.name,t.subjects, count(*) over() from student t where t.score < 85;

执行结果：

可以看到这个SQL语句与平常写的语句不同的是我们在count()后面加了一个over关键字。
总结：
over关键字表示把函数当成开窗函数而不是聚合函数，SQL标准允许将所有聚合函数用做开窗函数，使用over关键字来区分这两种用法。
开窗函数的基本格式为：
函数名（列）over(选项)
例如在上面的例子，开窗函数count() over()对于查询结果的每一行都返回所有符合条件的行的记录，over关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围，如果over关键字后的括号中选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
当然over当中也可以设置很多条件，来改变开窗的范围和规则
语法:主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. )
常用函数：

1、row_number() over(partition by … order by …) 
增加一列,类似与增加伪列 
2、rank()  over(partition by … order by …) 
3、dense_rank()  over(partition by … order by …) 
          rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。 
          dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。 
4、count() over(partition by … order by …) 
5、max() over(partition by … order by …) 
6、min() over(partition by … order by …) 
7、sum() over(partition by … order by …) 
8、avg() over(partition by … order by …) 
9、first_value() over(partition by … order by …) 
10、last_value() over(partition by … order by …) 

例如：需要通过分组查询取最大值
因通过group by进行分组查询，select查询的字段除聚合函数之外的字段，group by 中的字段都要列出，且除此以外的字段查询放在select中都会报错，故通过group by进行分组实现取最大值的功能比较困难，这时候就可以使用开窗函数。
示例模板：

select * from 
(select t.*, row_number() over(partition by 分组字段 order by 排序字段 desc ) rn
from tablename t )
where rn=1