MYSQL学习笔记（3）——窗口函数

题目复盘——窗口函数

题源牛客网——记录一下自己的学习

部分窗口函数知识复盘

窗口函数大全补充[http://t.csdn.cn/xJGxx]
窗口函数只能用在SELECT后

1.专用窗口函数

rank、dense_rank、row_number

①rank、dense_rank

rank：并列的项名词相同，且按照个数跳过后面的名次。e:1、2、3、3、5

 rank () over (partition by 分组字段名
	            order by 排序字段名
	             rows between 1 preceding and 1 following)

rows（行）、preceding（之前）、following（之后）指定几条记录为一个框架，可以用于计算移动平均值
dense_rank：并列的项名词相同，不跳过后面的名次。e:1、2、3、3、4

②row_number

生成唯一的连续排序：1、2、3、4、5
窗口函数无需参数，因此通常括号里都是空的。

2.能作为窗口函数的聚合函数

sum，avg，count，max，min
这里括号里就不是空的了，需要添加计算的字段。

 sum(计算字段) over (order by 排序字段) as current_sum,
   avg(计算字段) over (order by 排序字段) as current_avg,
   count(计算字段) over (order by 排序字段) as current_count

sum——从上到下累加效果
avg——从上到下平均值
count——累计几个了
max——累计到此最大的数
min——累计到此最小的数

这篇总结的很好：http://t.csdn.cn/LKa7e

SQL27 每类试卷得分前3名

问题分析

（1）有新的ranking列——窗口函数——rank()over(partition by order by)；
（2）每类试卷得分前三名——得分前三名的人，不是找top3分数；
（3）三个排序条件——max(score),min(score)，uid ；
（4）每一类每个人的max和min——group by tag,uid；

答案重写

SELECT tid,uid,ranking FROM
(SELECT e_i.tag tid,e_r.uid uid,rank()over(partition by e_i.tag
                           order by max(score) desc,min(score) desc,e_r.uid desc) ranking
FROM exam_record e_r 
LEFT JOIN examination_into e_i ON e_r.exam_id=e_i.exam_id
GROUP BY tag,e_r.uid) ranktab
WHERE ranking<=3;

细节问题

（1）提示错误信息：
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘school.student.s_id’; this is incompatible with sql_mode=only_full_group_by

当mysql的sql_mode是only_full_group_by的时候，在不使用group by
并且select后面出现聚集函数的话，那么所有被select的都应该是聚集函数，否则就会报错

这种情况最好子查询里SELECT出字段，这样不用GROUP BY也不会报错。
（2）GROUP BY和窗口函数同时使用

“如果查询包含任何窗口函数，则在进行任何分组，聚合和HAVING过滤后，将对这些函数进行评估 ”——运行顺序依次是：group
by、having、聚合函数、窗口函数。
https://www.it1352.com/1764380.html

以下源自 牛客id金鱼游了过去 评论：
【group by 字段A,B 】发挥分组作用的时机是 运行聚合函数max(字段C)) 时。即group by字段A,B所形成的一个个组，就是max()所计算的对象。
【partition by 字段A】发挥分组作用的时机是 运行order by时。即partition by字段A所形成的一个个组，就是order by排序比较时所框定的范围。
补充解释：也就是三个排序条件，需要在试卷tag的分组下进行，所以partition by tag。
group by和partition by参考http://t.csdn.cn/0OGg5

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

问题分析

（1）第二快和第二慢——窗口函数两个排名一个正序一个倒序；
（2）用时之差大于试卷时长一半——这里的坑之前出现过一次，duration给的是分钟，但算timestampdiff时要用秒为单位，否则不准确会有用例不通过的情况；
（3）submit_time里有NULL值，要在rank那一层就筛选掉；

答案重写

SELECT exam_id,duration,release_time FROM
(SELECT exam_id,duration,release_time,
SUM(case when rank2=2 then time_s 
         when rank1=2 then -time_s else 0 end) time_c
FROM (SELECT e_r.exam_id exam_id,duration,release_time,
TIMESTAMPDIFF(second,start_time,submit_time) time_s,
row_number()over(partition by e_r.exam_id order by TIMESTAMPDIFF(second,start_time,submit_time) asc) rank1,
row_number()over(partition by e_r.exam_id order by TIMESTAMPDIFF(second,start_time,submit_time) desc) rank2
FROM exam_record e_r LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE submit_time IS NOT NULL) tab_one
GROUP BY exam_id) tab_two
WHERE time_c>duration*60/2
ORDER BY exam_id DESC;

评论区大神写法：

select distinct c.exam_id,duration,release_time from 
(select a.exam_id, 
nth_value(TIMESTAMPDIFF(minute,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as low_2,
nth_value(TIMESTAMPDIFF(minute,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) asc) as fast_2,
duration,release_time
from exam_record a left join examination_info b on a.exam_id = b.exam_id) c 
where low_2-fast_2>duration*0.5
order by exam_id desc;

NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT |IGNORE} NULLS ] OVER (analytic_clause)

返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。返回的值具有 measure_expr 的数据类型

http://t.csdn.cn/4h6ER

细节问题

（1）left join 后要注意可能存在的重复情况，加distionct；
（2）选出的第二和倒数第二两个数，可以通过外面包一层max,min聚合函数进行新的命名，从而可以直接在后面的条件中相减，来自评论区的做法，可谓是非常的妙；

SQL29 连续两次作答试卷的最大时间窗

问题分析

（1）符合条件的数据：
2021年——year(start_time)=2021;
（2）至少有两天作答过——count(distinct date(submit_time))>=2,group by uid;要注意使用group by的时候我们的select后面是否还要提取不能组合并的字段，小心报错，所以两个条件筛选不一定在同一层写；
（3）连续两次试卷作答的时间窗——同一字段相邻的时间相减——start_time从小到大排列，用lead(start_time,1)over把后一个时间提出新的一列next_start_time;
（4）这里时间窗以天为单位——datediff(next_start_time,start_time)+1 加一天才是真正的时间窗；
（5）平均作答试卷套数——示例中给的算法是（最大时间窗里做的试卷套数/最大时间窗）*最大连续两次试卷作答时间窗
最大时间窗=datediff(max(start_time),min(start_time))+1
（6）保留两位小数——round(,2)
（7）写到这里就可以发现，至少有两天作答过可以转化为最大的时间窗>1，只有在一天内做多次或者没做。时间窗才会是1；
（8）排序倒序——ORDER BY days_window DESC,avg_exam_cnt DESC;

答案重写

SELECT uid,days_window,
round((exam_num/max_window)*days_window,2) avg_exam_cnt
FROM
(SELECT uid,
max(datediff(next_start_time,start_time)+1) days_window,
datediff(max(start_time),min(start_time))+1 max_window,
count(start_time) exam_num
FROM
(SELECT uid,start_time,
lead(start_time,1)over(partition by uid order by start_time asc) next_start_time 
FROM exam_record
WHERE year(start_time)=2021) table_one
GROUP BY uid
HAVING max_window>1) table_two
ORDER BY days_window DESC,avg_exam_cnt DESC;

细节问题

（1）为什么group by后面不能使用列的别名（
🤷‍♂️why mysql可以:

在mysql中，group by中可以使用别名；where中不能使用别名；order by中可以使用别名。其余像oracle，hive中别名的使用都是严格遵循sql执行顺序的，group by后面不能用别名。mysql特殊是因为mysql中对查询做了加强。

http://t.csdn.cn/WoOht
（2）窗口函数大全补充http://t.csdn.cn/xJGxx

SQL30 近三个月未完成试卷数为0的用户完成情况

问题分析

（1）每个人近三个有作答记录的月——提取年月date_format(start_time,’%Y%m’)因为月份可能会有重复记录，所以采用并列但不跳过排名的dens_rank()over(partition by uid order by date_format(start_time,’%Y%m’) DESC)降序排列前三名为最近三个月份；
（2）筛选出的三个月中没有试卷是未完成状态的用户——也就是score和submit_time不存在null的情况——但是这里有个坑🐷
（3）试卷作答完成数——count(submit_time)
（4）排序——order by exam_complete_cnt desc,uid desc;

答案重写

SELECT uid,count(submit_time) exam_complete_cnt
FROM
(SELECT uid,start_time,submit_time,date_format(start_time,'%Y%m') year_m,
dens_rank()over(partition by uid order by date_format(start_time,'%Y%m') DESC) ranking
FROM exam_record) table_one
WHERE ranking<=3
GROUP BY uid
HAVING count(uid)=count(submit_time)
ORDER BY exam_complete_cnt DESC,uid DESC;

直观一点也可以用这个表达COUNT(start_time) = COUNT(submit_time)，分组之后，只保留组内两个count相等的uid，妙啊！

细节问题

（1）关于未完成状态的筛选，首先不能在选出三个月前去除，因为那时候还有别的月份的未完成记录，如果在筛选出三个月后用WHERE submit_time is not null显然不对，并没有把其完成状态的uid全部去除，如果用uid in或者is not in就会用到子查询套起来特别麻烦，我试着写的时候就是很麻烦不太行，不知道有没有简单的方法，上面写的那种是看到的最简单的；