MySQL8 窗口函数

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: MySQL 8 引入了窗口函数,这是一种强大的分析工具,可以在查询结果集中执行计算而无需将数据分组到多个输出行中。本文介绍了窗口函数的基本概念和使用方法,并通过几个实际案例展示了如何使用窗口函数进行成绩和排名统计、销售数据分析等操作。

MySQL8 还是有很多重量级变化的,一些底层优化大家在使用中有时候不易察觉,但是有一些用法,还是带给我们耳目一新的感觉,今天松哥和大家分享一下 MySQL8 里边的窗口函数。

一 什么是窗口函数

在 MySQL 8 中,窗口函数(Window Functions)是一类强大的分析函数,允许你在查询结果集上执行计算,而无需将数据分组到多个输出行中。窗口函数通常与 OVER() 子句一起使用,以指定数据窗口,即窗口函数将要在其上执行计算的行集。

简单来说,窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中。

窗口函数的格式类似下面这样:

sql

代码解读

复制代码

<窗口函数> OVER ([PARTITION BY <分组列> [, <分组列>...]]
                     [ORDER BY <排序列> [ASC | DESC] [, <排序列> [ASC | DESC]]...]
                     [<rows or range clause>])
  • <窗口函数> : 定义要在窗口中计算的聚合函数或其它分析函数,如 COUNTRANKSUM 等。
  • OVER : 窗口函数的核心关键字。
  • PARTITION BY : 定义要用来分组的一组列名。
  • ORDER BY : 定义用来排序的一组列名。
  • <rows or range clause> : 定义窗口的行集合。默认为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,表示窗口包括从窗口开始到当前行的所有行。

接下来我们通过一个实际案例来体会下窗口函数。

二 窗口函数实践

2.1 统计成绩和排名

假设我有如下一张表:

我现在想要计算学生的考试总成绩以及单科成绩排名,利用窗口函数就能快速搞定,如下:

sql

代码解读

复制代码

SELECT name,subject,score,
SUM(score) OVER(PARTITION by name) AS '总分',
DENSE_RANK() OVER(PARTITION by subject ORDER BY score DESC) AS '学科排名'
from student

和窗口函数相关的就两列:

  • sum 求总分,over 中按照 name 进行分组,相当于就是计算每个人的总分。
  • dense_rank 是排序,这个函数会考虑并列的情况,但是并列并不影响排序,因为是计算每个人单科排名,所以就按照学科分组之后按照 score 排序。

最终执行结果如下:

2.2 销售统计

假设我有如下一张表:

这是一个名为 sales 的表,其中包含 id(销售记录 ID)、product_id(产品 ID)、sale_date(销售日期)和 amount(销售额)等字段。

现在有如下几个需求,大家把这几个需求搞懂了,基本上窗口函数就会用了。

计算累计销售额

需求:按产品 ID 分组,计算每个产品的累计销售额。

sql

代码解读

复制代码

SELECT 
    id, 
    product_id, 
    sale_date, 
    amount, 
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS '累计销售额'
FROM 
    sales;

SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS '累计销售额' 表示按 product_id 分组,按 sale_date 排序,计算每个产品的累计销售额。

最终查询结果如下:

计算移动平均值

需求:按产品 ID 分组,计算每个产品的最近 3 笔销售记录的移动平均销售额。

sql

代码解读

复制代码

SELECT 
    id, 
    product_id, 
    sale_date, 
    amount, 
    AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS '移动平均销售额'
FROM 
    sales;

AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS '移动平均销售额' 表示按 product_id 分组,按 sale_date 排序,计算当前行及前两行的平均销售额。

最终查询结果如下:

计算排名

需求:按产品 ID 分组,计算每个销售记录在该产品中的排名。

sql

代码解读

复制代码

SELECT 
    id, 
    product_id, 
    sale_date, 
    amount, 
    RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS '销售金额排名'
FROM 
    sales;

RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS '销售金额排名' 表示按 product_id 分组,按 amount 降序排序,计算每个销售记录在该产品中的排名。

最终查询结果如下:

计算百分比排名

需求:按产品 ID 分组,计算每个销售记录在该产品中的百分比排名。

sql

代码解读

复制代码

SELECT 
    id, 
    product_id, 
    sale_date, 
    amount, 
    PERCENT_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS '百分比排名'
FROM 
    sales;

PERCENT_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS '百分比排名' 表示按 product_id 分组,按 amount 降序排序,计算每个销售记录在该产品中的百分比排名。

最终查询结果如下:

计算前后行的差值

需求:按产品 ID 分组,计算每个销售记录与上一个销售记录之间的销售额差值。

sql

代码解读

复制代码

SELECT 
    id, 
    product_id, 
    sale_date, 
    amount, 
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS '上个销售记录',
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS '差额'
FROM 
    sales;

LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date):按 product_id 分组,按 sale_date 排序,获取当前行的上一行的 amount 值。amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date):计算当前行与上一行的销售额差值。

最终查询结果如下:

计算第一个和最后一个值

需求:按产品 ID 分组,计算每个产品的第一个和最后一个销售日期。

sql

代码解读

复制代码

SELECT 
    product_id, 
    MIN(sale_date) OVER (PARTITION BY product_id) AS '第一个销售日期', 
    MAX(sale_date) OVER (PARTITION BY product_id) AS '最后一个销售日期'
FROM 
    sales;

MIN(sale_date) OVER (PARTITION BY product_id):按product_id分组,计算每个产品的第一个销售日期。MAX(sale_date) OVER (PARTITION BY product_id):按product_id分组,计算每个产品的最后一个销售日期。

最终查询结果如下:

好啦,通过这几个小小案例,小伙伴们明白窗口函数了吧~


转载来源:https://juejin.cn/post/7426765897326149658

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
125 0
|
5月前
|
关系型数据库 MySQL 数据挖掘
MySQL窗口函数:原理和使用
MySQL窗口函数:原理和使用
|
5月前
|
关系型数据库 MySQL 数据挖掘
MySQL窗口函数详解(概念+练习+实战)
MySQL窗口函数详解(概念+练习+实战)
1045 1
|
5月前
|
关系型数据库 MySQL
MYSQL 窗口函数
MYSQL 窗口函数
|
5月前
|
关系型数据库 MySQL 数据挖掘
MySQL 8窗口函数详解:高效数据处理的必备技能
MySQL 8窗口函数详解:高效数据处理的必备技能
228 3
|
5月前
|
关系型数据库 MySQL Serverless
【随手记】MySQL窗口函数计算累加和
【随手记】MySQL窗口函数计算累加和
437 0
|
6月前
|
SQL Oracle 关系型数据库
MySQL窗口函数怎么用
这段内容介绍了如何使用窗口函数(Window Function)在 MySQL 8.x 中进行更高效、简洁的数据分析和计算。窗口函数允许我们在查询结果集的特定窗口(或分区)内执行计算,如聚合、排序和行号生成。文章以一个复杂的 SQL 查询作为示例,展示了如何使用窗口函数简化这个计算排名的过程。
|
关系型数据库 MySQL
MySQL8.0新特性之窗口函数学习
MySQL8.0新特性之窗口函数学习
154 0
|
SQL 数据挖掘 关系型数据库
MySQL8新特性窗口函数详解
MySQL8新特性窗口函数详解
256 0
|
SQL 存储 关系型数据库
Mysql数据库基础第八章:窗口函数和公用表表达式(CTE)
# 1.窗口函数 MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中。
Mysql数据库基础第八章:窗口函数和公用表表达式(CTE)

相关产品

  • 云数据库 RDS MySQL 版