资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

【SQL】一张学生表带你学会开窗函数

创作时间:

作者:

@小白创作中心

【SQL】一张学生表带你学会开窗函数

引用

CSDN

https://m.blog.csdn.net/Liu_y_xin/article/details/140521899

SQL中的开窗函数（Window Functions）是一种强大的数据分析工具，它们允许在数据集的子集上执行计算，这个子集由OVER子句定义。开窗函数不会减少结果集中的行数，而是为每一行添加新的列，这些列包含了基于窗口计算得到的值。这些函数非常适合进行排名、移动平均、累计总和等计算。

其基本语法为：

<窗口函数> OVER (
    [PARTITION BY <列名>]  
    [ORDER BY <列名> [ASC|DESC]]  
    [ROWS|RANGE BETWEEN <开始点> AND <结束点>]  
)

我们常见的开窗函数如RANK() 和 DENSE_RANK()，为分区内的行提供一个排名，但在处理并列排名时有所不同。RANK()会在并列后留下空位，而DENSE_RANK()则不会。SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。区别在于聚合函数每组只返回一个值，开窗函数每组可返回多个值，开窗函数不会导致结果集的行数减少。

一 sum over

假设有一个学生表student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并计算每个班级的学生平均分（class_avg_score）。

如果是按照班级计算平均分数的话，非常简单，只需要使用avg()函数就好了，也就是:

select class_id,avg(score)
from student
group By class_id

但是，题目要求返回每个学生的信息以及平均分数，该如何做呢？这时就需要使用开窗函数，将group by中的分组条件放到over()中即可。

select id,name,age,score,class_id,avg(score) over(PARTITION BY class_id) class_avg_score
from student

运行结果如下：

二 sum over order by

在sum over的基础上，该函数还可以实现累加求和：

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

不难看出，其实它只是在原先的基础上，增加了order by子句，可以根据字段进行排序，此时显示的求和就是满足分组条件时，到该条数据为止的累加值，而非是总值。例如：

假设有一个学生表student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（class_sum_score）。

本题在上一题的基础上，增加了按照分数升序的方式累加计算这个条件，此时就要用到order by了。

select id,name,age,score,class_id,sum(score) over(
    PARTITION by class_id
    order by score asc
) as class_sum_score
from student

运行结果如下，大家可自行与上题的结果对比一下差异。

三 rank

rank开窗函数可以用于“排名”相关的操作，也是最经常用到的一个开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。基本语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

假设有一个学生表student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式计算每个班级内的学生的分数排名（ranking）。

select id,name,age,score,class_id,rank()over(
    PARTITION BY class_id order by score desc
) ranking
from student

运行结果:

四 row_number

row_number与Rank函数功能相似，区别在于Row_Number函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。其基本语法也与rank()一样。

ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column

假设有一个学生表student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式给每个班级内的学生分配一个编号（row_number）。

select id,name,age,score,class_id,row_number()over(
    PARTITION BY class_id order by score desc
) row_number
from student

可以看到，其实两道题的代码相差不大，结果也基本一致：

那么，两个函数有什么具体区别呢？

row_number()：对相等的值不进行区分，其实就是行号，相等的值对应的排名不同，序号从1到n连续。
rank()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。

也就是说，两者的本质都是排序，只是对相同值的行处理不同。

五 lag / lead

获取结果集中，按一定排序所排列的当前行的上下相邻若干offset的某个行的某个列(不用结果集的自关联）。通常用于需要比较相邻行数据或进行时间序列分析。

Lag函数用于获取当前行之前的某一列的值。Lead函数用于获取当前行之后的某一列的值。其基本语法为：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
--column_name：要获取值的列名
--offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推
--default_value：可选参数，用于指定当没有前一行时的默认值
--PARTITION BY和ORDER BY子句可选，用于分组和排序数据

假设有一个学生表student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）。

select id,name,age,score,class_id,lag(name,1,null) over (
    PARTITION BY class_id 
    ORDER BY score desc
) prev_name,LEAD(name,1,null) over (
    PARTITION BY class_id 
    ORDER BY score desc
) next_name
from student

运行结果如下：