MySQL8 窗口函数

本文涉及的产品
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 Tair(兼容Redis),内存型 2GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 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

相关文章
|
7天前
|
弹性计算 双11 开发者
阿里云ECS“99套餐”再升级!双11一站式满足全年算力需求
11月1日,阿里云弹性计算ECS双11活动全面开启,在延续火爆的云服务器“99套餐”外,CPU、GPU及容器等算力产品均迎来了全年最低价。同时,阿里云全新推出简捷版控制台ECS Lite及专属宝塔面板,大幅降低企业和开发者使用ECS云服务器门槛。
|
24天前
|
存储 弹性计算 人工智能
阿里云弹性计算_通用计算专场精华概览 | 2024云栖大会回顾
阿里云弹性计算产品线、存储产品线产品负责人Alex Chen(陈起鲲)及团队内多位专家,和中国电子技术标准化研究院云计算标准负责人陈行、北京望石智慧科技有限公司首席架构师王晓满两位嘉宾,一同带来了题为《通用计算新品发布与行业实践》的专场Session。本次专场内容包括阿里云弹性计算全新发布的产品家族、阿里云第 9 代 ECS 企业级实例、CIPU 2.0技术解读、E-HPC+超算融合、倚天云原生算力解析等内容,并发布了国内首个云超算国家标准。
阿里云弹性计算_通用计算专场精华概览 | 2024云栖大会回顾
|
6天前
|
人工智能 弹性计算 文字识别
基于阿里云文档智能和RAG快速构建企业"第二大脑"
在数字化转型的背景下,企业面临海量文档管理的挑战。传统的文档管理方式效率低下,难以满足业务需求。阿里云推出的文档智能(Document Mind)与检索增强生成(RAG)技术,通过自动化解析和智能检索,极大地提升了文档管理的效率和信息利用的价值。本文介绍了如何利用阿里云的解决方案,快速构建企业专属的“第二大脑”,助力企业在竞争中占据优势。
|
4天前
|
人工智能 自然语言处理 安全
创新不设限,灵码赋新能:通义灵码新功能深度评测
自从2023年通义灵码发布以来,这款基于阿里云通义大模型的AI编码助手迅速成为开发者心中的“明星产品”。它不仅为个人开发者提供强大支持,还帮助企业团队提升研发效率,推动软件开发行业的创新发展。本文将深入探讨通义灵码最新版本的三大新功能:@workspace、@terminal 和 #team docs,分享这些功能如何在实际工作中提高效率的具体案例。
|
8天前
|
自然语言处理 数据可视化 前端开发
从数据提取到管理:合合信息的智能文档处理全方位解析【合合信息智能文档处理百宝箱】
合合信息的智能文档处理“百宝箱”涵盖文档解析、向量化模型、测评工具等,解决了复杂文档解析、大模型问答幻觉、文档解析效果评估、知识库搭建、多语言文档翻译等问题。通过可视化解析工具 TextIn ParseX、向量化模型 acge-embedding 和文档解析测评工具 markdown_tester,百宝箱提升了文档处理的效率和精确度,适用于多种文档格式和语言环境,助力企业实现高效的信息管理和业务支持。
3914 2
从数据提取到管理:合合信息的智能文档处理全方位解析【合合信息智能文档处理百宝箱】
|
11天前
|
负载均衡 算法 网络安全
阿里云WoSign SSL证书申请指南_沃通SSL技术文档
阿里云平台WoSign品牌SSL证书是由阿里云合作伙伴沃通CA提供,上线阿里云平台以来,成为阿里云平台热销的国产品牌证书产品,用户在阿里云平台https://www.aliyun.com/product/cas 可直接下单购买WoSign SSL证书,快捷部署到阿里云产品中。
2181 6
阿里云WoSign SSL证书申请指南_沃通SSL技术文档
|
3天前
|
安全 数据建模 网络安全
2024阿里云双11,WoSign SSL证书优惠券使用攻略
2024阿里云“11.11金秋云创季”活动主会场,阿里云用户通过完成个人或企业实名认证,可以领取不同额度的满减优惠券,叠加折扣优惠。用户购买WoSign SSL证书,如何叠加才能更加优惠呢?
833 1
|
1天前
|
算法 数据建模 网络安全
阿里云SSL证书2024双11优惠,WoSign DV证书220元/年起
2024阿里云11.11金秋云创季火热进行中,活动月期间(2024年11月01日至11月30日),阿里云SSL证书限时优惠,部分证书产品新老同享75折起;通过优惠折扣、叠加满减优惠券等多种方式,阿里云WoSign SSL证书将实现优惠价格新低,DV SSL证书220元/年起。
526 4
|
1天前
|
存储 分布式计算 流计算
实时计算 Flash – 兼容 Flink 的新一代向量化流计算引擎
本文介绍了阿里云开源大数据团队在实时计算领域的最新成果——向量化流计算引擎Flash。文章主要内容包括:Apache Flink 成为业界流计算标准、Flash 核心技术解读、性能测试数据以及在阿里巴巴集团的落地效果。Flash 是一款完全兼容 Apache Flink 的新一代流计算引擎,通过向量化技术和 C++ 实现,大幅提升了性能和成本效益。
418 7
实时计算 Flash – 兼容 Flink 的新一代向量化流计算引擎
|
22天前
|
编解码 Java 程序员
写代码还有专业的编程显示器?
写代码已经十个年头了, 一直都是习惯直接用一台Mac电脑写代码 偶尔接一个显示器, 但是可能因为公司配的显示器不怎么样, 还要接转接头 搞得桌面杂乱无章,分辨率也低,感觉屏幕还是Mac自带的看着舒服