一、前言
由力扣题引发的一次窗口函数的学习,mysql从8.0开始支持窗口函数,使用窗口函数,会令我们的分组查询变得便捷。
二、概念
一、定义
窗口函数:对一个查询SQL,将其结果集按指定的规则进行分区,每个分区可以看作是一个窗口,分区内的每一行,根据其所属分区内的行数据进行函数计算,获取计算结果,作为该行的窗口函数结果值。
二、语法
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
三、常见的排名场景
一、连续排名
例如薪水3000、2000、2000、1000排名结果为1-2-3-4,体现同薪不同名,排名类似于编号。
出现相同的数时,往后面顺移一位。对于这种可以使用窗口函数 row_number() over
如果不需要重复的,可以使用group by 去重后再排序,注意查询字段最好只有排序字段,和窗口函数,窗口函数计算出来的都是数字。
二、排名不连续
同样的薪水还有另一种排名要求:1-2-2-4;对于这一种可以使用窗口函数rank() over (跟着分组/排序条件)
三、排名连续(另一种)
同样的薪水还有另一种连续排名要求:1-2-2-3;对于这种类型的可以使用窗口函数dense_rank() over
最后可以参考以下博文:MySQL-窗口函数 - SmithBee - 博客园
四、力扣题窗口函数应用
select id, if(id % 2 = 1,lead(student,1,student) over (order by id), lag(student,1)over (order by id)) student
from seat
这里使用了两个窗口函数:lead()以及lag()
一、lead():
用于返回当前字段后n行的数据
语法格式:
LEAD(<expression>[,offset[, default_value]]) OVER (
PARTITION BY (expr)
ORDER BY (expr)
)
expression:这里可以放字段名
offset:偏移量,向后取N行
必须是一个非负整数。如果offset
为零,则LEAD()
函数计算expression
当前行的值。
default_value:
如果没有后续行,则LEAD()
函数返回default_value
。例如,如果offset
是1,则最后一行的返回值为default_value
。
如果您未指定default_value
,则函数返回 NULL
。
PARTITION BY子句:
PARTITION BY
子句将结果集中的行划分LEAD()
为应用函数的分区。
如果PARTITION BY
未指定子句,则结果集中的所有行都将被视为单个分区
可用于计算当前行和后一行之间的差异。
二、lag():
用于返回当前字段前n行的数据
语法结构:
LAG(<expression>[,offset[, default_value]]) OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
所有属性和ead():一样
可用于计算当前行和上一行之间的差异。
官网提供的几个窗口函数: