简介
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
绝大多数情况,sql语句处理数据是行为基本单位,一行一行的对数据操作。窗口函数则是可以对行数据进行分组,将多行数据分成一组,然后进行组间操作或者组内操作。
详解
基本语法
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
没有partition by分组默认每行一组
简单使用
创建测试表并插入测试数据:
CREATE TABLE student(
sno INT,
class INT,
score INT
);
INSERT student VALUES(001,1,99);
INSERT student VALUES(002,1,89);
INSERT student VALUES(003,1,60);
INSERT student VALUES(004,2,98);
INSERT student VALUES(005,2,67);
INSERT student VALUES(006,2,87);
INSERT student VALUES(007,1,77);
INSERT student VALUES(008,2,76);
SELECT * FROM student;
查询各班级内按分数排序的数据:
select *,
rank() over (partition by class order by score desc) as ranking #按班级分组,组内按分数排序
from student;
窗口函数讲解
窗口函数写在select子句中。
排序窗口函数
有rank()、dense_rank()、row_number()三个。
rank()函数值相等排名一样,但是占用位置。
dense_rank()函数值相等的排名一样,但是不占用位置。
row_number()函数值相等的也分先后排名。
INSERT student VALUES(009,2,76); #再插入一行测试数据
select *,
rank() over (order by score desc) as rank,
dense_rank() over (order by score desc) as dense_rank,
row_number() over (order by score desc) as row_number
from student;
执行结果:
聚合函数
聚合函数也可当窗口函数使用,看以下例子:
select *,
sum(score) over (order by sno ) as sum1,
avg(score) over (order by score ) as avg1,
min(score) over (order by sno ) as min1,
count(class) over (order by sno ) as count1,
max(score) over (order by sno ) as max1
from student;
看途中红色标记的两行数据。只有order by语句没有partition by分组语句,但是这两行数据一样,说明了只有order by语句时,排名相等的行数据是一组。
移动平均
使用窗口函数还可以实现移动平均,直接上实例:
select *,
avg(score) over (order by sno ROWS 2 preceding ) as avg1
from student;
over子句中添加了rows 2 preceding,查询的平均值是包含本行以及上两行数据计算出来的。
结语
注意:不同的数据库和不同的版本可能支持的窗口函数功能不同,建议使用高版本的数据库学习。