题解 | #未完成率较高的50%用户近三个月答卷情况#

思路是先筛选出限定条件的用户（SQL试卷、未完成率较高的50%用户），再分组排序找出用户近三个月的记录，最后输出要统计的指标。

一开始的代码（报错）。错在我想用limit语句，不想用窗口函数排序，筛选出未完成率较高的50%用户。limit语句后面只能跟常量。错误代码如下：

SELECT uid, DATE_FORMAT(start_time,'%Y%m') start_month, COUNT(start_time), COUNT(submit_time) 
FROM (SELECT uid, start_time, submit_time,
      dense_rank() over (PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) rk
      FROM exam_record
      WHERE uid IN (SELECT uid FROM 
              (SELECT uid, (COUNT(start_time)-COUNT(submit_time))/ COUNT(start_time) incomplete_rate
               FROM exam_record
               WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
               GROUP BY uid
               ORDER BY incomplete_rate DESC
               LIMIT ROUND((SELECT COUNT(DISTINCT uid) FROM exam_record) /2,0)
               ) t1)
      AND uid IN (SELECT uid FROM user_info WHERE level=7 OR level =6)
     ) t2
WHERE rk<=3
GROUP BY uid, start_month
ORDER BY uid, start_month

因此只能加一层窗口函数取代上面limit的做法。正确代码如下：

SELECT uid, DATE_FORMAT(start_time,'%Y%m') start_month, COUNT(start_time), COUNT(submit_time) 
FROM (SELECT uid, start_time, submit_time,
      dense_rank() over (PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) rk
      FROM exam_record
      WHERE uid IN (SELECT uid FROM
                    (SELECT uid, row_number() over (ORDER BY incomplete_rate DESC) rnrk FROM 
                     (SELECT uid, (COUNT(start_time)-COUNT(submit_time))/ COUNT(start_time) 
                      incomplete_rate
                      FROM exam_record
                      WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
                      GROUP BY uid) t1 ) t2
                    WHERE t2.rnrk<=CEIL((SELECT COUNT(DISTINCT uid) FROM exam_record) /2)
                    
                   )
      AND uid IN (SELECT uid FROM user_info WHERE level=7 OR level =6)
     ) t3
WHERE t3.rk<=3
GROUP BY uid, start_month
ORDER BY uid, start_month

我的总结是每每加一次窗口函数做排序并取出排名前几的记录，就得多一层select。

最后mark一下，ceil或者ceiling这个向上取整的函数。