当前位置: 首页 > news >正文

MySql8.x---开窗函数

1、定义 

语法结构: ** 开窗函数|聚合函数 over([分组函数] [排序函数] [自定义窗口]) **

分组函数:partition by ...,根据指定的字段对表分组,分组字段可以有多个。省略时表示整个表为一组。

排序函数:order by ...,排序字段也可以有多个,当排序字段为多个时表示先按照第一个字段排序,当第一个字段相等确定不了顺序时再按照第二个字段排序,以此类推…

自定义窗口:mysql中的窗口类型有两种:rows和range。rows是以物理行距离为基准通过计算与当前行的物理距离计算窗口大小,range是以当前行的值为基准通过计算与当前行值的差值计算窗口大小。

窗口大小可通过between 上界 and 下界来指定,其中,窗口的上下界分别有下面几种取值:

  • unbounded preceding:包含当前行及当前行之前的所有记录。
  • n preceding:包含当前行及当前行之前的n-1行,实际窗口大小n。
  • current row:仅包含当前行。
  • unbounded following:包含当前行及当前行之后的所有记录。
  • n following:包含当前行及当前行之后的n-1行,实际窗口大小n。

当窗口下界为current row时,可以不使用between and,也就是下面几种情况可简写:

  1. between unbounded preceding and current row --> unbounded preceding
  2. between n preceding and current row --> n preceding
  3. between current row and current row --> current row

排序

  • row_number() over() 从小到大依次排序 如:1,2,3,4,5
  • rank() over() 相同数据并列保存,下一个值跳值,如:1,2,2,4
  • dense_rank() over() 相同数据并列保存,不存着断值,如:1,2,2,3,3,3,4

位移

  • lag(字段,往下位移行数,往下没有行时默认值) over()
  • lead(字段,往上位移行数,往上没有行时默认值) over()

求和

  • sum(字段) over()

指定顺序的字段值:

  • first_value(col):取窗口中字段col的第一个值。
  • last_value(col):取窗口中字段col的最后一个值。
  • nth_value(col, n):取窗口中第n顺序的值。

2、语法

  • 方式一:按照列所有行进行分组

over(partition by 列)

  • 方式二:按照列排序

over(order by 列)

  • 方式三:按照列1分组,按照列2排序

over(partition by 列1 order by 列2)

3、练习

源数据sql:

CREATE TABLE `student_scores` (
  `sid` INT PRIMARY KEY,
  `student_id` INT,
  `student_name` VARCHAR(50),
  `course_id` INT,
  `course_name` VARCHAR(50),
  `num` INT
);

INSERT INTO `student_scores` (`sid`, `student_id`, `student_name`, `course_id`, `course_name`, `num`) VALUES
(1, 1, 'Alice', 1, 'Math', 10),
(2, 1, 'Alice', 2, 'Physics', 9),
(5, 1, 'Alice', 4, 'Biology', 66),
(6, 2, 'Bob', 1, 'Math', 8),
(8, 2, 'Bob', 3, 'Chemistry', 68),
(9, 2, 'Bob', 4, 'Biology', 99),
(10, 3, 'Charlie', 1, 'Math', 77),
(11, 3, 'Charlie', 2, 'Physics', 66),
(12, 3, 'Charlie', 3, 'Chemistry', 87),
(13, 3, 'Charlie', 4, 'Biology', 99),
(14, 4, 'David', 1, 'Math', 79),
(15, 4, 'David', 2, 'Physics', 11),
(16, 4, 'David', 3, 'Chemistry', 67),
(17, 4, 'David', 4, 'Biology', 100),
(18, 5, 'Eve', 1, 'Math', 79),
(19, 5, 'Eve', 2, 'Physics', 11),
(20, 5, 'Eve', 3, 'Chemistry', 67),
(21, 5, 'Eve', 4, 'Biology', 100),
(22, 6, 'Frank', 1, 'Math', 9),
(23, 6, 'Frank', 2, 'Physics', 100),
(24, 6, 'Frank', 3, 'Chemistry', 67),
(25, 6, 'Frank', 4, 'Biology', 100);

# 每门学科的第一名,有并列的情况一起就一起展示
SELECT* 
FROM( SELECT *, DENSE_RANK() over ( PARTITION BY course_id ORDER BY source DESC ) AS num FROM student_scores ) xx 
WHERExx.num <=1;#每个人不同学科中的最高分
SELECT* 
FROM( SELECT *, DENSE_RANK() over ( PARTITION BY student_id ORDER BY source DESC ) AS num FROM student_scores ) xx 
WHERExx.num <=1;#每门学科的平均分
SELECTcourse_name,courseAvg	
FROM( SELECT *,avg(source) as courseAvg, ROW_NUMBER() over ( PARTITION BY course_id ORDER BY source DESC ) AS num FROM student_scores GROUP BY course_id ) xx ;#每人课程得分高于课程平均分的数量
SELECTstudent_name,SUM(CASE WHEN source > courseAvg THEN 1ELSE 0END) as 及格数
FROM( SELECT *,avg(source) over(PARTITION by course_id  ) as courseAvg FROM student_scores GROUP BY course_id,student_id ) xx GROUP BY xx.student_id

相关文章:

MySql8.x---开窗函数

1、定义 语法结构&#xff1a; ** 开窗函数|聚合函数 over([分组函数] [排序函数] [自定义窗口]) ** 分组函数&#xff1a;partition by ...&#xff0c;根据指定的字段对表分组&#xff0c;分组字段可以有多个。省略时表示整个表为一组。 排序函数&#xff1a;order by ...&…...

图文讲解HarmonyOS应用发布流程

HarmonyOS应用的开发和发布过程可以分为以下几个步骤&#xff1a;证书生成、应用开发、应用签名和发布。 1. 证书生成&#xff1a; 在开始开发HarmonyOS应用之前&#xff0c;首先需要生成一个开发者证书。开发者证书用于标识应用的开发者身份并确保应用的安全性。可以通过Har…...

【专题】2024飞行汽车技术全景报告合集PDF分享(附原数据表)

原文链接&#xff1a; https://tecdat.cn/?p37628 6月16日&#xff0c;小鹏汇天旅航者X2在北京大兴国际机场临空经济区完成首飞&#xff0c;这也是小鹏汇天的产品在京津冀地区进行的首次飞行。小鹏汇天方面还表示&#xff0c;公司准备量产&#xff0c;并计划今年四季度开启预…...

经典负载调制平衡放大器(LMBA)设计-从理论到ADS仿真

经典负载调制平衡放大器&#xff08;LMBA&#xff09;设计-从理论到ADS仿真 ADS工程下载&#xff1a;经典负载调制平衡放大器&#xff08;LMBA&#xff09;设计-从理论到ADS仿真-ADS工程 参考论文: An Efficient Broadband Reconfigurable Power Amplifier Using Active Load…...

Web开发:基础Web开发的支持

创建项目&#xff1a; 添加依赖: <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0" xmlns:xsi"http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation"http://mav…...

【LeetCode每日一题】——LCR 168.丑数

文章目录 一【题目类别】二【题目难度】三【题目编号】四【题目描述】五【题目注意】六【题目示例】七【题目提示】八【解题思路】九【时间频度】十【代码实现】十一【提交结果】 一【题目类别】 优先队列 二【题目难度】 中等 三【题目编号】 LCR 168.丑数 四【题目描述…...

Day7 | Java框架 | SpringMVC

Day7 | Java框架 | SpringMVC SpringMVC简介SpringMVC 概述入门案例入门案例工作流程分析Controller 加载控制与业务bean加载控制&#xff08;SpringMVC & Spring&#xff09;PostMan 请求与响应请求映射路径请求方式&#xff08;不同类型的请求参数&#xff09;&#xff1…...

【网络通信基础与实践第二讲】包括互联网概述、互联网发展的三个阶段、互联网的组成、计算机网络的体系结构

一、互联网概述 计算机网络是由若干节点&#xff08;node&#xff09;和连接这些节点的链路&#xff08;link&#xff09;组成。 网络之间还可以通过路由器互联起来&#xff0c;这就构成了一个覆盖范围更大的计算机网络。这样的网络称为互联网。 网络把许多计算机连接在一起…...

CentOS7下安装Ruby3.2.4的实施路径

一、CentOS版本 [userzt ~]$ cat /etc/os-release NAME"CentOS Linux" VERSION"7 (Core)" ID"centos" ID_LIKE"rhel fedora" VERSION_ID"7" PRETTY_NAME"CentOS Linux 7 (Core)" ANSI_COLOR"0;31" CPE…...

Redis 实现原理或机制

Redis 是一个高性能的、基于内存的键值对存储系统&#xff0c;广泛用于缓存、会话管理、排行榜和消息队列等场景。它的高效性得益于其独特的实现原理和机制&#xff0c;Redis支持丰富的数据结构和多种持久化、复制、集群和发布/订阅功能&#xff0c;提供了灵活性和高可用性。 …...

使用程序方式获取与处理MySQL表数据

8.1  执行多条语句获取 MySQL 表数据 8.1.1  MySQL 中的常量 8.1.2  MySQL 中的变量 1&#xff0e;用户变量 用户可以在表达式中使用自己定义的变量&#xff0c;这样的变量称为用户变量。 用户变量在使用前必须定义和初始化&#xff0c;如果使用没有初始化的变量&#x…...

计算机网络(五) —— 自定义协议简单网络程序

目录 一&#xff0c;关于“协议” 1.1 结构化数据 1.2 序列化和反序列化 二&#xff0c;网络版计算器实现准备 2.1 套用旧头文件 2.2 封装sock API 三&#xff0c;自定义协议 3.1 关于自定义协议 3.2 实现序列化和反序列化 3.3 测试 三&#xff0c;服务器实现 3.1…...

开源模型应用落地-qwen2-7b-instruct-LoRA微调-unsloth(让微调起飞)-单机单卡-V100(十七)

一、前言 本篇文章将在v100单卡服务器上,使用unsloth去高效微调QWen2系列模型,通过阅读本文,您将能够更好地掌握这些关键技术,理解其中的关键技术要点,并应用于自己的项目中。 使用unsloth能够使模型的微调速度提高 2 - 5 倍。在处理大规模数据或对时间要求较高的场景下,…...

[数据集][目标检测]车油口挡板开关闭合检测数据集VOC+YOLO格式138张2类别

数据集格式&#xff1a;Pascal VOC格式YOLO格式(不包含分割路径的txt文件&#xff0c;仅仅包含jpg图片以及对应的VOC格式xml文件和yolo格式txt文件) 图片数量(jpg文件个数)&#xff1a;138 标注数量(xml文件个数)&#xff1a;138 标注数量(txt文件个数)&#xff1a;138 标注类别…...

Delphi 的 RSA 库 LockBox

LockBox 是用于 Delphi 的一套加密/解密控件 最早是一套商业控件&#xff0c;后来开源了。再后来&#xff0c;又有一个新版本的 LockBox&#xff0c;和旧版本完全不同。 旧版本的 LockBox 叫 LockBox 2&#xff1b;新版本的叫 LockBox 3。 这两个控件&#xff0c;都可以通过…...

element UI学习使用(1)

https://element.eleme.cn/2.6/#/zh-CN/component/container vue模块库&#xff0c;可复制直接使用 1、搜索框、下拉搜索框 <el-form :inline"true" class"demo-form-inline"><el-form-item label"结果搜索"><el-inputplaceho…...

如何搞定日语翻译?试试这四款工具

写一篇字数800-1000字的软文&#xff0c;用翻译新手的角度分享福昕翻译在线、福昕翻译客户端、海鲸AI翻译以及彩云翻译在翻译日语时候的表现&#xff0c;要求口语化表达。 最近对于一些轻小说突然感兴趣了&#xff0c;所以我开始尝试各种翻译工具来帮助我搞定日语翻译。今天&am…...

【STM32】独立看门狗(IWDG)原理详解及编程实践(上)

本篇文章是对STM32单片机“独立看门狗&#xff08;IWDG&#xff09;”的原理进行讲解。希望我的分享对你有所帮助&#xff01; 目录 一、什么是独立看门狗 &#xff08;一&#xff09;简介 &#xff08;二&#xff09;、独立看门狗的原理 &#xff08;三&#xff09;、具体操…...

前端框架大观:探索现代Web开发的基石

目录 引言 一、前端框架概述 二、主流前端框架介绍 2.1 React 2.1.1 简介 2.1.2 特点 2.1.3 代码示例 2.2 Vue.js 2.2.1 简介 2.2.2 特点 2.2.3 代码示例 2.3 Angular 2.3.1 简介 2.3.2 特点 2.3.3 代码示例 三、其他前端框架与库 四、前端框架的选择 五、结…...

16 训练自己语言模型

在很多场景下下&#xff0c;可能微调模型并不能带来一个较好的效果。因为特定领域场景下&#xff0c;通用话模型过于通用&#xff0c;出现多而不精。样样通样样松&#xff1b;本章主要介绍如何在特定的数据上对模型进行预训练&#xff1b; 训练自己的语言模型&#xff08;从头开…...

云原生核心技术 (7/12): K8s 核心概念白话解读(上):Pod 和 Deployment 究竟是什么?

大家好&#xff0c;欢迎来到《云原生核心技术》系列的第七篇&#xff01; 在上一篇&#xff0c;我们成功地使用 Minikube 或 kind 在自己的电脑上搭建起了一个迷你但功能完备的 Kubernetes 集群。现在&#xff0c;我们就像一个拥有了一块崭新数字土地的农场主&#xff0c;是时…...

内存分配函数malloc kmalloc vmalloc

内存分配函数malloc kmalloc vmalloc malloc实现步骤: 1)请求大小调整:首先,malloc 需要调整用户请求的大小,以适应内部数据结构(例如,可能需要存储额外的元数据)。通常,这包括对齐调整,确保分配的内存地址满足特定硬件要求(如对齐到8字节或16字节边界)。 2)空闲…...

C++初阶-list的底层

目录 1.std::list实现的所有代码 2.list的简单介绍 2.1实现list的类 2.2_list_iterator的实现 2.2.1_list_iterator实现的原因和好处 2.2.2_list_iterator实现 2.3_list_node的实现 2.3.1. 避免递归的模板依赖 2.3.2. 内存布局一致性 2.3.3. 类型安全的替代方案 2.3.…...

PPT|230页| 制造集团企业供应链端到端的数字化解决方案:从需求到结算的全链路业务闭环构建

制造业采购供应链管理是企业运营的核心环节&#xff0c;供应链协同管理在供应链上下游企业之间建立紧密的合作关系&#xff0c;通过信息共享、资源整合、业务协同等方式&#xff0c;实现供应链的全面管理和优化&#xff0c;提高供应链的效率和透明度&#xff0c;降低供应链的成…...

SCAU期末笔记 - 数据分析与数据挖掘题库解析

这门怎么题库答案不全啊日 来简单学一下子来 一、选择题&#xff08;可多选&#xff09; 将原始数据进行集成、变换、维度规约、数值规约是在以下哪个步骤的任务?(C) A. 频繁模式挖掘 B.分类和预测 C.数据预处理 D.数据流挖掘 A. 频繁模式挖掘&#xff1a;专注于发现数据中…...

vscode(仍待补充)

写于2025 6.9 主包将加入vscode这个更权威的圈子 vscode的基本使用 侧边栏 vscode还能连接ssh&#xff1f; debug时使用的launch文件 1.task.json {"tasks": [{"type": "cppbuild","label": "C/C: gcc.exe 生成活动文件"…...

【网络安全产品大调研系列】2. 体验漏洞扫描

前言 2023 年漏洞扫描服务市场规模预计为 3.06&#xff08;十亿美元&#xff09;。漏洞扫描服务市场行业预计将从 2024 年的 3.48&#xff08;十亿美元&#xff09;增长到 2032 年的 9.54&#xff08;十亿美元&#xff09;。预测期内漏洞扫描服务市场 CAGR&#xff08;增长率&…...

基于当前项目通过npm包形式暴露公共组件

1.package.sjon文件配置 其中xh-flowable就是暴露出去的npm包名 2.创建tpyes文件夹&#xff0c;并新增内容 3.创建package文件夹...

Cloudflare 从 Nginx 到 Pingora:性能、效率与安全的全面升级

在互联网的快速发展中&#xff0c;高性能、高效率和高安全性的网络服务成为了各大互联网基础设施提供商的核心追求。Cloudflare 作为全球领先的互联网安全和基础设施公司&#xff0c;近期做出了一个重大技术决策&#xff1a;弃用长期使用的 Nginx&#xff0c;转而采用其内部开发…...

2023赣州旅游投资集团

单选题 1.“不登高山&#xff0c;不知天之高也&#xff1b;不临深溪&#xff0c;不知地之厚也。”这句话说明_____。 A、人的意识具有创造性 B、人的认识是独立于实践之外的 C、实践在认识过程中具有决定作用 D、人的一切知识都是从直接经验中获得的 参考答案: C 本题解…...