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

题目复盘——窗口函数2

复盘两个难题

SQL31 未完成率较高的50%用户近三个月答卷情况

问题分析

（1）SQL试卷上未完成率较高的50%用户——
未完成率：1-count(submit_time)/count(start_time) incomplete_rate from exam_record e_r left join examination_info e_i on e_r.exam_id=e_i.exam_id where tag=‘SQL’;
窗口函数排序：percent_rank()over(order by 未完成率 desc) ranking1;
较高的50%用户：ranking1<=0.5;
（2）6级和7级用户——在（1）筛选后的基础上（或者同时在where里用and并列筛选）left join user_info u_i on …where level in (6,7)或者子查询where uid in(select uid from user_info where level in (6,7));
（3）有试卷作答记录的近三个月——
窗口函数排序：dense_rank()over(partition by uid order by date_format(start_time,’%Y%m’) desc) ranking2
筛选近三个月：ranking2<=3;
（4）每个月答卷数目和完成数目——count(start_time) total_cnt;count(submit_time) complete_cnt group by uid,start_month;
（5）用户ID、月份升序排列——order by uid asc,start_month asc;

答案重写

SELECT uid,start_month,total_cnt,complete_cnt
FROM
(SELECT e_r.uid uid,date_format(start_time,'%Y%m') start_month,count(start_time) total_cnt,count(submit_time) complete_cnt,
dense_rank()over(partition by uid order by date_format(start_time,'%Y%m')) ranking2
FROM exam_record e_r LEFT JOIN user_info u_i ON e_r.uid=u_i.uid
WHERE e_r.uid in
(SELECT uid FROM
(SELECT e_r.uid,
percent_rank()over(order by 1-count(submit_time)/count(start_time) DESC) ranking1
FROM exam_record e_r LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE tag='SQL'
GROUP BY uid) t1
WHERE ranking1<=0.5)
AND level in (6,7) AND score IS NOT NULL
GROUP BY uid,start_month) t2
WHERE ranking2<=3
ORDER BY uid,start_month;

细节问题

（1）percent_rank()函数partition by与group by问题
在一开始解题的过程中，我按照以往的解题惯例在窗口函数的over()中写上partition by并同时在下面写了group by语句,然后提交时报错，自认为逻辑没有问题，就是找不出错误，各方面都考虑了，最后在评论区找到了问题所在：
percent_rank()函数对已分组的表不用再次分组
只要把percent_rank()over()函数里over()中的partition by uid删了就提交结果正确了。
为什么呢？百度了一下也没有一个专门的权威一点的结果,dense_rank也出现了一次这样的问题，但是再提交又好了，应该与percent_rank()不太一样，属于按照什么分组，也不太明白机理是什么，只能先就这样记住了。
（2）复习一下运算顺序，窗口函数是最后运算的。

（3）join表之后对于类似select uid这种两个表都有的字段，记得加前缀指明具体的表，避免字段模糊报错。

SQL32 试卷完成数同比2020年的增长率及排名变化

问题分析

（1）2021年上半年、2020年上半年做完次数——
日期限制：date_format(submit_time,’%Y%m’) 用between 202101 and 202106 筛选（2020同理）；
次数：count(submit_time) exam_cnt_21（2020同理）;
（2）21年与20年可以分别select两个表出来，再用tag作为相同列join起来，根据解释里的描述，应该用inner join；
（3）同期增长率——
观察growth_rate保留一位小数用round，写成百分号形式用concat连接；concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),’%’) growth_rate
（4）做完次数排名——
rank()over(order by count(score) desc) exam_cnt_rank_21（20同理）
（5）排名变化——这里有个坑就是负数问题
一开始只想到了用绝对值函数abs()，在评论区大神学到了数据类型转化cast；cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) rank_delta；

答案重写

SELECT tag,exam_cnt_20,exam_cnt_21,
concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),'%') growth_rate,
exam_cnt_rank_20,exam_cnt_rank_21,
cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) rank_delta
FROM 
(SELECT tag,count(score) exam_cnt_21,
rank()over(order by count(score) desc) exam_cnt_rank_21
FROM exam_record e_r LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE date_format(submit_time,'%Y%m') BETWEEN 202101 AND 202106
GROUP BY tag) t1
INNER JOIN
(SELECT tag,count(score) exam_cnt_20,
rank()over(order by count(score) desc) exam_cnt_rank_20
FROM exam_record e_r LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE date_format(submit_time,'%Y%m') BETWEEN 202001 AND 202006
GROUP BY tag) t2
ON t1.tag=t2.tag
GROUP BY tag
WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC;

细节问题

（1）partition by问题又出现了！
21年排序中rank()over(order by count(score) desc)不可以用partition by tag，下面已经按照tag分组了group by tag，如果加了partition by会出现下图错误（语句没错能运行但是答案不对）

评论区看到一个与我类似的答案也只是备注了一下这里是不能用partition的, 因为是根据分组之后的tag进行的,做到现在虽然仍然感到模糊，但形成了一种做题的感觉，就是似乎能感觉到哪里应该用哪里不应该用，能做对但是总结不出具体的经验。

（2）因为mysql本身特殊原因，如果结果出现负数可能会报错，一般数据库默认都是unsigned, 是不能出现负数的, 可用cast(字段 as signed)即可；
（3）关于非空的条件限制，写的时候总会落下类似score is not null这种限制因为感觉count(字段)不会把null计算上，但是有时候join完可能会保留下无用的等于0的情况，所以加一个本题WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0筛选有记录的情况，虽然不加答案也通过检验了，但还是加上更严谨。