MYSQL窗口函数

article/2025/10/11 2:33:33

文章目录

  • 一、为何使用窗口函数
  • 二、什么是窗口函数
  • 三、窗口函数如何使用
    • 3.1 序号函数
    • 3.2 分布函数
    • 3.3 前后函数
    • 3.4 头尾函数
    • 3.5 其他函数
    • 3.6 聚类窗口函数

在这里插入图片描述

一、为何使用窗口函数

在日常工作中经常会遇到类似这样的需求：

怎么样得到各部门工资排名前N名的员工列表?
查找各部门每人工资占部门总工资的百分比?

对于这样的需求，使用传统的SQL实现起来比较困难，这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作。不使用窗口函数的话可能要进行多次的表连接操作，可读性差的同时还会影响性能。

窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好, 注意, 是每一条；因为MySQL的普通聚合函数的结果(如 group by)是每一组只有一条记录。

二、什么是窗口函数

  • 窗口函数也称为OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

  • 窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数，有的函数，随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

  • 窗口函数和普通聚合函数也很容易混淆，二者区别如下：

  1. 聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，查询结果并不会改变记录条数，有几条记录执行完还是几条。
  2. 普通聚合函数也可以用于窗口函数中，赋予它窗口函数的功能。
    原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了，所以不会再产生数据聚合。

窗口函数的简单语法如下：

<窗口函数> OVER (partition by <用于分组的列名>order by <用于排序的列名>)

三、窗口函数如何使用

数据表：

drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',exam_id int UNIQUE NOT NULL COMMENT '试卷ID',tag varchar(32) COMMENT '类别标签',difficulty varchar(8) COMMENT '难度',duration int NOT NULL COMMENT '时长',release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE TABLE user_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int UNIQUE NOT NULL COMMENT '用户ID',`nick_name` varchar(64) COMMENT '昵称',achievement int COMMENT '成就值',level int COMMENT '用户等级',job varchar(32) COMMENT '职业方向',register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE TABLE exam_record (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int NOT NULL COMMENT '用户ID',exam_id int NOT NULL COMMENT '试卷ID',start_time datetime NOT NULL COMMENT '开始时间',submit_time datetime COMMENT '提交时间',score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES(1001, '牛客1', 3200, 7, '算法', '2020-01-01 10:00:00'),(1002, '牛客2号', 2500, 6, '算法', '2020-01-01 10:00:00'),(1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00');INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),(9002, 'SQL', 'hard', 80, '2020-01-01 10:00:00'),(9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),(9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', null, null),
(1002, 9001, '2020-02-01 12:11:01', null, null),
(1003, 9001, '2020-03-01 19:01:01', null, null),
(1001, 9001, '2020-03-01 12:01:01', null, null),
(1002, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1001, 9002, '2020-01-01 12:11:01', null, null),
(1002, 9001, '2020-01-01 18:01:01', '2020-01-01 18:59:02', 90),
(1002, 9003, '2020-05-06 12:01:01', null, null),
(1001, 9002, '2020-05-05 18:01:01', null, null);

3.1 序号函数

  • ROW_NUMBER()：顺序排序——1、2、3
  • RANK()：并列排序，跳过重复序号——1、1、3
  • DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
select nick_name,ei.exam_id,score,
row_number() over(partition by nick_name order by score desc) row_ranking,
rank() over(partition by nick_name order by score desc) ranking,
dense_rank() over(partition by nick_name order by score desc) dense_ranking
from user_info ui 
join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where score is not null

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

3.2 分布函数

  1. percent_rank()
  • 用途：和之前的RANK()函数相关，每行按照如下公式进行计算： (rank - 1) / (rows - 1)

其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

  1. cume_dist()
  • 用途：分组内大于等于当前rank值的行数/分组内总行数，这个函数比percen_rank使用场景更多。

应用场景：班级中比当前同学成绩高的学生比例是多少

select nick_name,ei.exam_id,score,
PERCENT_RANK() OVER (PARTITION BY nick_nameORDER BY score DESC) as percent,
CUME_DIST() OVER (PARTITION BY nick_nameORDER BY score DESC) as cumdist
from user_info ui 
join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where score is not null

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

3.3 前后函数

  • lead(n) / lag(n)
  • 用途：分组中位于当前行后n行（lead）/ 前n行(lag)的记录值。
  • 应用场景：求每个用户相邻两次浏览的时间差
select nick_name,ei.exam_id,score,lead(score,1) OVER (PARTITION BY nick_nameORDER BY score DESC) as leadVal,lag(score,1) OVER (PARTITION BY nick_nameORDER BY score DESC) as lagVal
from user_info ui 
join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where score is not null

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

3.4 头尾函数

  • first_val(expr) / last_val(expr)
  • 用途：得到分区中的第一个/最后一个指定参数的值
select nick_name,ei.exam_id,score,FIRST_VALUE(score) OVER (PARTITION BY nick_nameORDER BY score DESC) as firstVal,LAST_VALUE(score) OVER (PARTITION BY nick_nameORDER BY score DESC) as lastVal
from user_info ui 
join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where score is not null

结果：

在这里插入图片描述
截止到第二行，第一个记录为90分，最后一个记录为90分；

截止到第四行，第一个记录为90分，最后一个记录为81分.

3.5 其他函数

  1. nth_value(expr, n)
  • 用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名
select nick_name,ei.exam_id,score,nth_value(score,1) OVER (PARTITION BY nick_nameORDER BY score DESC) as 1th,nth_value(score,2) OVER (PARTITION BY nick_nameORDER BY score DESC) as 2th
from user_info ui 
join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where score is not null
  • 结果：

在这里插入图片描述
2. nfile()

  • 用途：将分区中的有序数据分为n个桶，记录桶号。
  • 此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NFILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均，多出来的部分则依次加给第一组、第二组···直到分配完。

3.6 聚类窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

select *,sum(成绩) over (order by 学号) as current_sum,avg(成绩) over (order by 学号) as current_avg,count(成绩) over (order by 学号) as current_count,max(成绩) over (order by 学号) as current_max,min(成绩) over (order by 学号) as current_min
from 班级表

在这里插入图片描述
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

参考链接：狗哥数据分析
数据来源：牛客网


http://chatgpt.dhexx.cn/article/gVOXN84M.shtml

相关文章

MySQL常见函数

MySQL常见函数

一、单行函数 1.1 字符函数 length(str)&#xff1a;统计字符串的字节数&#xff08;取决于编码方式&#xff0c;utf8汉字3字节&#xff0c;gbk汉字2字节&#xff09;concat(str1, str2)&#xff1a;拼接字符substr/substring(str, n1 [,n2])&#xff1a;切割字符&#xff0c…
阅读更多...
MySQL常用函数大全（总结篇）

MySQL常用函数大全（总结篇）

本篇文章讲解是是MySQL的函数方法&#xff0c;涵盖所有的MySQL常见的方法。下面是本篇文章的目录结构&#xff0c;可以根据自己需求点击右方目录查找&#xff1a; 一、数字函数二、字符串函数三、日期函数四、MySQL高级函数 &#xff08;一&#xff09;数字函数 1、ABS(x) 返…
阅读更多...
MySQL常用函数大全（实例演示）

MySQL常用函数大全（实例演示）

此博客主要包括如下类型函数&#xff1a; 一、数字函数 二、字符串函数 三、日期函数 四、MySQL高级函数 一、数字函数 1、ABS(x) 返回x的绝对值 SELECT ABS(-1); 结果&#xff1a; 2、AVG(expression) 返回一个表达式的平均值&#xff0c;expression 是一个字段 SELE…
阅读更多...
MySql常用函数大全

MySql常用函数大全

MySql常用函数大全 MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数&#xff0c;可以简化用户的操作。例如&#xff0c;字符串连接函数可以很方便的将多个字符串连接…
阅读更多...
MySQL函数大全

MySQL函数大全

目录 &#xff08;一&#xff09;字符函数 ①length(str)函数 ②concat(str1,str2,...)函数 ③upper(str)、lower(str)函数 ④substr(str,start,len)函数 ⑤instr(str,要查找的子串)函数 ⑥trim(str)函数 ⑦lpad(str,len,填充字符)、rpad(str,len,填充字符)函数 ⑧rep…
阅读更多...
MySQL函数（经典收藏）

MySQL函数（经典收藏）

MySQL函数&#xff08;经典收藏&#xff09; MySQL函数 MySQL数据库提供了很多函数包括&#xff1a; 数学函数&#xff1b;字符串函数&#xff1b;日期和时间函数&#xff1b;条件判断函数&#xff1b;系统信息函数&#xff1b;加密函数&#xff1b;格式化函数&#xff1b; …
阅读更多...
MySql常用函数大全（详细）

MySql常用函数大全（详细）

一、数学函数 &#xff08;1&#xff09;ABS&#xff08;x&#xff09;返回绝对值&#xff1b;例&#xff1a; &#xff08;2&#xff09;PI&#xff08;&#xff09;返回圆周率的函数&#xff0c;默认值为小数后六位&#xff1b;例&#xff1a; &#xff08;3&#xff09;求函…
阅读更多...
齐全且实用的MySQL函数使用大全

齐全且实用的MySQL函数使用大全

目录 一、MySQL函数介绍 二、MySQL函数分类 &#xff08;一&#xff09;单行函数 ①字符串函数 ②数学函数 ③日期函数 ④流程控制函数 ⑤系统信息函数 ⑥其他函数 &#xff08;二&#xff09;聚合函数 三、函数使用示例 &#xff08;一&#xff09;字符函数 ①le…
阅读更多...
sql注入的小工具介绍

sql注入的小工具介绍

sql注入的小工具介绍 啊D注入工具&#xff1a; pangolin&#xff08;穿山甲&#xff09; 穿山甲&#xff1b;Pangolin&#xff08;中文译名为穿山甲&#xff09;一款帮助渗透测试人员进行Sql注入测试的安全工具&#xff0c;是深圳宇造诺赛科技有限公司(Nosec)旗下的网站安全测…
阅读更多...
sql注入检测工具 mysql_SQL注入测试

sql注入检测工具 mysql_SQL注入测试

简介 SQL 注入是一种专门针对SQL语句的攻击方式。通过把SQL命令插入到web表单提交、输入域名或者页面请求的查询字符串中&#xff0c;利用现有的程序&#xff0c;来非法获取后台的数据库中的信息。在web的测试中涉及到的会比较多些。 注入原理 存在注入的原因是后台在编写程序时…
阅读更多...
mysql注入扫描网站漏洞工具_网站安全检测，高手必备几款SQL注入工具

mysql注入扫描网站漏洞工具_网站安全检测，高手必备几款SQL注入工具

按照百度说法&#xff0c;SQL注入是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串&#xff0c;最终达到欺骗服务器执行恶意的SQL命令。 利用SQL注入&#xff0c;攻击者可远程利用SQL注入漏洞&#xff0c;窃取用户数据库数据&#xff0c;包括用户名、密码、登…
阅读更多...
3-5SQL注入漏洞工具的使用

3-5SQL注入漏洞工具的使用

如果我们要去找出&#xff0c;一个网站中可能存在的所有漏洞&#xff0c;那对于我们而言是相当累的&#xff0c;有时候&#xff0c;我们只能借助于sql注入漏洞的工具&#xff0c;进行检测&#xff0c;检测的工具对我们相当重要&#xff0c;因为他能替代人工和提高效率 我们本篇…
阅读更多...
sql注入检测工具 mysql_Java自动化SQL注入测试工具—jSQL Injection v0.5

sql注入检测工具 mysql_Java自动化SQL注入测试工具—jSQL Injection v0.5

jSQL是一款轻量级安全测试工具&#xff0c;可以检测SQL注入漏洞。它跨平台(Windows, Linux, Mac OS X, Solaris)、开源且免费。 更新记录 0.5SQL shell Uploader 0.4 Admin page checker and preview Brute forcer (md5 mysql...) Coder (encode decode base64 hex md5...) 0.3…
阅读更多...
sql注入工具、检测及手工注入集合

sql注入工具、检测及手工注入集合

0x01 sqlmap sqlmap是一个开源渗透测试工具&#xff0c;它可以自动检测和利用SQL注入缺陷&#xff0c;并接管数据库服务器。它配备了强大的检测引擎&#xff0c;为最终渗透测试提供了许多细分功能&#xff0c;以及广泛的交换机&#xff0c;从数据库指纹、从数据库获取数据&…
阅读更多...
sql 注入工具

sql 注入工具

众所周知&#xff0c;SQL注入攻击是最为常见的Web应用程序攻击技术。同时SQL注入攻击所带来的安全破坏也是不可弥补的。以下罗列的10款SQL工具可帮助管理员及时检测存在的漏洞。 BSQL Hacker BSQL Hacker是由Portcullis实验室开发的&#xff0c;BSQL Hacker 是一个SQL自动注入工…
阅读更多...
sql注入 学习笔记

sql注入 学习笔记

学习来源视频&#xff1a;https://www.bilibili.com/video/BV1HT411E7bH?p1&vd_source91ac068ef47e260a09856e8db81907c9 什么是sql注入 sql注入又称sql injection&#xff0c;通过网页或者网络指令的方式修改原本的sql指令&#xff0c;从而从数据库中查询的敏感的信息。…
阅读更多...
Java 防止 SQL 注入工具类

Java 防止 SQL 注入工具类

2019独角兽企业重金招聘Python工程师标准>>> package cn.manmanda.api.util;import javax.servlet.http.HttpServletRequest;/*** 防止SQL注入工具类* author * date 2017/12/29 15:39*/ public class AntiSQLInjectionUtil {// public final static String regex …
阅读更多...
java防止SQL注入

java防止SQL注入

前言 这里常用的方法就不说了&#xff0c;#{}预编译不说&#xff0c;但是如果我们不得不用${}来传递参数&#xff0c;那就有点麻烦了&#xff0c;另外&#xff0c;一般我们会用到druid自带的sql注入过滤&#xff1a; 但是经过测试发现&#xff0c;这个方法并不能完全屏宾sql注…
阅读更多...
Pangolin-最好的SQL注入工具

Pangolin-最好的SQL注入工具

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow 也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！ 点击这里使用RSS订阅本Blog： <script type="text/javascript" src="…
阅读更多...
sql注入工具--Sqlmap

sql注入工具--Sqlmap

Sqlmap是一款自动化sql注入的工具&#xff0c;其功能强大&#xff0c;支持多种数据库&#xff0c;包括mysql&#xff0c;sqlserver等主流的数据库&#xff0c;采用独特的sql注入方法来达到自动化注入扫描漏洞的目的。 1&#xff09;基于布尔的盲注&#xff08;Type:boolean-bas…
阅读更多...
推荐文章

Copyright @ 2022~2023