mysql--窗口函数

一、前言

由力扣题引发的一次窗口函数的学习，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():一样

可用于计算当前行和上一行之间的差异。

官网提供的几个窗口函数：