【第34天】SQL进阶-SQL高级技巧-Window Funtion（SQL 小虚竹)

回城传送–》《100天精通MYSQL从入门到就业》

文章目录

  • 零、前言
  • 一、练习题目
  • 二、SQL思路
      • 初始化数据
      • 什么是Window Funtion
      • 窗口函数的分类
      • 语法结构
        • 第一种写法：
        • 第二种写法：
      • 实战体验
        • 序号函数：row_number()
        • 序号函数：rank()
        • 序号函数：dense_rank()
        • 分布函数:percent_rank()
        • 分布函数:cume_dist()
        • 前后函数：LAG(expr, n)
        • 前后函数：LEAD(expr, n)
        • 首尾函数：first_value(expr)
        • 首尾函数：last_value(expr)
        • 其他函数：nth_value(expr, n)
        • 其他函数：ntile(n)
    • 三、总结
    • 四、参考

零、前言

今天是学习 SQL 打卡的第 34 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

​ 我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL高级技巧-Window Funtion

一、练习题目

题目链接难度
工资最高的人★★★☆☆

二、SQL思路

在这里插入图片描述
在这里插入图片描述

初始化数据

创建表

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');

什么是Window Funtion

窗口函数特别适用于需要对分组统计结果中的每一条记录进行计算的场景。

使用窗口函数，你可以为结果集中的每一行返回特定的计算结果，而不是像普通的聚合函数那样，只针对每个分组返回一条记录。通过窗口函数，你可以同时查看原始数据以及与之相关的计算值，为数据分析提供了更丰富的信息。

窗口函数的分类

MySQL8.0版本起开始支持窗口函数。
窗口函数在查询中发挥着类似分组数据的作用，但不同的是：

  • 分组操作是将每个分组的结果聚合为单一的记录。
  • 窗口函数将分组的结果直接融入到查询结果集中的每一条数据记录中

窗口函数分为静态函数和动态函数

  • 静态窗口函数的特点在于其窗口大小是固定的，无论处理的是哪条记录，窗口的边界都保持不变。
  • 动态窗口函数的窗口大小则会根据记录的不同而发生变化。这种变化可能是基于数据的某个字段值，或者是基于记录之间的相对位置。

语法结构

第一种写法：

函数 OVER （PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC）[window_frame] as 自定义别名

字段解释：

  • 函数： 有多个函数可选择

    • 序号函数：row_number()顺序编号；rank()并列编号，跳过重复的；dense_rank() 并列编号，不会跳过重复的；

    • 分布函数：percent_rank()等级比百分比；cume_dist()累积分布值；

    • 前后函数：LAG(expr, n) 返回当前行的前n行(本组内)的expr值（expr是指定字段）；LEAD(expr, n)返回当前行的后n行(本组内)的expr值（expr是指定字段）

    • 首尾函数：first_value(expr) 返回指定字段，截止到当前行，第一行的值；last_value(expr)返回指定字段，截止到当前行，最后一行的值；

    • 其他函数：nth_value(expr, n)返回对应字段，按排序下，第n行的值；ntile(n)将结果集划分为大致相等的n个部分，并为每行数据返回一个“桶”编号，在数据分桶、排名分组等场景有用。

  • OVER：这是定义窗口的关键字，它后面跟着圆括号，用于包含窗口的设置。

  • PARTITION BY：这是可选的子句，用于将结果集分成不同的分区（组）。partition_expression是一个表达式，根据它的值来划分不同的分区。窗口函数将在每个分区内独立计算。

  • ORDER BY：按指定字段排序

  • window_frame：这是可选的子句，用于指定在每个分区中用于窗口函数的行范围。它决定了哪些行包含在计算中。如果未指定window_frame，则窗口函数默认使用所有分区中的所有行。

第二种写法：
SELECT
	...,
	函数 OVER 自定义的窗口名 AS 自定义的列名,
	函数 OVER 自定义的窗口名 AS 自定义的列名2
FROM
	GOODS window 自定义的窗口名 as （PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC）;

如果窗口在sql中多次使用，则可以提取出来，用自定义窗口名的方式复用，简化sql。

实战体验

序号函数：row_number()

对数据进行顺序排号
例子：查询goods表中每个商品类别对价格降序排列的商品信息。

SELECT 
	*,
	ROW_NUMBER() OVER (PARTITION BY CATEGORY
ORDER BY
	PRICE DESC) AS ROW_NUM
FROM
	GOODS;

我们可以看到，已经对CATEGORY字段进行区分，并对PRICE进行了降序排列，对每个CATEGORY下的商品进行顺序排序。
在这里插入图片描述

序号函数：rank()

并列编号，跳过重复的：如, 1,1,3,4 而不会是 1,2,3,4
例子：查询goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT 
	*,
	RANK() OVER (PARTITION BY CATEGORY
ORDER BY
	PRICE DESC) AS TOP4PRICE
FROM 
	GOODS
WHERE 
	CATEGORY = '女装/女士精品'
LIMIT 4;

如图所示，TOP4PRICE字段的排序是1，2，2，4
在这里插入图片描述

序号函数：dense_rank()

并列编号，不会跳过重复的；如, 1,1,2,4
例子：查询goods表中每个商品类别对价格降序排列的商品信息。

SELECT
	*,
	DENSE_RANK() OVER(PARTITION BY CATEGORY
ORDER BY
	PRICE DESC) AS PRICE_RANK
FROM 
	GOODS

如图所示，可以看到PRICE_RANK 的排序是1,2,2,3,4,5
在这里插入图片描述

分布函数:percent_rank()

等级比百分比
例子：查询goods 数据表中类别为“女装/女士精品”,价格的百分数排名。

	
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;

如图可以看出，按category_id分区数据，并按PRICE降序排列。PERCENT_RANK()函数会计算每个商品在其类别中的百分比排名。
在这里插入图片描述

分布函数:cume_dist()

累积分布值
例子：查询goods 数据表小于或等于当前价格的比例


SELECT
	CUME_DIST() OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE ASC) AS CD,
	ID,
	CATEGORY,
	NAME,
	PRICE
FROM
	GOODS;

cume_dist()=分区中值小于或等于当前行值的行数（包括当前行自身）/分区中的总行数

百褶裙的价格是29.9,没有比它更低的价格，女装/女士精品这个分区有6个，1/6=0.16666666666666667
在这里插入图片描述

前后函数：LAG(expr, n)

返回当前行的前n行(本组内)的expr值（expr是指定字段）
例子：查询goods 数据表同一个分类下，价格降序，前一个商品价格。

SELECT
	*,
	LAG(PRICE, 1) OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE DESC) AS PRE_PRICE
FROM
	GOODS;

在这里插入图片描述

前后函数：LEAD(expr, n)

返回当前行的后n行(本组内)的expr值（expr是指定字段）
例子：查询goods 数据表同一个分类下，价格降序，后一个商品价格。

SELECT
	*,
	LEAD(PRICE, 1) OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE DESC) AS PRE_PRICE
FROM
	GOODS;

在这里插入图片描述

首尾函数：first_value(expr)

返回指定字段，截止到当前行，第一行的值；
例子：取每个商品对应分类下，价格降序下，返回指定字段的第一行的值。

SELECT
   *,
   FIRST_VALUE(PRICE) OVER (PARTITION BY CATEGORY_ID
ORDER BY
   PRICE DESC) AS FV
FROM
   GOODS;

在这里插入图片描述

首尾函数：last_value(expr)

返回指定字段，截止到当前行，最后一行的值；

例子：取每个商品对应分类下，价格降序下，返回指定字段的最后一行的值。

SELECT
   *,
   LAST_VALUE(PRICE) OVER (PARTITION BY CATEGORY_ID
ORDER BY
   PRICE desc) AS LV
FROM
   GOODS;

在这里插入图片描述

其他函数：nth_value(expr, n)

返回对应字段，按排序下，第n行的值
注意：这个是截止当前行的排序
在这里插入图片描述

例子：查询每个商品，在对应分类下，价格升序排行在第2，第3行的数据

SELECT
	*,
	NTH_VALUE(PRICE,
	2) OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE ASC) AS SECOND_PRICE,
	NTH_VALUE(PRICE,
	3) OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE ASC) AS THIRD_PRICE
FROM
	GOODS;

在这里插入图片描述
第二种写法：

SELECT
	*,
	NTH_VALUE(PRICE,
	2) OVER w AS SECOND_PRICE,
	NTH_VALUE(PRICE,
	3) OVER w AS THIRD_PRICE
FROM
	GOODS window w as (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE ASC);

在这里插入图片描述

其他函数：ntile(n)

将结果集划分为大致相等的n个部分，并为每行数据返回一个“桶”编号，在数据分桶、排名分组等场景有用。
例子：将商品价格按商品分类，分为三个等级；

SELECT
	NTILE(3) OVER (PARTITION BY CATEGORY_ID
ORDER BY
	PRICE ASC) AS N,
	ID,
	CATEGORY,
	NAME,
	PRICE
FROM
	GOODS;

在这里插入图片描述

三、总结

本文分享了什么是Window Function，并介绍了Window Function的分类，语法结构，并以实战例子介绍每个函数如何使用Window Function。

所以，嗯，这题的答案选。。评论区大声告诉虚竹哥。

四、参考

MySQL进阶技能树–》SQL高级技巧–》Window Function

我是虚竹哥，我们明天见~

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【树莓派Linux内核开发】入门实操篇（虚拟机Ubuntu环境搭建+内核源码获取与配置+内核交叉编译+内核镜像挂载）

【树莓派Linux内核开发】入门实操篇（虚拟机Ubuntu环境搭建+内核源码获取与配置+内核交叉编译+内核镜像挂载）

【树莓派Linux内核开发】入门实操篇（虚拟机Ubuntu环境搭建内核源码获取与配置内核交叉编译内核镜像挂载） 文章目录 【树莓派Linux内核开发】入门实操篇（虚拟机Ubuntu环境搭建内核源码获取与配置内核交叉编译内核镜像挂载）一、搭建…
阅读更多...
什么是0-day漏洞，怎么防护0-day漏洞攻击

什么是0-day漏洞，怎么防护0-day漏洞攻击

随着信息技术的快速发展，网络安全问题日益凸显，其中0day漏洞攻击作为一种高级威胁手段，给企业和个人用户带来了极大的风险。下面德迅云安全就对0day漏洞攻击进行简单讲解下，并分享相应的一些安全措施，以期提高网络安全…
阅读更多...
网络空间地图测绘理论体系白皮书（2023年）02网络空间测绘研究背景(想法比较好，着重看)

网络空间地图测绘理论体系白皮书（2023年）02网络空间测绘研究背景(想法比较好，着重看)

01前言 02 网络空间测绘研究背景 2.1 网络空间的起源 2.2 传统测绘理论 2.3 网络空间测绘相关工作 03 测绘体系框架概念定义 3.1 网络空间 3.2 网络空间地图测绘 3.3 体系框架总体思路 04 测绘体系框架应用实践 4.1 网络空间地形图 4.2 网络空间地志图 4.3 网络空间战略图 05 总…
阅读更多...
Python 全栈安全（一）

Python 全栈安全（一）

原文：annas-archive.org/md5/712ab41a4ed6036d0e8214d788514d6b 译者：飞龙 协议：CC BY-NC-SA 4.0 前言 序言 多年前，我在亚马逊搜索了一本基于 Python 的应用程序安全书。我以为会有多本书可供选择。已经有了很多其他主题的 Pyt…
阅读更多...
【ZYNQ】Zynq 芯片介绍

【ZYNQ】Zynq 芯片介绍

Zynq 是 Xilinx 公司提出的全可编程 SoC 架构，集成了单核或多核 ARM 处理器与 Xilinx 16nm 或 28nm 可编程逻辑，包括 Zynq 7000 Soc，Zynq UltraScale MPSoC 和 Zync UltraScale RFSoC 等系列。本文主要介绍 Xilinx Zynq 7000 系列芯片架构、功…
阅读更多...
Hadoop1X，Hadoop2X和hadoop3X有很大的区别么？

Hadoop1X，Hadoop2X和hadoop3X有很大的区别么？

Hadoop的演进从Hadoop 1到Hadoop 3主要是为了提供更高的效率、更好的资源管理、更高的可靠性以及对更多数据处理方式的支持。下面是Hadoop 1, Hadoop 2, 和 Hadoop 3之间的主要区别和演进的原因： Hadoop 1 特点： 主要包括两大核心组件：HDFS&a…
阅读更多...
simple-jwt快速入门(包含自定制)

simple-jwt快速入门(包含自定制)

simple-jwt快速入门(包含自定制) 目录 simple-jwt快速入门(包含自定制)安装路由层视图层全局配置前端传入参数配置文件定制登录返回格式定制payload格式自定制签发-认证 安装 pip install djangorestframework-simplejwt路由层 from rest_framework_simplejwt.views import T…
阅读更多...
【 AIGC 研究最新方向（上）】面向平面、视觉、时尚设计的高可用 AIGC 研究方向总结

【 AIGC 研究最新方向（上）】面向平面、视觉、时尚设计的高可用 AIGC 研究方向总结

目前面向平面、视觉、时尚等设计领域的高可用 AIGC 方向有以下 4 种： 透明图层生成可控生成图像定制化SVG 生成 本篇（上篇）介绍 1、2，而下篇将介绍 3、4。 透明图层生成 LayerDiffuse 代表性论文：Transparent Imag…
阅读更多...
Qt基础之四十六：Qt界面中嵌入第三方程序的一点心得

Qt基础之四十六：Qt界面中嵌入第三方程序的一点心得

本文主要讲解QWidget和QWindow的区别，以及如何在QWidget中嵌入第三方程序，并完美解决在QWidget中嵌入某些程序（比如Qt程序）时出现的白边问题。 下面是嵌入QQ音乐的样子，这首歌还不错。 先用spy++查看QQ音乐的窗口信息，如果安装了Visual Studio，工具菜单里自带spy++ 然后…
阅读更多...
Spring Boot | Spring Boot 默认 “缓存管理“ 、Spring Boot “缓存注解“ 介绍

Spring Boot | Spring Boot 默认 “缓存管理“ 、Spring Boot “缓存注解“ 介绍

目录: 一、Spring Boot 默认 "缓存" 管理 :1.1 基础环境搭建① 准备数据② 创建项目③ 编写 "数据库表" 对应的 "实体类"④ 编写 "操作数据库" 的 Repository接口文件⑤ 编写 "业务操作列" Service文件⑥ 编写 "applic…
阅读更多...
Redis入门到通关之数据结构解析-QuickList

Redis入门到通关之数据结构解析-QuickList

文章目录 ☃️前提概要☃️ 配置项相关☃️简要源码☃️总结 Redis中的 QuickList 是一种特殊的数据结构，用于存储列表类型的数据。它的设计目的是在内存中高效地存储和操作大量的列表元素，尤其是当列表长度很大时。 QuickList的内部结构是一个由多个节…
阅读更多...
政安晨：【Keras机器学习示例演绎】（八）—— 利用 PointNet 进行点云分割

政安晨：【Keras机器学习示例演绎】（八）—— 利用 PointNet 进行点云分割

目录 简介 导入 下载数据集 加载数据集 构建数据集 预处理 创建 TensorFlow 数据集 PointNet 模型 排列不变性 变换不变性 点之间的相互作用 实例化模型 训练 直观了解培训情况 推论 最后说明 政安晨的个人主页：政安晨 欢迎 👍点赞✍评论…
阅读更多...
【PCL】教程 implicit_shape_model.cpp 3D点云数据的对象识别 利用隐式形状模型进行训练和识别...

【PCL】教程 implicit_shape_model.cpp 3D点云数据的对象识别 利用隐式形状模型进行训练和识别...

ism_test_cat.pcd 参数：ism_train_cat.pcd 0 ism_train_horse.pcd 1 ism_train_lioness.pcd 2 ism_train_michael.pcd 3 ism_train_wolf.pcd 4 ism_test_cat.pcd 0 这里红点表示对应感兴趣类别的对象预测中心 ./ism_t…
阅读更多...
字节FE：JavaScript学习路线图

字节FE：JavaScript学习路线图

JavaScript简介 JavaScript是一种高级的、解释执行的编程语言。它是互联网的三大核心技术之一，与HTML和CSS一同工作，用于创建交互式的网页。JavaScript被所有现代网页浏览器支持而不需要任何插件。它可以增强用户界面和网页的交互性，可以进行…
阅读更多...
【讲解下Spring Boot单元测试】

【讲解下Spring Boot单元测试】

🌈个人主页: 程序员不想敲代码啊 🏆CSDN优质创作者，CSDN实力新星，CSDN博客专家 👍点赞⭐评论⭐收藏 🤝希望本文对您有所裨益，如有不足之处，欢迎在评论区提出指正，让我们共…
阅读更多...
FineBi中创建自定义的图表

FineBi中创建自定义的图表

FineBi中增加自己的自定义图表组件，比如： 的相关笔记： 1 获取有哪些BI自定义图表组件：http://localhost:8080/webroot/decision/v5/plugin/custom/component/list?_=1713667435473[{"name": "图表DEMO_EK","chartType": "amap_demo&q…
阅读更多...
GO环境及入门案例

GO环境及入门案例

文章目录 简介一、win GO开发环境安装二、Linux go运行环境二、GO代码入门2.1 导包案例2.2 赋值2.3 变量、函数2.4 三方库使用 简介 go不是面向对象语言， 其指针、结构体等比较像C，知名的go 开源项目有docker k8s prometheus node-exporter等 一、win …
阅读更多...
如何在3dMax中快速打包mzp 文件？

如何在3dMax中快速打包mzp 文件？

如何在3dMax中创建mzp 文件？ 我喜欢将我的Maxscript脚本发布为mzp文件。这是一个为3dMax构建的自解压zip文件。在mzp文件中，您可以捆绑Maxscript脚本文件、图片、预设或其他文件，并链接安装时执行的特殊操作。 在3dMax中使用大型脚本时&…
阅读更多...
耐高温300度锅炉轴承，江苏鲁岳轴承制造的行业标杆

耐高温300度锅炉轴承，江苏鲁岳轴承制造的行业标杆

自润滑轴承-产品类型-耐高温轴承-不锈钢轴承-江苏鲁岳轴承制造有限公司。锅炉轴承，耐高温至200度-800度。 江苏鲁岳轴承制造有限公司，一家专注于锅炉轴承和耐高温轴承的研发与生产的企业，致力于为客户提供高质量、高性能的轴承解决方案。其中…
阅读更多...
LeetCode题练习与总结：矩阵置零--73

LeetCode题练习与总结：矩阵置零--73

一、题目描述 给定一个 m x n 的矩阵，如果一个元素为 0 ，则将其所在行和列的所有元素都设为 0 。请使用 原地 算法。 示例 1： 输入：matrix [[1,1,1],[1,0,1],[1,1,1]] 输出：[[1,0,1],[0,0,0],[1,0,1]]示例 2&#xf…
阅读更多...
最新文章

Copyright @ 2022~2023