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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL数据库基础:各类窗口函数操作一文详解

前言


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


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


一、窗口函数简介


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


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


5839a303a4a64cd8a4bfdcdc646e1c3a.png



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

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


窗口函数分类


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

image.png


语法结构


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


   <窗口函数> 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')

6463fcf088a446469351d96cdf55f3de.png


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

b7a077d5bb714e44b486dd7b6ee1fd95.png

这里如果我们插入一个相同数值的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');

f9e353645b08448b86ef78ae66787bed.png

我们会发现价格相同的书籍但是序号确不一样,这时我们可以用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;

0d6c7a413fa64eda9f423988272a497a.png

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

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');

6449534630924557825053741e485cbe.png


可以看到,使用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;

f016d158220b43298cea6cef433b182b.png

可以看到,使用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;

06c1e57de1b546d0b0fd3c0515e525d3.png


2.CUME_DIST()函数


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

gif.gif

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;

07c62d55f71343a78bd74d5d62a3936b.png


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;

e2839c1f209d47f798f1d9d8d69ab694.png

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;


fa0ce851f01d4df28e424287f2df7bab.png


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);


8d5ca4409fc844078a919e47811a4774.png

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);

ba221c6b26604e63bae6e0f38804b911.png

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);


f5b405b189f442469c6d433cc9b46d25.png


该函数依赖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);  

922f23dd34f64406a0aa96712034d9f7.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
SQL 运维 关系型数据库
MySQL 中 GRANT 操作会引起复制中断吗?
GRANT 操作并不是一个原子性操作,不管执行成功与否,都会触发一个隐式重载授权表的行为。 在生产环境中需要规范用户创建及授权的操作,不推荐使用 DML 语句去直接变更 mysql.user 表,可能会引发其他的问题,若使用了 DML 语句进行变更, 需要手工执行 flush privileges。
60 4
|
3月前
|
JavaScript 关系型数据库 MySQL
创建nodejs项目并接入mysql,完成用户相关的增删改查的详细操作
创建nodejs项目并接入mysql,完成用户相关的增删改查的详细操作
50 0
|
4月前
|
关系型数据库 MySQL
【mysql技巧】如何在这个mysql语句执行前加个前提,也就是只有表里没有相同数据才进行添加插入操作
【mysql技巧】如何在这个mysql语句执行前加个前提,也就是只有表里没有相同数据才进行添加插入操作
32 1
|
3月前
|
存储 关系型数据库 文件存储
面试题MySQL问题之简单的SELECT操作在MVCC下加锁如何解决
面试题MySQL问题之简单的SELECT操作在MVCC下加锁如何解决
41 2
|
3月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版操作报错合集之从mysql读数据写到hive报错,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
3月前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版操作报错合集之运行mysql to doris pipeline时报错,该如何排查
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
3月前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版操作报错合集之整库同步mysql到starRock提交任务异常,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
3月前
|
SQL 关系型数据库 MySQL
「Python入门」python操作MySQL和SqlServer
**摘要:** 了解如何使用Python的pymysql模块与MySQL数据库交互。首先,通过`pip install pymysql`安装模块。pymysql提供与MySQL的连接功能,例如创建数据库连接、执行SQL查询。在设置好MySQL环境后,使用`pymysql.connect()`建立连接,并通过游标执行SQL(如用户登录验证)。注意防止SQL注入,使用参数化查询。增删改操作需调用`conn.commit()`来保存更改。pymssql模块类似,但导入和连接对象创建略有不同。
48 0
「Python入门」python操作MySQL和SqlServer
|
3月前
|
SQL 存储 关系型数据库
|
4月前
|
关系型数据库 MySQL 数据库
『Django』模型入门教程-操作MySQL
一个后台如果没有数据库可以说废了一半。日常开发中大多数时候都在与数据库打交道。Django 为我们提供了一种更简单的操作数据库的方式。 在 Django 中,模型(Model)是用来定义数据库结构的类。每个模型类通常对应数据库中的一个表,类的属性对应表中的列。通过定义模型,Django 的 ORM(Object-Relational Mapping)可以将 Python 对象映射到数据库表,并提供一套 API 来进行数据库操作。 本文介绍模型的用法。

推荐镜像

更多