Mysql基础篇（11）—— MySQL8.0新特性之窗口函数

news/2023/5/28 6:57:47

举例1

假设我现在有这样一个数据表，它显示了某购物网站在每个城市每个区的销售额：

CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);
INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00)

需求：需要计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。

在mysql8之前，我们使用分组（聚合）函数来实现：

  • 第一步，计算总销售金额，存入临时表a
CREATE TEMPORARY TABLE a -- 创建临时表
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales;
  • 第二步，计算每个城市的销售总额并存入临时表b
CREATE TEMPORARY TABLE b -- 创建临时表
SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计
FROM sales
GROUP BY city
  • 第三步，计算所有结果
SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率, a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率 
FROM sales s
JOIN b ON (s.city=b.city) -- 连接市统计结果临时表
JOIN a -- 连接总计金额临时表
ORDER BY s.city,s.county;

这个实现虽然逻辑思路很清晰，但是步骤太繁琐了。

同样的查询，如果使用窗口函数，就简单许多，我们可以用下面代码来实现：

SELECT city AS 城市,county AS 区,sales_value AS 区销售额,
SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额
sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额
sales_value/SUM(sales_value) OVER() AS 总比率
FROM sales
ORDER BY city,county;

个人对窗口函数的理解就是，OVER后面的就是可以临时地对数据进行分组,且支持对分组里面每条数据进行处理,这里临时的分组就是窗口

窗口函数可以分为静态窗口函数和动态窗口函数。

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

MySQL官方网站窗口函数的网址

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

除了大部分的聚合函数，其他窗口函数还有：

函数函数说明
ROW_NUMBER()顺序排序，序号不可能会出现重复
RANK()并列排序，会跳过重复的序号，比如序号为1、1、3
DENSE_RANK()并列排序，不会跳过重复的序号，比如序号为1、1、2
PERCENT_RANK()等级值百分比
CUME_DIST()累计分布值
LAG(expr, n)返回当前行的前n行的expr的值
LEAD(expr, n)返回当前行的后n行的expr的值
FIRST_VALUE(expr)返回第一个expr值
LAST_VALUE(expr)返回最后一个expr的值
NTH_VALUE(expr, n)返回第n个expr的值
NTILE(n)将分区中的有序数据分为n个桶，记录桶编号

语法结构

函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

或者是

函数 OVER 窗口名	...WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
  • OVER关键字指定函数窗口的范围
  • 窗口名：为窗口设置一个别名，用来标识窗口。
  • PARTITION BY 子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
  • ORDER BY 子句：执行窗口函数按照哪些字段进行排序。执行排序操作时窗口函数按照排序后的数据记录的顺序进行编号。

举例2

创建表：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
)

添加数据

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
ROW_NUMBER()

顺序排序，序号不可能会出现重复。

比如，展示每个商品在对应分类下的排序序号。

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stock
FROM goods;

结果：
在这里插入图片描述

RANK()

并列排序，会跳过重复的序号。

比如，使用RANK()函数获取goods表中各类别的价格从高到低排序的各商品信息。

SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock
FROM goods;

结果：
在这里插入图片描述

DENSE_RANK()

并列排序，不会跳过重复的序号。

举例，同上。

SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock
FROM goods;

结果：
在这里插入图片描述

PERCENT_RANK()

等级值百分比函数，按照如下方式计算。

(rank - 1) / (rows - 1)

其中，rank就是使用RANK()函数产生的序号，rows为当前窗口的总记录数

比如，计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值

写法一

SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;

写法二

SELECT RANK() OVER w AS r, 
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
HERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

结果：
在这里插入图片描述

CUME_DIST()

主要用于查询小于或等于某个值的比例。

比如，查询goods数据表中小于或等于当前价格的比例

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;

结果：
在这里插入图片描述

LAG(expr,n)

返回当前行的前n行的expr的值。

比如，查询goods数据表中前一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (
SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;

结果：
在这里插入图片描述

LEAD(expr, n)

返回当前行的后n行的expr的值。和LAG用法一样。

FIRST_VALUE(expr)

返回第一个expr的值。

比如，返回每个种类价格最低的商品价格

SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

结果：
在这里插入图片描述

LAST_VALUE(expr)

返回最后一个expr的值，用法同FIRST_VALUE。

NTH_VALUE(expr, n)

返回第n个expr的值，n为1的时候和FIRST_VALUE()效果一样。

NTILE(n)

将分区中的有序数据分为n个桶，记录桶编号。

比如，将goods表中的商品按价格分为3组。

SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods 
WINDOW w AS (PARTITION BY category_id ORDER BY price);

结果：
在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.exyb.cn/news/show-4560159.html

如若内容造成侵权/违法违规/事实不符，请联系郑州代理记账网进行投诉反馈，一经查实，立即删除！

相关文章

OPLS-DA

OPLS-DA

转自迈维代谢微信公众号 什么是opls-da opls-da&vip
阅读更多...
C语言中 e3是什么意思,fib在c语言中是什么意思？

C语言中 e3是什么意思,fib在c语言中是什么意思？

fib在c语言中为斐波那契数列，又称黄金分割数列、因数学家列昂纳多斐波那契(Leonardoda Fibonacci)以兔子繁殖为例子而引入，故又称为“兔子数列”。从第二项开始，每个偶数项的平方都比前后两项之积多1，每个奇数项的平方都比前后两项…
阅读更多...
git cherry-pick 教程

git cherry-pick 教程

对于多分支的代码库，将代码从一个分支转移到另一个分支是常见需求。 这时分两种情况。一种情况是，你需要另一个分支的所有代码变动，那么就采用合并（git merge）。另一种情况是，你只需要部分代码变动&#x…
阅读更多...
大数据分析01——成都二手房(平均价格)

大数据分析01——成都二手房(平均价格)

背景前段时间很多成都的朋友准备买房，遇到了各种问题。有的交了订金，房东发现还有机会涨价，宁愿交2w的违约金，也要再等一等房价涨起来；有的没有2年社保或户口买房；现在人才落户放宽后，有机会买房…
阅读更多...
用python爬取链家网成都房价信息（包括总价、均价、地址、描述等）

用python爬取链家网成都房价信息（包括总价、均价、地址、描述等）

用python爬取链家网成都房价信息（包括总价、均价、地址、描述等） 文章目录准备工作1、网页分析2、获取HTML信息3、获取数据4、保存文件到本地5、完整代码准备工作 链家网作为互联网房屋销售信息的大平台之一，拥有大量的二手房源信息&#xf…
阅读更多...
链家房屋数据分析实战

链家房屋数据分析实战

上周有某高校老师来我们公司进行培训，公司安排我上了两天课。最后一天是一个数据分析的小案例，这里记录分享一下，比较适合刚入门的小白练手。大概的逻辑是这样的：利用Scrapy爬取了链家的2900余条成都二手房的数据，然后…
阅读更多...
Python爬取链家成都二手房源信息，异步爬虫实战项目！

Python爬取链家成都二手房源信息，异步爬虫实战项目！

本文先熟悉并发与并行、阻塞与非阻塞、同步与异步、多线程、多线程、协程的基本概念。再实现asyncio aiohttp爬取链家成都二手房源信息的异步爬虫，爬取效率与多线程版进行简单测试和比较。 1. 基本概念 并发与并行 并发： 指在同一时刻只能有一条指令…
阅读更多...
链家在售房屋分析

链家在售房屋分析

利用Scrapy爬取了链家的2900余条成都二手房的数据，然后基于这些数据做了一些关于房屋价格、区域、户型、房屋数量等方面的分析。 导入基本的库 import re import pandas as pd import seaborn as sns import matplotlib.pyplot as plt数据具体： 数据集…
阅读更多...
成都双流区链家网租房python数据可视化

成都双流区链家网租房python数据可视化

0X00 数据来源：python爬虫获取链家二手房源信息 上一次我使用python爬取了链家上成都市双流区的一些租房信息，今天我们就来对爬取到的数据进行一些简单的数据可视化处理，学习学习python一些简单的数据处理。 0X01 数据展示 这些就是我们…
阅读更多...
Python爬虫实战之五：requests-re多页爬取链家成都地区租房市场信息

Python爬虫实战之五：requests-re多页爬取链家成都地区租房市场信息

本实战项目爬取了链家网成都地区租房的信息 目录 1.爬取目标 2.爬取连接 3.技术路线 4.代码及输出模块 单页爬取全代码 多页爬取核心代码 5.总结 全代码文件见： 1.爬取目标 链家网发布的房屋数据信息主要包括二手房、新房、租房、海外、商业办公等。 这次爬…
阅读更多...
python分析链家二手房信息----数据分析实战（一）

python分析链家二手房信息----数据分析实战（一）

链家二手房信息 # 导入需要的库：科学计算包numpy, pandas 可视化包matplotlib, seaborn 机器学习包 sklearn import numpy as np import pandas as pd import matplotlib as mpl import seaborn as snsimport matplotlib.pyplot as plt from IPython.display imp…
阅读更多...
Qt使用第三方库QXlsx将数据库的数据导出为Excel表格

Qt使用第三方库QXlsx将数据库的数据导出为Excel表格

一、参考和下载第三方库QXlsx 参考1 这篇博客对第三方库QXlsx介绍的比较详细。 1、概述 QXlsx是一个可以读写Excel文件的库。不依赖office以及wps组件，可以在Qt5支持的任何平台上使用。 2、使用方式 (1) QXlsx可以编译为静态库库使用（可以提升项目编…
阅读更多...
Linux（二）进程概念

Linux（二）进程概念

目录 一、冯诺依曼体系结构 二、操作系统 三、进程概念 1、程序与进程的区别： 2、cpu分时机制 3、pcb——进程控制块 4、进程是什么？ 四、进程状态 1、linux状态 2、僵尸态 pid_t fork(void)： fork创建进程之后，父子进…
阅读更多...
bochs安装配置

bochs安装配置

玄学bochs安装配置记录一波 系统：Ubuntu 16.04/64位bochs版本：bochs-2.6.9 安装步骤 第1步：bochs下载：下载地址第2步：解压下载的源代码 sudo tar zxvf bochs-2.6.9.tar.gz 第3步：进入bochs-2.6.8目录&…
阅读更多...
bochs在安卓上模拟kali linux系统

bochs在安卓上模拟kali linux系统

概述 本文将介绍如何在安卓手机上安装并运行Kali系统，我们可以使用Kali系统来破解Wifi密码，当然它的功能远不止于此，它还有非常强大而且实用的功能，同时Kali系统对硬件配置要求并不高，如果你身边有很久不用的旧手机那…
阅读更多...
基于OpenCv的人脸识别,翻车了居然识别错误。

基于OpenCv的人脸识别,翻车了居然识别错误。

前言 我们身边的人脸识别有车站检票，监控人脸，无人超市，支付宝人脸支付，上班打卡，人脸解锁手机。 人脸检测是人脸识别系统组成的关键部分之一，其目的是检测出任意给定图片中的包含的一个或多个人脸&#xf…
阅读更多...
聚焦儿童羽绒服产业，看用友YonSuite打造领先实践的数智创新小灯塔

聚焦儿童羽绒服产业，看用友YonSuite打造领先实践的数智创新小灯塔

有一种冷“是妈妈觉得你冷”。每每想起小时候，为了应对寒冷的冬季，都会“全副武装”，裹得厚厚的，里三层外三层。 放到如今，有了羽绒服的萌娃们，已不再像我们当年一样穿得厚厚的了。现在的年轻爸妈喜欢装扮…
阅读更多...
AtCoder Beginner Contest 165 (C(暴力),D（数学推导）E（思维）,F(树上LIS))

AtCoder Beginner Contest 165 (C(暴力),D（数学推导）E（思维）,F(树上LIS))

题目链接 C - Many Requirements 如何计算这种序列有多少个呢？ 我们可以将此转化为在长度为 n的序列上划分成 m 个段，由于有些数字可能没被选上 我们补上m个数，然后每种数至少选一次。根据隔板法原理，数列的个数为 C(nm−1,m−1) …
阅读更多...
excel表格分割线一分为二_PDF转Excel的Python代码

excel表格分割线一分为二_PDF转Excel的Python代码

本代码由广州75中麻玉国老师分享在NOI教练群内，自己亲测了一下，感觉蛮好玩，所以特意收藏下来，具体代码如下：import pdfplumberfrom openpyxl import Workbookwb Workbook() # 创建文件对象ws wb.active # 获取第一…
阅读更多...
layui table 每列加标签_【前端】layui表格中根据条件给对应的列加背景色

layui table 每列加标签_【前端】layui表格中根据条件给对应的列加背景色

【前端】layui表格中根据条件给对应的列加背景色【前端】layui表格中根据条件给对应的列加背景色1.效果(根据条件动态给表格加背景色)2.代码:在我自己的项目中，由于条件比较多，提取出了一个方法 getColor(that,item,index); 代码的示例中写出了其中一条d…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023