mysql学习教程,从入门到精通,SQL窗口函数(38)
1、SQL窗口函数
SQL窗口函数(Window Functions)是一种强大的数据分析工具,它们允许你在结果集的行上执行计算,而不需要将这些行分组到单独的输出行中。窗口函数通常与OVER()子句一起使用,该子句定义了窗口或分区,以及窗口内的排序规则。以下是SQL窗口函数的一般语法:
<window_function>() OVER ([PARTITION BY <partition_expression>, ...][ORDER BY <order_expression> [ASC|DESC], ...][ROWS or RANGE <frame_clause>]
)
-
<window_function>():这是你要应用的窗口函数,如ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、MIN()、MAX()等。注意,虽然SUM()、AVG()、MIN()和MAX()通常是聚合函数,但当它们与OVER()子句一起使用时,它们就变成了窗口函数。 -
OVER():这个子句定义了窗口函数的操作范围。 -
PARTITION BY <partition_expression>:可选。这个子句将结果集划分为分区,窗口函数将在每个分区内独立计算。如果没有PARTITION BY,则整个结果集被视为一个单一的分区。 -
ORDER BY <order_expression>:通常必需(但某些窗口函数可能不需要)。这个子句定义了窗口内行的排序顺序。这对于计算如排名或累计和等窗口函数是必需的。 -
ROWS or RANGE <frame_clause>:可选。这个子句定义了窗口帧(frame),即窗口函数将考虑的行集。ROWS基于物理行数,而RANGE基于值的范围。常见的帧子句包括ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(从分区开始到当前行)和RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(基于值的范围,但具体行为可能因数据库而异)。
以下是一些常见的窗口函数及其用途:
ROW_NUMBER():为窗口内的每一行分配一个唯一的序号。RANK():为窗口内的行分配排名,跳过相同的值并留下空位。DENSE_RANK():为窗口内的行分配排名,不跳过相同的值。SUM()、AVG()、MIN()、MAX():计算窗口内行的总和、平均值、最小值和最大值。
例如,以下查询使用ROW_NUMBER()窗口函数为按销售额排序的每个销售人员的销售记录分配一个序号:
SELECTsalesperson_id,sale_date,amount,ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS sale_rank
FROMsales;
在这个查询中,ROW_NUMBER()函数在salesperson_id分区内按sale_date排序为每行分配一个序号(sale_rank)。
窗口函数(Window Functions)是SQL中的一种强大工具,用于在查询结果集的行上执行计算,而不需要将结果集分组为单独的输出行。这些函数通常用于执行诸如排名、累计总和、移动平均等操作。
以下是一个示例,展示如何使用窗口函数来计算每个员工在部门内的工资排名和累计工资总和。
假设我们有一个名为 employees 的表,结构如下:
CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(100),department_id INT,salary DECIMAL(10, 2)
);
并且表中有以下数据:
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, 'Alice', 1, 70000),
(2, 'Bob', 1, 50000),
(3, 'Charlie', 1, 60000),
(4, 'David', 2, 80000),
(5, 'Eve', 2, 90000),
(6, 'Frank', 2, 75000);
现在,我们希望计算每个员工在其部门内的工资排名(按降序排列)和累计工资总和。
可以使用以下SQL查询:
SELECTemployee_id,employee_name,department_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROMemployees;
解释:
-
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC):RANK()是窗口函数之一,用于计算排名。PARTITION BY department_id表示将结果集按部门分区。ORDER BY salary DESC表示在每个分区内按工资降序排列。- 结果是每个员工在其部门内的工资排名。
-
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):SUM()是聚合函数之一,但在这里用作窗口函数。PARTITION BY department_id同样表示将结果集按部门分区。ORDER BY salary DESC表示在每个分区内按工资降序排列。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示累计从分区开始到当前行的所有行的工资总和。- 结果是每个员工在其部门内的累计工资总和。
运行上述查询后,结果将如下所示:
employee_id | employee_name | department_id | salary | salary_rank | cumulative_salary
------------|---------------|---------------|--------|-------------|-------------------
4 | David | 2 | 80000 | 1 | 80000
5 | Eve | 2 | 90000 | 2 | 170000
6 | Frank | 2 | 75000 | 3 | 245000
1 | Alice | 1 | 70000 | 1 | 70000
3 | Charlie | 1 | 60000 | 2 | 130000
2 | Bob | 1 | 50000 | 3 | 180000
这样,我们就成功地使用了窗口函数来计算每个员工在其部门内的工资排名和累计工资总和。
当然可以。以下是一些使用SQL窗口函数的实际案例,这些案例展示了窗口函数在不同场景下的应用。
案例一:累计销售额计算
假设有一个销售数据表 sales,包含以下字段:date(日期)、amount(销售额)。我们需要计算到当前日期为止的累计销售额。
SELECTdate,amount,SUM(amount) OVER (ORDER BY date) AS cumulative_total
FROMsales;
在这个查询中,SUM(amount) OVER (ORDER BY date) 是一个窗口函数,它按照日期的顺序对销售额进行累计求和。结果集将包含每一天的销售额以及到该天为止的累计销售额。
案例二:部门内工资排名
考虑一个员工表 employees,包含以下字段:employee_id(员工ID)、name(姓名)、department_id(部门ID)、salary(工资)。我们希望计算每个员工在其部门内的工资排名。
SELECTemployee_id,name,department_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROMemployees;
在这个查询中,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 是一个窗口函数,它首先按部门对员工进行分区,然后在每个分区内按工资降序排列,并计算排名。结果集将包含每个员工的ID、姓名、部门ID、工资以及在其部门内的工资排名。
案例三:累计计数
假设我们想要计算到当前日期为止的累计销售次数,可以使用 COUNT() 窗口函数。
SELECTdate,COUNT(*) OVER (ORDER BY date) AS cumulative_count
FROMsales;
这个查询将返回每一天的日期以及到该天为止的累计销售次数。
案例四:获取前一行和后一行的数据
有时候,我们需要获取当前行前一行或后一行的数据。这可以使用 LAG() 和 LEAD() 窗口函数来实现。
SELECTemployee_id,salary,LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary,LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROMemployees;
在这个查询中,LAG(salary, 1) OVER (ORDER BY salary) 返回当前行前一行的工资,而 LEAD(salary, 1) OVER (ORDER BY salary) 返回当前行后一行的工资。结果集将包含每个员工的ID、工资以及前一行和后一行的工资。
案例五:分组内的累计平均值
假设我们有一个表 student_scores,包含以下字段:student_id(学生ID)、course_id(课程ID)、score(成绩)。我们希望计算每个学生在每门课程内的累计平均成绩。
SELECTstudent_id,course_id,score,AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_average
FROMstudent_scores;
在这个查询中,AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 是一个窗口函数,它首先按学生和课程进行分区,然后在每个分区内按成绩顺序计算累计平均成绩。结果集将包含每个学生的ID、课程ID、成绩以及在该课程内的累计平均成绩。
这些案例展示了窗口函数在SQL中的广泛应用,它们可以极大地简化复杂的数据分析任务。
相关文章:
mysql学习教程,从入门到精通,SQL窗口函数(38)
1、SQL窗口函数 SQL窗口函数(Window Functions)是一种强大的数据分析工具,它们允许你在结果集的行上执行计算,而不需要将这些行分组到单独的输出行中。窗口函数通常与OVER()子句一起使用,该子句定义了窗口或分区&…...
gbase8s数据库实现黑白名单的几种方案
1、借用操作系统的黑白名单 2、使用数据库 TRUSTED CONTEXT 机制 CREATE TRUSTED CONTEXT tcx1USER rootATTRIBUTES (ADDRESS 172.16.39.162)ATTRIBUTES (ADDRESS 172.16.39.163)ENABLEWITH USE FOR wangyx WITHOUT AUTHENTICATION; 如上创建 可信任上下文对象 tcx1 在 jdb…...
Qt-窗口布局按钮输入类
1. 窗口布局 Qt 提供了很多摆放控件的辅助工具(又称布局管理器或者布局控件),它们可以完成两件事: 自动调整控件的位置,包括控件之间的间距、对齐等; 当用户调整窗口大小时,位于布局管理器内的…...
Apache DolphinScheduler社区9月进展记录
各位热爱 Apache DolphinScheduler 的小伙伴们,社区 9 月月报更新啦!这里将记录 Apache DolphinScheduler 社区每月的重要更新,欢迎关注! 月度 Merge Star 感谢以下小伙伴上个月为 Apache DolphinScheduler 做的精彩贡献&#x…...
在docker中安装并运行mysql8.0.31
第一步:命令行拉取mysql镜像 docker pull mysql:8.0.31查看是否拉取成功 docker images mysql:latest第二步:运行mysql镜像,启动mysql实例 docker run -p 3307:3307 -e MYSQL_ROOT_PASSWORD"123456" -d mysql:8.0.313307:3307前…...
C++ | Leetcode C++题解之第458题可怜的小猪
题目: 题解: class Solution { public:int poorPigs(int buckets, int minutesToDie, int minutesToTest) {if (buckets 1) {return 0;}vector<vector<int>> combinations(buckets 1,vector<int>(buckets 1));combinations[0][0] …...
【万字长文】Word2Vec计算详解(三)分层Softmax与负采样
【万字长文】Word2Vec计算详解(三)分层Softmax与负采样 写在前面 第三部分介绍Word2Vec模型的两种优化方案。 【万字长文】Word2Vec计算详解(一)CBOW模型 markdown行 9000 【万字长文】Word2Vec计算详解(二࿰…...
【分布式微服务云原生】探索Dubbo:接口定义语言的多样性与选择
目录 探索Dubbo:接口定义语言的多样性与选择引言Dubbo的接口定义语言(IDL)1. Java接口2. XML配置3. 注解4. Protobuf IDL 流程图:Dubbo服务定义流程表格:Dubbo IDL方式比较结论呼吁行动Excel表格:Dubbo IDL…...
SAP将假脱机(Spool requests)内容转换为PDF文档[RSTXPDFT4]
将假脱机(Spool requests)内容转换为PDF文档[RSTXPDFT4] 有时需要将Spool中的内容导出成PDF文件,sap提供了一个标准程序RSTXPDFT4可以实现此功能。 1, Tcode:SP01, 进入spool requests list 2, SE38 运行程序RSTXPDFT4 输入spool reqeust号码18680,然后…...
DNS能加速游戏吗?
在游戏玩家追求极致游戏体验的今天,任何可能提升游戏性能的因素都备受关注,DNS(域名系统)便是其中一个被探讨的对象。那么,DNS能加速游戏吗? 首先,我们需要了解DNS的基本功能。DNS就像是互联网…...
Raspberry Pi3B+之C/C++开发环境搭建
Raspberry Pi3B之C/C开发环境搭建 1. 源由2. 环境搭建2.1 搭建C语言开发环境2.2 工程目录结构2.3 Makefile2.4 Demo (main.c) 3. 测试工程3.1 编译3.2 运行 4. 总结5. 参考资料 1. 源由 为了配合《Ardupilot开源飞控之FollowMe验证平台搭建》,以及VINS-Fusion对于图…...
[笔记] 仿射变换性质的代数证明
Title: [笔记] 仿射变换性质的代数证明 文章目录 I. 仿射变换的代数表示II. 仿射变换的性质III. 同素性的代数证明1. 点变换为点2. 直线变换为直线 IV. 结合性的代数证明1. 直线上一点映射为直线上一点2. 直线外一点映射为直线外一点 V. 保持单比的代数证明VI. 平行性的代数证明…...
遥感影像-语义分割数据集:sar水体数据集详细介绍及训练样本处理流程
原始数据集详情 简介:该数据集由WHU-OPT-SAR数据集整理而来,覆盖面积51448.56公里,分辨率为5米。据我们所知,WHU-OPT-SAR是第一个也是最大的土地利用分类数据集,它融合了高分辨率光学和SAR图像,并进行了充…...
极狐GitLab 发布安全补丁版本 17.4.1、17.3.4、17.2.8
GitLab 是一个全球知名的一体化 DevOps 平台,很多人都通过私有化部署 GitLab 来进行源代码托管。极狐GitLab 是 GitLab 在中国的发行版,专门为中国程序员服务。可以一键式部署极狐GitLab。 学习极狐GitLab 的相关资料: 极狐GitLab 官网极狐…...
汽车管理系统中使用函数
目录 setupUisetEnabledcurrentText()setTextsetFocus()query.exec(...)addWidgetconnect setupUi setupUi() 是 ui 对象的一个成员函数,它的作用是根据 .ui 文件中的设计,将设计好的组件(如按钮、文本框、布局等)添加到当前的窗…...
大数据分析入门概述
大数据分析入门概述 本文旨在为有意向学习数据分析、数据开发等大数据方向的初学者提供一个学习指南,当然如果你希望通过视频课程的方式快速入门,B站UP主戴戴戴师兄的课程质量很高,并且适合初学者快速入门。本文的目的旨在为想要了解大数据但…...
提示工程、微调和 RAG
自众多大型语言模型(LLM)和高级对话模型发布以来,人们已经运用了各种技术来从这些 AI 系统中提取所需的输出。其中一些方法会改变模型的行为来更好地贴近我们的期望,而另一些方法则侧重于增强我们查询 LLM 的方式,以提…...
自动化测试中如何高效进行元素定位!
前言 在自动化测试中,元素定位是一项非常重要的工作。良好的元素定位可以帮助测试人员处理大量的测试用例,加快测试进度,降低工作负担。但是在实际的测试工作中,我们常常遇到各种各样的定位问题,比如元素定位失败、元…...
UE5数字人制作平台使用及3D模型生成
第10章 数字人制作平台使用及3D模型生成 在数字娱乐、虚拟现实(VR)、增强现实(AR)等领域,高质量的3D模型是数字内容创作的核心。本章将引导你了解如何使用UE5(Unreal Engine 5)虚幻引擎这一强大…...
Linux进程被占用如何杀死进程
文章目录 前言一、根据名称进行查找程序所占用的端口号二、杀死进程总结 前言 由于Linux中,校园网登录的时候容易出现端口被占用,如何快速查找程序所占用的端口号。 提示:以下是本篇文章正文内容,下面案例可供参考 一、根据名称…...
React hook之useRef
React useRef 详解 useRef 是 React 提供的一个 Hook,用于在函数组件中创建可变的引用对象。它在 React 开发中有多种重要用途,下面我将全面详细地介绍它的特性和用法。 基本概念 1. 创建 ref const refContainer useRef(initialValue);initialValu…...
Zustand 状态管理库:极简而强大的解决方案
Zustand 是一个轻量级、快速和可扩展的状态管理库,特别适合 React 应用。它以简洁的 API 和高效的性能解决了 Redux 等状态管理方案中的繁琐问题。 核心优势对比 基本使用指南 1. 创建 Store // store.js import create from zustandconst useStore create((set)…...
抖音增长新引擎:品融电商,一站式全案代运营领跑者
抖音增长新引擎:品融电商,一站式全案代运营领跑者 在抖音这个日活超7亿的流量汪洋中,品牌如何破浪前行?自建团队成本高、效果难控;碎片化运营又难成合力——这正是许多企业面临的增长困局。品融电商以「抖音全案代运营…...
今日学习:Spring线程池|并发修改异常|链路丢失|登录续期|VIP过期策略|数值类缓存
文章目录 优雅版线程池ThreadPoolTaskExecutor和ThreadPoolTaskExecutor的装饰器并发修改异常并发修改异常简介实现机制设计原因及意义 使用线程池造成的链路丢失问题线程池导致的链路丢失问题发生原因 常见解决方法更好的解决方法设计精妙之处 登录续期登录续期常见实现方式特…...
使用Matplotlib创建炫酷的3D散点图:数据可视化的新维度
文章目录 基础实现代码代码解析进阶技巧1. 自定义点的大小和颜色2. 添加图例和样式美化3. 真实数据应用示例实用技巧与注意事项完整示例(带样式)应用场景在数据科学和可视化领域,三维图形能为我们提供更丰富的数据洞察。本文将手把手教你如何使用Python的Matplotlib库创建引…...
Docker 本地安装 mysql 数据库
Docker: Accelerated Container Application Development 下载对应操作系统版本的 docker ;并安装。 基础操作不再赘述。 打开 macOS 终端,开始 docker 安装mysql之旅 第一步 docker search mysql 》〉docker search mysql NAME DE…...
Linux nano命令的基本使用
参考资料 GNU nanoを使いこなすnano基础 目录 一. 简介二. 文件打开2.1 普通方式打开文件2.2 只读方式打开文件 三. 文件查看3.1 打开文件时,显示行号3.2 翻页查看 四. 文件编辑4.1 Ctrl K 复制 和 Ctrl U 粘贴4.2 Alt/Esc U 撤回 五. 文件保存与退出5.1 Ctrl …...
基于PHP的连锁酒店管理系统
有需要请加文章底部Q哦 可远程调试 基于PHP的连锁酒店管理系统 一 介绍 连锁酒店管理系统基于原生PHP开发,数据库mysql,前端bootstrap。系统角色分为用户和管理员。 技术栈 phpmysqlbootstrapphpstudyvscode 二 功能 用户 1 注册/登录/注销 2 个人中…...
破解路内监管盲区:免布线低位视频桩重塑停车管理新标准
城市路内停车管理常因行道树遮挡、高位设备盲区等问题,导致车牌识别率低、逃费率高,传统模式在复杂路段束手无策。免布线低位视频桩凭借超低视角部署与智能算法,正成为破局关键。该设备安装于车位侧方0.5-0.7米高度,直接规避树枝遮…...
Python竞赛环境搭建全攻略
Python环境搭建竞赛技术文章大纲 竞赛背景与意义 竞赛的目的与价值Python在竞赛中的应用场景环境搭建对竞赛效率的影响 竞赛环境需求分析 常见竞赛类型(算法、数据分析、机器学习等)不同竞赛对Python版本及库的要求硬件与操作系统的兼容性问题 Pyth…...
