这里介绍SQL中的窗口函数Window Function

概述

窗口函数是一种可以对查询结果集中的一组行记录进行计算的函数。与GROUPBY子句相比，其提供了在不破坏查询结果原始行的前提下执行聚合、排序、排名等操作的能力

窗口函数的语法规则如下所示

-- 用法1
＜窗口函数> OVER()

-- 用法2
＜窗口函数> OVER(
    [PARTITION BY 子句]
    [ORDER BY 子句]
    [ROWS/RANGE 子句]
)

使用窗口函数时，OVER子句是必须存在的，不可以省略。例如用法1。同时在OVER子句中，我们可以可选地使用PARTITION BY子句、ORDER BY子句、ROWS/RANGE子句。例如用法2。具体地：

PARTITION BY子句：其定义了窗口函数的分区方式。它指定了窗口函数的作用范围，即所谓的窗口大小。窗口函数将在每个分区内进行计算
ORDER BY子句：其定义窗口函数在计算前，窗口中各条记录的的排序顺序
ROWS/RANGE子句：其定义了开窗范围

建表语句

这里建立一张表，以供后续测试

CREATE TABLE `stu_info` (
    `id` int NOT NULL COMMENT '主键',
    `name` varchar(255) DEFAULT NULL COMMENT '姓名',
    `age` int DEFAULT NULL COMMENT '年龄',
    `sex` varchar(255) DEFAULT NULL COMMENT '性别',
    `score` int DEFAULT NULL COMMENT '分数',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='学生信息表';

INSERT INTO stu_info (id, name, age, sex, score) VALUES(1,'Tony',18,'男',12);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(2,'Bob',11,'男',28);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(3,'Amy',22,'女',22);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(4,'Tom',3,'男',45);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(5,'Aaron',14,'女',74);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(6,'David',9,'男',85);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(7,'小王',7,'男',99);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(8,'张三',7,'男',53);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(9,'李四',7,'男',71);
INSERT INTO stu_info (id, name, age, sex, score) VALUES(10,'王二麻子',7,'男',84);

常用窗口函数

ROW_NUMBER 函数

其会为窗口中每一个行记录分配一个唯一的序号。这个序号是根据窗口中记录的排序顺序进行分配的。从1开始、依次递增。这里，我们期望对每个性别而言，按年龄大小顺序进行排名

-- 通过 PARTITION BY sex 子句 实现 将记录按性别进行分区
-- 通过 ORDER BY age 子句 实现 对各分区内对记录按年龄升序排序
-- 使用 ROW_NUMBER() 窗口函数 为各分区中的每条记录依次分配一个序号
SELECT
    *, ROW_NUMBER() OVER (PARTITION BY sex ORDER BY age ASC) as rank_num
FROM stu_info;

RANK 函数

其会为窗口中每一个行记录分配一个排名。但与ROW_NUMBER函数不同的是，对于相同数值会有相同排名，并且下一个排名值会跳过相应数量的排名

-- 对每个性别而言，按年龄大小顺序进行排名
SELECT
    *, RANK() OVER (PARTITION BY sex ORDER BY age ASC) as rank_num
FROM stu_info;

DENSE_RANK 函数

其会为窗口中每一个行记录分配一个排名。虽然对于相同数值会有相同排名，但与RANK函数不同的是，下一个排名值不会跳过相应数量的排名

-- 对每个性别而言，按年龄大小顺序进行排名
SELECT
    *, DENSE_RANK() OVER (PARTITION BY sex ORDER BY age ASC) as rank_num
FROM stu_info;

FIRST_VALUE/LAST_VALUE 函数

FIRST_VALUE/LAST_VALUE函数会返回窗口中第一行/最后一行的指定字段的值。但需要注意的是，LAST_VALUE 函数的结果会受到开窗范围的影响

-- 对每个性别而言，按分数大小升序排序。获取相应性别下分数最低、最高人的姓名
SELECT
    *,
    FIRST_VALUE(name) OVER (PARTITION BY sex ORDER BY score) as min_score_name,
    LAST_VALUE(name) OVER (PARTITION BY sex ORDER BY score) as max_score_name
FROM stu_info;

LAG/LEAD 函数

LAG/LEAD(<指定字段名>, n, default)函数：可用来获取当前行的前n行/后n行的指定字段的值。其中，default参数表示没有可用的行取不到值时返回的默认值

SELECT
    *,
    -- 当前行的上一行的值，取不到值时默认为O
    LAG(score, 1, 0) OVER (PARTITION BY sex ORDER BY age ) as last_score,
    -- 当前行的下一行的值，取不到值时默认为0
    LEAD(score, 1, 0) OVER (PARTITION BY sex ORDER BY age) as next_score,
    -- 当前行与上一行的分数差值
    score - LAG(score, 1, 0) OVER (PARTITION BY sex ORDER BY age) as last_diff_score,
    -- 当前行与下一行的分数差值
    score - LEAD(score, 1, 0) OVER (PARTITION BY sex ORDER BY age) as next_diff_score
FROM stu_info;

开窗范围

ROWS 子句

ROWS 子句是基于分区中的物理行数来定义开窗范围。其中，行数的表示法如下：

CURRENT ROW：ROWS子句下，指分区中的当前行
UNBOUNDED PRECEDING：ROWS子句下，指分区中的第一行
UNBOUNDED FOLLOWING：ROWS子句下，指分区中的最后一行
[num] PRECEDING：ROWS子句下，表示当前行的前num行
[num] FOLLOWING：ROWS子句下，表示当前行的后num行

完整的ROWS子句，可以使用下述两种写法

-- 表示开窗范围为：指定的开始行 到当前行
ROWS <开始行>

-- 表示开窗范围为：指定的开始行 到 指定的结束行
ROWS BETWEEN <开始行> and ＜结束行>

示例如下所示

-- ROWS 开窗范围
SELECT
    *,
    -- 开窗范围：分区的 UNBOUNDED PRECEDING第一行 到 分区的 UNBOUNDED FOLLOWING最后一行
    LAST_VALUE(name) OVER (PARTITION BY sex ORDER BY score ROWS	BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as max_score_name_1,
    -- 开窗范围：分区的 UNBOUNDED PRECEDING第一行 到 分区的 CURRENT ROW当前行
    LAST_VALUE (name) OVER (PARTITION BY sex ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as max_score_name_2,
    -- 开窗范围：分区的 CURRENT ROW当前行 到 分区的 UNBOUNDED FOLLOWING最后一行
    FIRST_VALUE (name) OVER (PARTITION BY sex ORDER BY score ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as min_score_name_3,
    -- 开窗范围：分区的 1 PRECEDING当前行的前1行 到 分区的 2 FOLLOWING当前行的后2行
    FIRST_VALUE (name) OVER (PARTITION BY sex ORDER BY score ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ) as min_score_name_4,
    -- 开窗范围：分区的 1 PRECEDING当前行的前1行 到 分区的 2 FOLLOWING当前行的后2行
    LAST_VALUE (name) OVER (PARTITION BY sex ORDER BY score ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ) as max_score_name_5
FROM stu_info;

RANGE 子句

RANGE 子句是基于分区中的排序字段的值来定义开窗范围。值的表示法如下：

CURRENT ROW：RANGE子句下，指分区中的当前行的排序字段的值
UNBOUNDED PRECEDING：RANGE子句下，指分区中的第一行的排序字段的值
UNBOUNDED FOLLOWING：RANGE子句下，指分区中的最后一行的排序字段的值
[num] PRECEDIN：RANGE子句下，表示分区中的当前行的排序字段的值减去 num后的值
[num] FOLLOWING：RANGE子句下，表示分区中的当前行的排序字段的值加上 num后的值
INTERVAL [num] [时间单位] PRECEDING：对于排序字段为时间类型时，可以使用该语法表示分区中的当前行的排序字段的时间值减去 num [时间单位]的时间值。例如，INTERVAL 2 DAY PRECEDING 表示当前行的排序字段对应时间值的前2天
INTERVAL [num] [时间单位] FOLLOWING：对于排序字段为时间类型时，可以使用该语法表示分区中的当前行的排序字段的时间值加上 num [时间单位]的时间值。例如，INTERVAL 3 HOUR FOLLOWING 表示当前行的排序字段对应时间值的下3个小时

完整的RANGE子句，可以使用下述两种写法

-- 表示开窗范围为：排序字段的值从 指定的起始值 到 当前行排序字段对应的值
RANGE <起始值>

-- 表示开窗范围为：排序字段的值从 指定的起始值 到 指定的结束值
RANGE BETWEEN <起始值> and <结束值>

示例如下

-- RANGE 开窗范围
SELECT
    *,
    -- 开窗范围：sex字段的范围在［当前行的sex字段的值，当前行的sex字段的值] 之间
    LAST_VALUE(name) OVER (PARTITION BY sex ORDER BY age RANGE BETWEEN CURRENT ROW AND CURRENT ROW) as max_age_1,
    -- 开窗范围：sex字段的范围在 [当前行的sex字段的值-1，当前行的sex字段的值+2] 之间
    LAST_VALUE(name) OVER (PARTITION BY sex ORDER BY age RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING ) as max_age_2,
    -- 开窗范围：sex字段的范围在 [当前行的sex字段的值-4，分区中的最后一行的sex字段的值] 之间
    FIRST_VALUE(name) OVER (PARTITION BY SeX ORDER BY age RANGE BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) as min_age_3,
    -- 开窗范围：sex字段的范围在 [当前行的sex字段的值-4，分区中的最后一行的sex字段的值］之间
    LAST_VALUE(name) OVER (PARTITION BY sex ORDER BY age RANGE BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) as max_age_4
FROM stu_info;

聚合函数

事实上，除了专用的窗口函数。聚合函数也可作为窗口函数进行使用

SELECT
    *,
    MAX(score) OVER (PARTITION BY sex ORDER BY id) as max_score,
    MIN(score) OVER (PARTITION BY sex ORDER BY id) as min_score
FROM stu_info;

xyZGHio

SQL之窗口函数Window Function

概述