SQL之窗口函数

cultureSun

发布于 2023-05-18 06:43:11

40600

代码可运行

文章被收录于专栏：cultureSun学安全cultureSun学安全

运行总次数：0

代码可运行

简介

窗口函数，也叫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;

SQL

查询各班级内按分数排序的数据：

select *,
   rank() over (partition by class order by score desc) as ranking  #按班级分组，组内按分数排序
from student;

SQL

窗口函数讲解

窗口函数写在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;

SQL

执行结果：

聚合函数

聚合函数也可当窗口函数使用，看以下例子：

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;

SQL

看途中红色标记的两行数据。只有order by语句没有partition by分组语句，但是这两行数据一样，说明了只有order by语句时，排名相等的行数据是一组。

移动平均

使用窗口函数还可以实现移动平均，直接上实例：

select *,
        avg(score) over (order by sno ROWS 2 preceding ) as avg1
from student;

SQL

over子句中添加了rows 2 preceding，查询的平均值是包含本行以及上两行数据计算出来的。

结语

注意：不同的数据库和不同的版本可能支持的窗口函数功能不同，建议使用高版本的数据库学习。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度