MySQL数据库基础：各类窗口函数操作一文详解

2022-10-17 706

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： MySQL数据库基础：各类窗口函数操作一文详解

前言

MySQL从8.0版本开始支持窗口函数，其中，窗口可以理解为数据的集合。MySQL的开窗函数和Hive的开窗函数基本一致，窗口函数也就是在符合某种条件或者某些条件的记录集合中执行的函数，窗口函数会在每条记录上执行。一般我们在日常开发业务上面使用到开窗函数的场景都在聚合统计、数据挖掘之中，常用作聚合计算出某个特征数据。了解并掌握窗口函数将提升我们的数据挖掘能力。

该系列文章将按照这个脉络行文，此系列文章将被纳入我的专栏一文速学SQL各类数据库操作，基本覆盖到使用SQL处理日常业务以及常规的查询建库分析以及复杂操作方方面面的问题。从基础的建库建表逐步入门到处理各类数据库复杂操作，以及专业的SQL常用函数讲解都花费了大量时间和心思创作，如果大家有需要从事数据分析或者数据开发的朋友推荐订阅专栏，将在第一时间学习到最实用常用的知识。此篇博客篇幅较长，值得细读实践一番，我会将精华部分挑出细讲实践。博主会长期维护博文，有错误或者疑惑可以在评论区指出，感谢大家的支持。

一、窗口函数简介

窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好。

窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。

窗口函数可以分为静态窗口函数和动态窗口函数，其中，

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数分类

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数。

语法结构

窗口函数的语法结构形式:

<窗口函数> OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

OVER 关键字指定窗口的范围：

如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。

如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口：

PARTITION BY 子句: 指定窗口函数按照哪些字段进行分组, 分组后, 窗口函数可以在每个分组中分别执行;

ORDER BY 子句: 指定窗口函数按照哪些字段进行排序, 执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号;

FRAME 子句: 为分区中的某个子集定义规则, 可以用来作为滑动窗口使用;

二、窗口函数运用展示

1.创建演示库

CREATE TABLE books_goods (
  t_category_id int, 
  t_category VARCHAR(64), 
  t_name VARCHAR(64),
  t_price FLOAT(32),
  t_upper_time datetime
  );
INSERT INTO books_goods (t_category_id, t_category, t_name, t_price,t_upper_time) VALUES 
(1,'教育','计算机网络',40.0,'2020-11-10 00:00:00'),
(1,'教育','数据结构',58.0,'2020-11-10 00:00:00'),
(1,'教育','计算机系统',55.0,'2020-11-10 00:00:00'),
(2,'科幻','三体',30.0,'2020-11-10 00:00:00'),
(2,'科幻','流浪地球',35.0,'2020-11-10 00:00:00')

2.序号函数

1.ROW_NUMBER()函数

ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

SELECT ROW_NUMBER() over(PARTITION by t_category_id ORDER BY t_price DESC) AS row_num,
 t_category_id, t_category, t_name, t_price
FROM books_goods

这里如果我们插入一个相同数值的price：

INSERT INTO books_goods (t_category_id, t_category, t_name, t_price,t_upper_time) VALUES 
(2,'科幻','死亡游戏',40.0,'2020-11-10 00:00:00');
INSERT INTO books_goods (t_category_id, t_category, t_name, t_price,t_upper_time) VALUES 
(1,'教育','母猪的产后护理',40.0,'2020-11-10 00:00:00');

我们会发现价格相同的书籍但是序号确不一样，这时我们可以用RANK（）函数处理。

2.RANK()函数

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号。

SELECT RANK() over(PARTITION by t_category_id ORDER BY t_price DESC) AS row_num,
 t_category_id, t_category, t_name, t_price
FROM books_goods;

插入一条价格相同排第二的：

INSERT INTO books_goods (t_category_id, t_category, t_name, t_price,t_upper_time) VALUES 
(1,'教育','软件工程导论',55.0,'2020-11-10 00:00:00');

可以看到，使用RANK()函数得出的序号为1、2、2、4、4，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号。

3.DENSE_RANK()函数

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

SELECT DENSE_RANK() over(PARTITION by t_category_id ORDER BY t_price DESC) AS row_num,
 t_category_id, t_category, t_name, t_price
FROM books_goods;

可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。

3.分布函数

1.PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算：

(rank - 1) / (rows - 1)

其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

SELECT PERCENT_RANK() over(PARTITION by t_category_id ORDER BY t_price DESC) AS row_num,
 t_category_id, t_category, t_name, t_price
FROM books_goods;

2.CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

row为当前值的行数，sum(row)分组内总行数。若两值相等则当前行数计算为最大行数：

SELECT CUME_DIST() over(PARTITION by t_category_id ORDER BY t_price DESC) AS rate,
 t_category_id, t_category, t_name, t_price
FROM books_goods;

4.前后函数

1.LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。

例如，查询数据表中前一个商品价格与当前商品价格的差值。

SELECT t_category_id, t_name, t_price, pre_price, 
     t_price - pre_price AS diff_price
     FROM (
     SELECT   t_category_id, t_name, t_price,
     LAG(t_price,1) OVER w AS pre_price
     FROM books_goods
     WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t;

2.LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。

例如，查询数据表中后一个商品价格与当前商品价格的差值。

SELECT t_category_id, t_name, t_price, behind_price, 
     t_price - behind_price AS diff_price
     FROM (
     SELECT   t_category_id, t_name, t_price,
     LEAD(t_price,1) OVER w AS behind_price
     FROM books_goods
     WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t;

5.首尾函数

1.FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

例如，按照价格排序，查询第1个商品的价格信息。

  SELECT t_category_id, t_name, t_price,
  FIRST_VALUE(t_price) OVER w AS first_price
  FROM books_goods
  WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);

2. LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

例如，按照价格排序，查询最后一个商品的价格信息。

  SELECT t_category_id, t_name, t_price,
  LAST_VALUE(t_price) OVER w AS last_price     
  FROM books_goods
  WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);

6.其他函数

1.NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

查询数据表中排名第3和第4的价格信息。

SELECT t_category_id, t_name, t_price, 
    NTH_VALUE(t_price,2) OVER w AS second_price,
    NTH_VALUE(t_price,3) OVER w AS third_price
    FROM books_goods
    WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);

该函数依赖row_number来排序。故存在价格相等但排序不重复的情况。

2.NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

例如，将表中的商品按照价格分为3组。

SELECT 
    NTILE(3) OVER w AS nt,
    t_category_id, t_name, t_price
    FROM books_goods
    WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);