MySQL窗口函数的具体使用
作者:_陈哈哈 发布时间:2024-01-20 07:55:27
之前我给粉丝们搞过个投票,寻找MySQL中那个
最熟悉的陌生人
~~MySQL中哪些技术点是你既熟悉又陌生的?
前三名和我预料大差不差,分别是:
1、游标2、窗口函数3、聚簇索引
这三个点虽然平时用得少,但在面试中却常被问到
。值得一提的是,很多面试官对问题竟然也是一知半解。。
今天我想和你聊聊窗口函数,MySQL从8.0开始支持窗口函数
,或许你们公司的MySQL版本还无法让你爽一把,但我建议你要在本地搞一个试试,真香!
好了,废话不多说,老规矩,先上开胃小菜,看看今天的测试表数据吧。
本文用来演示用的测试表是chh_baozipu
,翻译过来就是emmm…陈哈哈的包子铺。悄悄告诉你,哈哥今年盘了个包子铺卖包子,这张表就是包子铺这半年的利润~
mysql> SELECT * from chh_baozipu ;
+----+--------------------+-------+---------+
| id | product | sales | month |
+----+--------------------+-------+---------+
| 1 | 猪肉大葱包子 | 600 | 2021-11 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 |
| 6 | 猪肉大葱包子 | 1000 | 2021-06 |
| 7 | 面馅儿包子 | 700 | 2021-11 |
| 8 | 面馅儿包子 | 200 | 2021-10 |
| 9 | 面馅儿包子 | 300 | 2021-09 |
| 10 | 面馅儿包子 | 0 | 2021-08 |
| 11 | 面馅儿包子 | 100 | 2021-07 |
| 12 | 面馅儿包子 | 200 | 2021-06 |
+----+--------------------+-------+---------+
12 rows in set (0.00 sec)
怎么说?什么时候来我店里,请大家吃面馅儿包子。
一、什么是窗口函数
1、怎么理解窗口?
其实窗口
的概念是非常重要的,要想学会窗口函数,可不能只知其一不知其二;我们得搞清楚窗口
代表着啥,才知道什么时候该用它。
拿测试表举个简单的例子,统计一下:包子铺的猪肉大葱包子这半年截至每月累计利润
。
SELECT *,SUM(sales) over(ORDER BY `month`) as 累计利润
from chh_baozipu where product='猪肉大葱包子';
mysql> SELECT *,SUM(sales) over(ORDER BY `month`) as 累计利润 from chh_baozipu where product='猪肉大葱包子';
+----+--------------------+-------+---------+--------------+
| id | product | sales | month | 累计利润 |
+----+--------------------+-------+---------+--------------+
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 1000 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 2600 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 3400 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 4400 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 6000 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 6600 |
+----+--------------------+-------+---------+--------------+
6 rows in set (0.00 sec)
从这条SQL可以看出,对于第一行id=6这行的窗口
就是第一行,对于第二行id=5这行的窗口
就是前两行,以此类推(如下图)。
可见,窗口就是范围的意思
,可以理解为一些记录(行)的集合;窗口函数也就是在满足某种条件的记录集合上执行计算
的特殊函数。
对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口
;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口
。看完本文再回来看这句话相信会理解的更透彻[手动狗头]。
2、什么是窗口函数
窗口函数也叫
OLAP函数
(Online Anallytical Processing),可以对数据进行实时分析处理。
窗口函数多用在什么场景?主要有以下两类:
排名问题,例如:查包子铺利润月排名;
TOPN问题,例如:查每种包子利润最高的两个月;
我们常见的窗口函数和聚合函数有这些:
专用窗口函数:
rank()
,dense_rank()
,row_number()
聚合函数:
max()
,min()
,count()
,sum()
,avg()
因为聚合函数也可以放在窗口函数中使用,因此窗口函数和普通聚合函数也很容易被混淆,二者区别如下:
聚合函数
是将多条记录聚合为一条
;而窗口函数是每条记录都会执行,有几条记录执行完还是几条
。聚合函数也可以用于窗口函数中,这个我会举例说明。
二、窗口函数用法
基本语法:
<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>);
-- over关键字用于指定函数的窗口范围,
-- partition by 用于对表分组,
-- order by子句用于对分组后的结果进行排序。
注意:窗口函数是对where或者group by子句处理后的结果再进行二次操作,因此会按照SQL语句的运行顺序,窗口函数一般放在select子句中(from前),例如上一条SQL,可以往上拖着看看~
窗口函数都有哪些?懒得画了,借lulin916
老哥的导图一用~~
序号函数:row_number() / rank() / dense_rank()
分布函数:percent_rank() / cume_dist()
前后函数:lag() / lead()
头尾函数:first_val() / last_val()
其他函数:nth_value() / nfile()
让我们来分别举例看一看:
1、序号函数:row_number() / rank() / dense_rank()
ROW_NUMBER():顺序排序 —— 1、2、3
RANK():并列排序,跳过重复序号 —— 1、1、3
DENSE_RANK():并列排序,不跳过重复序号 —— 1、1、2
mysql> SELECT *,ROW_NUMBER() over(ORDER BY sales desc) as pro_ROW_NUMBER,rank() over(ORDER BY sales desc) as pro_rank,DENSE_RANK() over(ORDER BY sales desc) as pro_DENSE_RANK from chh_baozipu where product='猪肉大葱包子';
+----+--------------------+-------+---------+----------------+----------+----------------+
| id | product | sales | month | pro_ROW_NUMBER | pro_rank | pro_DENSE_RANK |
+----+--------------------+-------+---------+----------------+----------+----------------+
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 1 | 1 | 1 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 2 | 1 | 1 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 3 | 3 | 2 |
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 4 | 3 | 2 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 5 | 5 | 3 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 6 | 6 | 4 |
+----+--------------------+-------+---------+----------------+----------+----------------+
6 rows in set (0.00 sec)
如上述示例可见,三个窗口函数服务与不同的三个典型业务需求,这三种足以应对我们的排序统计。
以后同学们在面试或笔试时被问到时,请不要再说自查询嵌套之类的lowB方案了,不然可别说你认识我~狗子们
2、分布函数:percent_rank() / cume_dist()
这个分布函数基本不用,不讲。有兴趣的同学自行百度~
3、前后函数:lag(expr,n) / lead(expr,n)
expr后面还会涉及到,统一解释一下:expr可以是
表达式
,也可以是列名
前后函数常用于:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))
的expr的值
应用场景:查询前n名同学的成绩和当前同学成绩的差值
内层SQL先通过LAG()函数得到前1名同学的成绩,外层SQL再将当前同学和前1名同学的成绩做差得到成绩差值diff。
这里换成哈哥的测试表就有点尬了。。但你肯定明白这意思,来,让我们尬查一下:
mysql> SELECT *,lag(sales,1) over win as pro_lag,lead(sales,1) over win as pro_lead from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY sales desc);
+----+--------------------+-------+---------+---------+----------+
| id | product | sales | month | pro_lag | pro_lead |
+----+--------------------+-------+---------+---------+----------+
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | NULL | 1600 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 1600 | 1000 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 1600 | 1000 |
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 1000 | 800 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 1000 | 600 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 800 | NULL |
| 7 | 面馅儿包子 | 700 | 2021-11 | NULL | 300 |
| 9 | 面馅儿包子 | 300 | 2021-09 | 700 | 200 |
| 8 | 面馅儿包子 | 200 | 2021-10 | 300 | 200 |
| 12 | 面馅儿包子 | 200 | 2021-06 | 200 | 100 |
| 11 | 面馅儿包子 | 100 | 2021-07 | 200 | 0 |
| 10 | 面馅儿包子 | 0 | 2021-08 | 100 | NULL |
+----+--------------------+-------+---------+---------+----------+
12 rows in set (0.00 sec)
这里我想问一下同学们是不是发现这条SQL和前面SQL不同?有哪几个地方不同呢?
SELECT *,
lag(sales,1) over win as pro_lag,
lead(sales,1) over win as pro_lead
from chh_baozipu where product='猪肉大葱包子'
WINDOW win as (PARTITION BY product ORDER BY sales desc);
1、把窗口提取出来设置了别名
其实,这种是把窗口提了出来,设置别名为:win
,像我们写SQL时用别名一样,这样看起来会简洁舒服一些,是吧。
有人问程序员要什么简洁?别人看不懂才会觉得代码牛B啊。这种同学一看就是没被社会毒打过,等你遇到百年一见的祖传代码时候,你就懂啥叫大道至简了(借胖哥图一用)。
2、窗口中增加了PARTITION BY product
这个关键字在over子句中,也就意味着控制了窗口的内容,在上面基础语法中我告诉你over中有两个个关键词:
partition by
是对窗口内容进行分组处理;order by
是对窗口内容分组后进行排序;
其实,还有更有意思的控制窗口范围的方式~~
对于滑动窗口的范围指定
,有两种方式,基于行和基于范围,我跟你着重介绍常用的基于行
来控制窗口范围;
通常使用BETWEEN frame_start AND frame_end语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:
CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用
UNBOUNDED PRECEDING 边界是分区中的第一行
UNBOUNDED FOLLOWING 边界是分区中的最后一行
expr PRECEDING 边界是当前行减去expr的值
expr FOLLOWING 边界是当前行加上expr的值
来看几个例子:
①计算当前行与前n行(共n+1行)的聚合窗口函数
下例中控制窗口大小为当前月+前两个月的利润总和,来看一下效果:
SELECT *,SUM(sales) OVER win as '近三个月利润相加'
FROM chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
mysql> SELECT *,SUM(sales) OVER win as '近三个月利润相加'
-> FROM chh_baozipu
-> WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
+----+--------------------+-------+---------+--------------------------+
| id | product | sales | month | 近三个月利润相加 |
+----+--------------------+-------+---------+--------------------------+
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 1000 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 2600 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 3400 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 3400 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 3400 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 3200 |
| 12 | 面馅儿包子 | 200 | 2021-06 | 200 |
| 11 | 面馅儿包子 | 100 | 2021-07 | 300 |
| 10 | 面馅儿包子 | 0 | 2021-08 | 300 |
| 9 | 面馅儿包子 | 300 | 2021-09 | 400 |
| 8 | 面馅儿包子 | 200 | 2021-10 | 500 |
| 7 | 面馅儿包子 | 700 | 2021-11 | 1200 |
+----+--------------------+-------+---------+--------------------------+
12 rows in set (0.00 sec)
②计算当前行与前n1行、后n2行的聚合窗口函数
下例中控制窗口大小为当前月前一个月到后一个月的利润总和,来看一下效果:
SELECT *,SUM(sales) OVER win as '前三个月利润相加'
FROM chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN n1 PRECEDING AND n2 FOLLOWING);
mysql> SELECT *,SUM(sales) OVER win as '前一个月到下一个月利润相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
+----+--------------------+-------+---------+--------------------------+
| id | product | sales | month |前一个月到下一个月利润相加|
+----+--------------------+-------+---------+--------------------------+
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 2600 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 3400 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 3400 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 3400 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 3200 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 2200 |
| 12 | 面馅儿包子 | 200 | 2021-06 | 300 |
| 11 | 面馅儿包子 | 100 | 2021-07 | 300 |
| 10 | 面馅儿包子 | 0 | 2021-08 | 400 |
| 9 | 面馅儿包子 | 300 | 2021-09 | 500 |
| 8 | 面馅儿包子 | 200 | 2021-10 | 1200 |
| 7 | 面馅儿包子 | 700 | 2021-11 | 900 |
+----+--------------------+-------+---------+--------------------------+
12 rows in set (0.00 sec)
4、头尾函数:FIRST_VALUE(expr)、LAST_VALUE(expr)
头尾函数应用于:返回第一个或最后一个expr的值;
应用场景:截止到当前,按照日期排序查询当前最大月收入
和当前最小月收入
。
SELECT *,
FIRST_VALUE(sales) over win as '当前最大月收入',
LAST_VALUE(sales) over win as '当前最小月收入'
from chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month`);
mysql> SELECT *,FIRST_VALUE(sales) over win as '当前最大月收入',LAST_VALUE(sales) over win as '当前最小月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
+----+--------------------+-------+---------+-----------------------+-----------------------+
| id | product | sales | month | 当前最大月收入 | 当前最小月收入 |
+----+--------------------+-------+---------+-----------------------+-----------------------+
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | 1000 | 1000 |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 1000 | 1600 |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 1000 | 800 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 1000 | 1000 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 1000 | 1600 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 1000 | 600 |
| 12 | 面馅儿包子 | 200 | 2021-06 | 200 | 200 |
| 11 | 面馅儿包子 | 100 | 2021-07 | 200 | 100 |
| 10 | 面馅儿包子 | 0 | 2021-08 | 200 | 0 |
| 9 | 面馅儿包子 | 300 | 2021-09 | 200 | 300 |
| 8 | 面馅儿包子 | 200 | 2021-10 | 200 | 200 |
| 7 | 面馅儿包子 | 700 | 2021-11 | 200 | 700 |
+----+--------------------+-------+---------+-----------------------+-----------------------+
12 rows in set (0.00 sec)
5、其他函数:nth_value() / nfile()
nfile()不常用,不再赘述;这里我们只提一下NTH_VALUE(expr,n)
函数;
NTH_VALUE用途:返回窗口中第n个expr的值。
应用场景:截止到当前,显示陈哈哈包子铺月利润榜中排名第2和第3的成绩的利润。
SELECT *,
nth_value(sales,2) over win as '当前排名第二的月收入',
nth_value(sales,3) over win as '当前排名第三的月收入'
from chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month`);
mysql> SELECT *,nth_value(sales,2) over win as '当前排名第二的月收入',nth_value(sales,3) over win as '当前排名第三的月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
| id | product | sales | month | 当前排名第二的月收入 | 当前排名第三的月收入 |
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
| 6 | 猪肉大葱包子 | 1000 | 2021-06 | NULL | NULL |
| 5 | 猪肉大葱包子 | 1600 | 2021-07 | 1600 | NULL |
| 4 | 猪肉大葱包子 | 800 | 2021-08 | 1600 | 800 |
| 3 | 猪肉大葱包子 | 1000 | 2021-09 | 1600 | 800 |
| 2 | 猪肉大葱包子 | 1600 | 2021-10 | 1600 | 800 |
| 1 | 猪肉大葱包子 | 600 | 2021-11 | 1600 | 800 |
| 12 | 面馅儿包子 | 200 | 2021-06 | NULL | NULL |
| 11 | 面馅儿包子 | 100 | 2021-07 | 100 | NULL |
| 10 | 面馅儿包子 | 0 | 2021-08 | 100 | 0 |
| 9 | 面馅儿包子 | 300 | 2021-09 | 100 | 0 |
| 8 | 面馅儿包子 | 200 | 2021-10 | 100 | 0 |
| 7 | 面馅儿包子 | 700 | 2021-11 | 100 | 0 |
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
12 rows in set (0.00 sec)
本章小结
窗口函数就说到这里,窗口函数是我接触MySQL8以后发现的新东西,突然感觉MySQL开发团队还是很灵性的,每个版本都会新增一些玩儿法,当然也很实用,希望MySQL9.0会给我们带来更多的惊喜。
来源:https://blog.csdn.net/qq_39390545/article/details/121279735
猜你喜欢
- 那什么时候会产生指令重排现象呢?两个阶段:1、编译期;2、运行期。编译期指令重排解释型语言是在运行期间执行编译+运行动作,所以运行效率较编译
- 摘要Beautiful Soup 是一个可以从 HTML 或 XML 格式文件中提取数据的 Python 库,他可以将HTML 或 XML
- 安装 setuptools 工具任务时间:1min ~ 5min安装yum install python-setuptools -y因为之后
- 问题描述 项目使用的vue2.0开发,项目中需要一个富文本编辑器,楼主经过一番心理挣扎选择了vue-quill-editor。具体如何引用
- 前言如果我们和面试官聊到事务的问题,怎么回答呢?先说下事务是什么,因为我们业务是比较复杂的,不可能一个sql就能解决的,涉及多个sql就组成
- 1、汇总统计介绍几个聚集函数有多少名学生SELECT COUNT(*) FROM student;SELECT COUNT(1) FROM
- 前言打开手机游戏列表发现了一款经典的扫雷游戏,在玩的过程中发现游戏逻辑应该不难,想着是不是能自己写写这个游戏,后来用了1天实现了整体游戏开发
- 详细介绍Scrapy shell的使用Scrapy shell是Scrapy框架提供的一个非常有用的工具,可以帮助开发者快速地测试和调试Sc
- 1、说明GIL规定一个Python解释程序只能同时由一个线程控制。在CPU限制类型和多线程代码中,GIL是一个性能瓶颈。GIL使Python
- 一.windows系统的解决方法1.首先以系统管理员身份登陆系统。2.停止MySQL的服务。3.进入命令窗口,然后进入MySQL的安装目录,
- 本文研究的主要是python实现Adapter模式的相关内容,具体实现代码如下。Adapter模式有两种实现方式一种是类方式。#理解 #就是
- 一、记事本源码#python简易记事本from tkinter import *from tkinter import messagebox
- 如下所示:import numpy as npimport pandas as pdfrom pandas import Series,Da
- 前言:目前我们使用的绝大多数计算机程序,无论是办公软件,浏览器,甚至游戏、视频都是通过菜单界面系统配置的,它几乎成了我们使用机器的默认方式。
- 为了给导航栏添加响应式特性,您要折叠的内容必须包裹在带有 classes .collapse、.navbar-collapse 的 <
- 本文实例讲述了php+jQuery实现的 * 导航栏下拉菜单显示效果。分享给大家供大家参考,具体如下:首先看看效果图:1.数据配置文件 db.
- 在熟悉了Python的基本安装与环境配置之后,我们来看看Python的基本运算操作。1. 基本运算>>>6 # 这里的‘#
- 有些时候我们需要得到刚刚插入数据库中的条目id,用于相关系统的更改,在以前我是使用select+top 1+desc 这种笨方法实现的,在没
- 前言:这个先来创建一个模块,名称为christmastree,在该模块中,首先定义一个全局变量,然后创建一个名称为fun_christmas
- 网页设计中,内容组织恐怕是最至关重要、最影响设计品质的方面了。如何将信息组织到好的布局中,是一个网站的基础,并且应该在考虑外观之前就决定好。