一、题意

今天这道题 打开了我对SQL的新的认知! 这道题参考了题解一个SQL大佬 即便是看了题解 完全掌握依旧有点难度 只是勉强 顺出来....

统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。

二、理论

在这道题中 运用到的函数有
percent_rank() 用于计算分区或结果集中行的百分位数。
dense_rank() 终返回连续的排名值(个人理解在统计连续月份作答等等会用到这个函数)
date_format()根据指定的日期格式格式化日期值

三、实战

1、各个用户对SQL试卷的未完成数、作答总数、未完成率如下

这个sql 并不是题解 但是有助于理解题解的SQL

SELECT
    uid,
    count(*),
    count(*)- count( score ) '未完成',
    count( score ) '已完成',
    (
    count(*) - count( score )) / count(*) 'incomplete_rate' 
FROM
    exam_record
    RIGHT JOIN examination_info ON exam_record.exam_id = examination_info.exam_id 
WHERE
    tag = 'SQL' 
GROUP BY
    uid 
ORDER BY
    uid

2、1001、1002、1003分别排在1.0、0.5、0.0的位置

这个sql 对应题中的提示 主要是排名的百分比

SELECT uid, percent_rank() over ( ORDER BY (( count(*) - count( score ) )/ count(*)) DESC 
) 
FROM
    exam_record
    RIGHT JOIN examination_info ON exam_record.exam_id = examination_info.exam_id 
WHERE
    tag = 'SQL' 
GROUP BY
    uid 
ORDER BY
    uid

3、连续作答月份

这个SQL有前面那道题的味了 都是找连续作答月份

SELECT
    A.uid,
    score,
    date_format( start_time, "%Y%m" ),
    dense_rank() over (
        PARTITION BY uid 
    ORDER BY
    date_format( start_time, "%Y%m" )) `percent_rank` 
FROM
    (
            2、(代码)
    ) A
    RIGHT JOIN user_info B ON A.uid = B.uid
    LEFT JOIN exam_record C ON B.uid = C.uid 
WHERE
    LEVEL > 5 
    AND `rank` <= 0.5

4、最终的完成试卷、答卷次数

SELECT
    uid,
    start_time start_month,
    count(*) total_cnt,
    count( score ) complete_cnt 
FROM
    (
    代码(3)
    ) D 
WHERE
    D.dense_rank < 4 
GROUP BY
    uid,
    start_time 
ORDER BY
    uid,
    start_time

四、细节注意

在使用百分比函数或者排名函数的时候 需要注意order by 里面也要desc