开窗函数学习整理

一、开窗函数是什么

1、定义：

普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。

（聚合函数是对一组值执行计算并返回单一的值的函数。）

2、与普通函数区别：

a、SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
b、聚合函数每组只返回一个值，开窗函数每组可返回多个值。
注：常见主流数据库目前都支持开窗函数，但mysql数据库目前还不支持。

3、调用格式：

函数名(列名) OVER(partition by 列名 order by列名)
OVER关键字表示把函数当成开窗函数而不是聚合函数。对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

unbounded：无界限
preceding：从分区第一行头开始，则为 unbounded。 N为：相对当前行向前的偏移量
following ：与preceding相反,到该分区结束，则为 unbounded。N为：相对当前行向后的偏移量
current row：顾名思义，当前行，偏移量为0
​
例子：
partition by order by asc/desc rows/range between unbounded preceding and current row
partition by order by asc/desc rows/range between 1 preceding and 1 following

4、执行顺序：

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by order by 的执行

二、开窗函数分类

有两类：一类是聚合开窗函数，一类是排序开窗函数。

1、聚合函数：

count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)

有ORDER BY，不写后面的开窗语句（range/rows between … and …)，默认为分组中的第一行到当前行。
没有ORDER BY，不写后面的开窗语句（range/rows between … and …),默认为分组中的所有行。

2、排序函数：

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
​
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

first_value() 、last_value() 、lag() 、lead()使用方法：lag ，lead 分别是向前，向后；lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值

三、应用举例

1、排序：row_number () over()

有如下学生成绩表：students_grades

查询每门课程course_name前三名的学生姓名及成绩，要求输出列格式如下：
course_name, number, stu_name, grades
查询语句如下：

select course_name,
       number,
       stu_name,
       grades
  from (
        select student_name,
               course_name,
               grade,
               row_number() over(partition by couuse_name order by grade desc) as number
          from students_grades
       ) a
 where number<=3

2、聚合：sum() over()

有如下学生成绩表：students_grades

查询每门课程course_name前三名的学生姓名及成绩，要求输出列格式如下：
course_name, number, stu_name, grades
查询语句如下：

-- sum开窗函数
select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
sum(math) over() as sum1,
-- 以按classId分组的所有行作为窗口
sum(math) over(partition by classId) as sum2,
 -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
sum(math) over(partition by classId order by math) as sum3,
 -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
sum(math) over(partition by classId order by math rows between 1 preceding and 2 following) as sum4,
 -- 以按classId分组、按math排序后、按按到当前行(含当前行)的所有行作为窗口，同sum3
sum(math) over(partition by classId order by math rows unbounded preceding and current row) as sum5,
from student_scores where departmentId='department1';

3、几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别

（1） row_number() over()：对相等的值不进行区分，相等的值对应的排名依次增加，序号从1到n连续。
（2） rank() over()：跳跃排序。相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。
（3） dense_rank() over()：连续排序。对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。
（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

参考：
https://www.jianshu.com/p/574fd294e054
from：https://www.douban.com/group/topic/155112949/
http://lxw1234.com/archives/2015/04/190.htm