写点什么

MySQL8 窗口函数是真的省事!

作者:江南一点雨
  • 2024-10-19
    广东
  • 本文字数:2404 字

    阅读完需:约 8 分钟

MySQL8 窗口函数是真的省事!

@[toc]MySQL9 已经出来了,MySQL8 相信也慢慢走进各位小伙伴的工作中了。


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

一 什么是窗口函数

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


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


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


<窗口函数> 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 统计成绩和排名

假设我有如下一张表:



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


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 分组,计算每个产品的累计销售额。


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 笔销售记录的移动平均销售额。


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 分组,计算每个销售记录在该产品中的排名。


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 分组,计算每个销售记录在该产品中的百分比排名。


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 分组,计算每个销售记录与上一个销售记录之间的销售额差值。


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 分组,计算每个产品的第一个和最后一个销售日期。


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 分组,计算每个产品的最后一个销售日期。


最终查询结果如下:



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

发布于: 刚刚阅读数: 6
用户头像

技术宅 2019-04-09 加入

Java猿

评论

发布
暂无评论
MySQL8 窗口函数是真的省事!_江南一点雨_InfoQ写作社区