mySQL中窗口函数的基本函数使用及举例

南瓜炖芒果

已于 2023-06-11 18:05:57 修改

阅读量490

点赞数 3

文章标签： sql mysql

于 2023-06-09 20:51:52 首次发布

本文链接：https://blog.csdn.net/m0_57372006/article/details/131134636

版权

4、例题（查询第二快慢用时之差大于试卷时长一半的试卷）

1、窗口函数与group by的区别

简单来说窗口函数对部分数据进行排序、计算等操作，groupby对一组值进行聚合，即窗口函数是每一行都会保留，groupby是从多行浓缩为少数行。

例如：一张超市商品表中，多种商品如笔记本、墨水属于文具类，锅、碗属于厨房类，

使用窗口函数可以对散乱的表进行排序并分别计算每种商品的销量，并保留每行

使用groupby则会统计文具类、厨房类的销量或单独为每种商品统计销量

2、窗口函数基本语法

[窗口函数] OVER(PARTITION BY 展示列 ORDER BY 排序列)

partition by 用于对行分为不同的窗口以便进行后续应用

3、窗口函数的主要三种应用

3.1排序函数（专用窗口函数）

排序函数用于对行进行排序

row_number() 计算出的序号是不具有重复值的如123456

rank（）排序行的排序值相同时，它们将会得到相同的排名，会根据重复的数跳过，如113446669

dense_rank（）排序行的排序值相同时，它们将会得到相同的排名，但不会跳过，如11223344567

3.2聚合函数

在窗口范围内进行聚合求值，如sum、max、min、count等。

3.3逐行函数

包括lead函数与lag函数

lead函数，返回排序后当前行后面的n行数的数据，可以用于计算行与行之间的差值或比率等。

如使用有顾客下单日期表，可对顾客下单日期进行排序后使用lead查看当前行下一行的日期，并用date_diff计算两个日期之间的间隔。

lag 函数则返回排序后当前行前面的n行数的数据

基本语法：

lead(a,1）返回a的后一列

lag(a,1)返回a的前一列

4、例题（查询第二快慢用时之差大于试卷时长一半的试卷）

下面是牛客网的一道窗口函数题目，地址如下

第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网 (nowcoder.com)

下面是代码及解释

select exam_id,duration,release_time
from 
    (select exam_id,duration,release_time,TIMESTAMPDIFF(minute,start_time,submit_time) as mm
    ,row_number() over(PARTITION BY exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time)) as m1
    ,row_number() over(PARTITION BY exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc) as m2
    from exam_record e left join examination_info ei using(exam_id)
    where submit_time is not null) t
where m1=2 or m2=2
group by exam_id 
having max(mm)-min(mm)>duration*0.5
order by exam_id desc

解释：

在子查询中，使用 TIMESTAMPDIFF(minute,start_time,submit_time) 计算每条记录的用时（以分钟为单位），
使用 ROW_NUMBER() 函数计算出当前记录在考试记录中的排名（第几短与第几长）,其中m1为正序排列，m2为倒序排列。
在主查询中，使用 WHERE 子句根据 m1 和 m2 的值，筛选出所有试卷考试时间排名为第二与倒数第二的记录。
使用group by聚合后，在 having 中，使用 max(mm) - MIN(mm) 筛选从上一步的筛选出的基础上，进一步得出每张试卷考试时间第二与倒数第二之差是否大于考试规定时长的一半。(因为group by后此时剩下的是每张试卷的第二与倒数第二时间记录)
最后使用order by排序输出