MySQL 窗口函数应用

已于 2023-04-07 14:19:15 修改
阅读量1.4k 收藏 12
点赞数
分类专栏： MySQL笔记 文章标签： sql 数据库 mysql
于 2023-03-14 16:28:27 首次发布
本文为CSDN博主「王林」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
本文链接：https://blog.csdn.net/qq_45912025/article/details/129523324
版权

MySQL 窗口函数应用

这里主要是记录MySQL窗口函数的相关应用，把有用的知识形成笔记，以方便自个儿复习😊

简介

窗口函数是 SQL 中一类特别的函数。与聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。

一、窗口函数

（一）窗口函数分类

常用窗口函数分类：
在这里插入图片描述

1、排序窗口函数
  1. rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
  2. row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
  3. dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可理解为对类别进行计数)
2、分布窗口函数
  • percent_rank() over() 按照数字所在的位置进行百分位分段
  • cume_dist() over()

cume_dist() over() 详解：

如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。

公式：

  • 当升序排列，计算小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）
  • 当降序排列，计算大于等于当前值的行数/总行数。
3、前后窗口函数
  • lead(字段名，n)over()把字段数据向前移n个单元格
  • lag(字段名，n)over()把字段数据向后移n个单元格
4、头尾窗口函数
  • first_value(字段名)over() 取分组内排序后，截止到当前行，第一个值
  • last_value(字段名)over() 函数返回有序行集中的最后一行
5、聚类窗口函数
  • 主要考察聚类窗口函数，和聚类窗口函数的用法和group by 函数类似；
  • 我的理解窗口函数的关键词是“不改变表格结构”，查出的数据，单独放一列。
  1. min()over() :不改变表结构的前提下，计算出最小值
  2. max()over():不改变表结构的前提下，计算出最大值
  3. count()over():不改变表结构的前提下，计数
  4. sum()over():不改变表结构的前提下，求和
  5. avg()over():不改变表结构的前提下，求平均值
6、其他窗口函数
  • ntile(n)over() 将数字按照大小平均分成n段
  • nth_value(字段名，n) over() 取指定排名行的数据

nth_value()详解：

nth_value(字段名 ,1) from first over (order by ID) 是根据查询出的排序结果取第一行数据的值作为该列的值，如果没取到该值为空

（二）窗口函数详解

（1）排序函数详解
①详解
  • rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
  • row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
  • dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可理解为对类别进行计数)
②示例

sql

select uid  ,
    row_number()over(order by uid) row_number1, /*按照uid的大小不重不漏1 2 3 4 5 6 7 */
    rank()over(order by uid) rank1, /*按照uid的大小并列第一无第二，1 1 1 4 5 5 7*/
    dense_rank()over(order by uid) dense_rank1,/*按照uid的大小并列第一有第二，1 1 1  2 3 3 4*/
    percent_rank()over(order by uid) percent_rank1,/*按照uid的大小进行百分法排序*/
    ntile(2)over(order by uid) ntile1,/*按照uid的大小，把uid评价分成2组*/
    lead(uid)over(order by uid) lead1,/*把uid向上推1个位置*/
    lag(uid)over(order by uid) lag1 /*把uid向下推1个位置*/    
from user_info;

结果
在这里插入图片描述

（2）percent_rank() 详解

描述

以第二行的0.111111…为例，它是通过(rank - 1) / (rows - 1)计算得到的，其中rows为表的行数，即10；rank为排名，即2；结果为：(2-1)/(10-1)=1/9

sql

select *,
       percent_rank() over(order by order_price) as percent_ranking,
       rank() over(order by order_price)as ranking
from order_content;

截图

在这里插入图片描述

（3）first_value() 详解
select s.sid,s1.sname,s1.gender,c.cname,s.num,
	first_value(num) over(partition by c.cname order by num desc) as first_value1 
from score s join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid

在这里插入图片描述

（4）nth_value() 详解

sql

select id, name ,dept,
	nth_value(name,3)  over (order by id)  as a
	nth_value(name ,4) over (order by id)  as b
	nth_value(name ,1) from first over (order by id)  as c
	nth_value(name ,2) from first over (partition by dept order by id)  as d,
	nth_value(name ,2) from   first  over (order by id)  as e，
	nth_value(name ,4) from   last   over (order by id)  as f
from salary

结果

在这里插入图片描述

解析

  1. 从上面的查询结果我们可以看出nth_value(product_name,3)是根据后面的分组的原则（分组或者不分组）找到已product_name排序的第三个值作为该列的值，如果没取到该值为空；
  2. nth_value(NAME ,1) from first over (order by ID) 是根据查询出的排序结果取第一行数据的值作为该列的值，如果没取到该值为空；
  3. nth_value(NAME ,4) from last over (order by ID) 这个的用法是从排序的是4名开始往后取依次错开作为该行的值。
（5）cume_dist() 详解
  • 当升序排列，计算小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）
  • 当降序排列，计算大于等于当前值的行数/总行数。
（6）lead、lag() 详解
①详解
  1. lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了，具体配合图片理解）;
  2. lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了，具体配合图片理解）;
  3. lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上，具体配合图片理解）。
②示例

sql

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位，后面就腾空了2个位置
      Lag(score,2) over(order by id) lag_score, -- score数列向后推2位，腾空2个位置
      lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位，空值被填充为666
FROM exam_record;

结果

在这里插入图片描述

（三）其他函数详解

（1）datediff() 详解
①详解
  1. datediff(时间1，时间2)：计算两个日期之间间隔的天数，单位为日
  2. timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)
  3. date_add(日期，INTERVAL n 时间单位) ：返回加上n个时间单位后的日期
  4. date_sub(日期，INTERVAL n 时间单位 )：返回减去n个时间单位后的日期
  5. date_format(时间，‘%Y-%m-%d’)：强制转换时间为所需要的格式
②示例

在这里插入图片描述

（2）datediff、timestampdiff() 详解
①详解
  1. datediff()函数的作用是求日期差，也就是把一个时间的日期部分取出来求差。例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只取2021-09-05和2021-09-04求日期差，并不会管后面的时间部分。
  2. timestampdiff()函数的作用则是求时间戳的差，例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

直接说可能有点懵，看完差别后，具体来看下面这个例子：

②示例1

先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天

#先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1;

在这里插入图片描述

③示例2

再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天

#再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2;

在这里插入图片描述

看完这个是不是就很容易理解啦~😊😊😊

二、窗口函数SQL

（1）示例1
select tag,uid,ranking
from (
select b.tag,a.uid,max(a.score),min(a.score),
row_number() over(partition by b.tag order by max(a.score) desc,min(a.score) desc,a.uid desc) ranking
from exam_record a
left join examination_info b on a.exam_id = b.exam_id
group by b.tag,a.uid
) t
where ranking < 4
（2）示例2
select exam_id,duration,release_time
from (
select exam_id,duration,release_time,
sum(case when rk_desc=2 then time_diff 
		 when rk_asc=2  then - time_diff else 0 end) sum_time
from (
select
	a.exam_id,b.duration ,timestampdiff(minute,a.start_time,a.submit_time) time_diff,b.release_time,
	row_number() over(partition by a.exam_id order by timestampdiff(minute,a.start_time,a.submit_time) desc ) rk_desc,
	row_number() over(partition by a.exam_id order by timestampdiff(minute,a.start_time,a.submit_time) asc) rk_asc
from exam_record a left join examination_info b on a.exam_id=b.exam_id
where a.submit_time is not null 
) t1 group by exam_id
) t2 
where sum_time*2 >= duration
order by exam_id desc
（3）示例3
WITH t2 AS (
SELECT 
	uid,
	COUNT(start_time) total, -- 用户2021年作答的次数
	DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
	MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
FROM (
	SELECT uid,start_time,
	LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
	FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
	) t1
GROUP BY uid
)
SELECT uid,days_window,ROUND(total* days_window/diff_time,2) avg_exam_cnt
FROM t2
WHERE diff_time>1
ORDER BY days_window DESC,avg_exam_cnt DESC

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。

王林-wlin
关注
  • 0
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL窗口函数-聚合窗口函数
每天学习一点点
04-18 8014
关于窗口函数的基础，请看文章SQL窗口函数 许多常见的聚合函数也可以作为窗口函数使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函数。 案例分析 案例使用的示例表 下面的查询中会用到两个表，其中sales_monthly表中存储了不同产品（苹果、香蕉、橘子）每个月的销售额情况。以下是该表中的部分数据： transfer_log表中记录了一些银行账户的交易日志。以下是该表中的部分数据： 该表中的字段分别表示交易日志编号、交易时间、交易发起账户、交易接收.
Mysql8.0使用窗口函数解决排序问题
09-08
MySQL 8.0 的窗口函数极大地提高了数据分析的效率和灵活性，使得在不使用复杂子查询和变量的情况下，可以轻松处理排序和排名问题。了解并熟练掌握窗口函数是提升 SQL 查询能力的重要一步，对于数据分析师和数据库...
深入理解窗口函数及其应用
weixin_43977307的博客
06-17 730
窗口函数（Window Function）是一种在关系型数据库中执行聚合、分析和排序操作的高级工具。与常规的聚合函数（如SUM、AVG、COUNT等）不同，窗口函数允许在结果集中的每一行上进行计算，而不需要将数据分组。窗口函数提供了对每一行的子集（称为窗口）执行计算的能力，同时保留原始数据行的上下文信息。想象一家咖啡馆，里面有一堵特殊的墙，我们称之为“洞察之墙”。这堵墙背后有一个魔法窗户，这就是窗口函数。
32-视频33MySQL窗口函数
09-13
MySQL窗口函数是数据库查询中的一个重要概念，它在SQL语句中提供了一种处理分组数据的新方式，使得在不使用子查询或者自连接的情况下，也能实现复杂的数据分析和计算。在本节视频“32-视频33MySQL窗口函数”中，我们...
MySQL8之窗口函数实战&应用
06-29
由浅入深的将窗口函数相关知识讲解清楚，为了能够让读者轻松掌握窗口函数，本课程具备以下特点。 引入相关的数据表数据和项目需求，让读者感觉自己在实现真实的项目。淡化纯理论的概念，重在实践。知识点由浅入深，...
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
mysql-窗口函数-实验三.docx
06-15
实验报告的标题为“mysql-窗口函数-实验三”，表明本次实验主要关注的是MySQL数据库中的窗口函数使用。窗口函数在数据库查询中具有重要的作用，它们允许我们在一组相关的行（一个"窗口"）上执行计算，而不仅仅局限于...
【力扣】SQL题库练习5
qq_40878316的博客
07-25 1012
即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。返回结果格式如下例所示。
会Excel就会sql？
svygh123的专栏
07-20 1260
以上就是通过具体的例子来说明Excel中对应的操作，以便更好地对比SQL，通过这些步骤，你可以直观地在Excel中实现类似数据库操作的功能，SQL就像是数据库世界的Excel，但它更加强大，适用于大规模数据处理和复杂的数据关系管理。虽然开始时可能会觉得SQL语法有些抽象，但一旦掌握了基础，你会发现它非常高效且强大，特别是在处理大量数据和执行复杂查询时。就像在Excel中一样，实践中学习SQL会更加有效，尝试编写一些简单的查询，逐渐增加难度，你会很快上手的！
WEB渗透Web突破篇-SQL注入（MSSQL）
qq_59468567的博客
07-25 479
EXEC xp_cmdshell "net user"; EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:'; EXEC master.dbo.xp_cmdshell 'ping 127.0.0.1'; 激活xp_cmdshell(sql server 2015中默认禁用状态) EXEC sp_configure 'show advanced options',1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell',1; REC
CTF Web SQL注入 10000字详解
qq_27489877的博客
07-25 1276
SQL注入就是在数据交互中，前端数据传到后台时没有做严格的判断，导致传进来的数据被拼接到sql语句中，被当作sql语句的一部分进行执行，从而导致数据泄露，丢失甚至服务器瘫痪。对其中的特殊字符进行了转义，但在存入数据库时还是原来的数据，数据中一般带有单引号和＃号，然后下次使用在拼凑SQL中，所以就形成了二次注入。当原语句的闭合字符遇到转义字符时，会被转义，那么没有闭合符的语句就不完整了，导致报错，通过报错信息我们就可以推断出闭合符。攻击者必须闭合开放的字符串（使用引号），然后注入额外的 SQL 代码。
sqlalchemy报错sqlalchemy.orm.exc.DetachedInstanceError
ithongchou的博客
07-25 253
再次读取该对象属性时将重载这个对象，方法是重新调用之前的查询语句。的对象刷新机制，默认值为。在初始化数据库的代码中，将。
qgis 连接postgresql空间库，出现两张同名表问题修复
我哎GIS博客
07-24 160
qgis连接postgresql空间库，空间库表出现两张表名一样的表，但是navicat还是显示的一张表。分析后，原因是因为building表中，有一部分记录没有空间参考导致。重新设置库表的空间参考，使用st_setsrid函数。qgis数据刷新，检查下，问题解决。
学习SQL如何使用CASE语句查询分析设备状态
李长渊的博客
07-25 412
学习SQL如何使用CASE语句查询分析设备状态
Mybatis(四)特殊SQL的查询：模糊查询、批量删除、动态设置表明、添加功能获取自增的主键
m0_73864806的博客
07-26 396
不适用#{ }，’%?%‘ 问号是属于字符串的一部分 不会被解析成占位符，会被当作是我们字符串的一部分来解析，所以我们执行的语句中找不到占位符，但是我们却为占位符进行了赋值，所以说就会报错。#{ } 还是不使适用，会产生 ' ' ,动态设置表名的时候不可以带 ' '#{ } 在sql语句中被解析之后本身就会加上一个单引号 ' '我们使用${ } ，将#{}替换成${ }不可以使用#{} 需要使用${ }使用字符串拼接的方法。字符串 日期 字段名。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 312
图数据库查询与操作指南-以Nebula图数据库为例
ODBC连接达梦数据库
qq_55187735的博客
07-24 297
用户执行，因为环境变量配置在。
SQL基础入门：解锁数据库管理的钥匙
WayneYalejk的博客
07-26 460
在数字化时代，数据已成为企业最宝贵的资产之一。为了高效地存储、查询和管理这些数据，SQL（Structured Query Language，结构化查询语言）应运而生。SQL不仅是数据库管理员的必备技能，也是数据分析师、开发人员等多个领域从业者的重要工具。本文将带您踏入SQL的世界，从基础概念到实际应用，一步步解锁数据库管理的钥匙。强调SQL在数据处理和分析中的重要性，鼓励读者通过实践不断深化对SQL的理解和应用。
已经10岁的K8S，在很多方面仍需努力
最新发布
u012516914的专栏
07-28 245
/ K8S 已经 10 岁了，但仍然有许多方面要继续努力 /Kubernetes 于 2014 年 6 月推出，自那时起，它在推广云原生应用设计和支持更多微服务部署方面发挥了巨大作用。容器部署的增长非常迅速，而 Kubernetes 对于企业管理这些部署至关重要——根据 CNCF 上一份报告的调查结果，84% 的组织正在使用或评估 Kubernetes，而 66% 的潜在和实际消费者在生产中使用 ...
mysql里面的窗口函数用count举例
04-01
MySQL中的窗口函数是指在查询结果中对一组行进行计算的函数，而不是对单个行进行计算的函数。窗口函数可以用来执行聚合操作，例如计算每个分组中的平均值、总和、最大值、最小值等。 下面是一个使用COUNT函数作为窗口函数的示例： 假设我们有以下表格： ``` CREATE TABLE sales ( id INT PRIMARY KEY, product VARCHAR(50), sale_date DATE, amount DECIMAL(10,2) ); INSERT INTO sales VALUES (1, 'Product A', '2021-01-01', 100.00), (2, 'Product A', '2021-01-02', 200.00), (3, 'Product B', '2021-01-01', 150.00), (4, 'Product B', '2021-01-02', 250.00), (5, 'Product B', '2021-01-03', 300.00), (6, 'Product C', '2021-01-01', 50.00), (7, 'Product C', '2021-01-02', 70.00), (8, 'Product C', '2021-01-03', 90.00); ``` 我们想要计算每个产品在每个销售日期的销售额和该产品在每个销售日期的总销售额。我们可以使用窗口函数来计算总销售额。 以下是使用窗口函数计算每个产品在每个销售日期的销售额和总销售额的示例查询： ``` SELECT product, sale_date, amount, COUNT(*) OVER (PARTITION BY product, sale_date) AS row_count, SUM(amount) OVER (PARTITION BY product, sale_date) AS total_sales FROM sales; ``` 在这个查询中，我们使用了COUNT作为窗口函数来计算每个产品在每个销售日期的行数。我们使用PARTITION BY子句将结果分组为每个产品和销售日期的组。这样，我们就可以在查询结果中看到每个产品在每个销售日期的销售额和总销售额。 查询结果如下所示： ``` product sale_date amount row_count total_sales ------------------------------------------------------- Product A 2021-01-01 100.00 1 100.00 Product A 2021-01-02 200.00 1 200.00 Product B 2021-01-01 150.00 1 150.00 Product B 2021-01-02 250.00 1 250.00 Product B 2021-01-03 300.00 1 300.00 Product C 2021-01-01 50.00 1 50.00 Product C 2021-01-02 70.00 1 70.00 Product C 2021-01-03 90.00 1 90.00 ``` 在这个查询结果中，我们可以看到每个产品在每个销售日期的销售额和该产品在每个销售日期的总销售额。我们还可以看到每个分组中的行数，这是使用COUNT作为窗口函数计算的。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值