函数

• date_format(start_time, '%Y%m'): 可以取出来年份和月份进行排序

坑

看了下题解下的评论

1. 近三个有记录的月份，用户只有1个月和2个月的记录也应该算的。

解题步骤

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名

方法一：这是一开始自己的方法，总之就是复杂了

1 找到近三个有试卷作答记录的月份

详细：开窗，按uid分区，year,month排序，取最新的年份和最新的三个月的月份，要统计这三个月所有的试卷，用dense_rank ，这个表作为t表

SELECT 
uid,
start_time,
submit_time,
DENSE_RANK() OVER(PARTITION BY uid ORDER BY YEAR(start_time) DESC, MONTH(start_time) DESC) AS rk
FROM 
exam_record

2 取前三位

SELECT
  uid,
  start_time,
  submit_time
FROM
  t
WHERE
  t.rk <= 3

3 把submit_time is null 的uid过滤掉,计算每个账号近三个有试卷记录的记录数，计算每个账号近三个有试卷记录的记录数

详细：

• 筛选出submit_time is null的uid

SELECT DISTINCT uid FROM t
    WHERE
      t.rk <= 3
      AND t.submit_time IS NULL

• 按uid not in (submit_time is null的uid)过滤

SELECT
  uid,
  COUNT(*) AS exam_complete_cnt
FROM
   t
WHERE
  t.rk <= 3
  AND uid NOT IN (
    SELECT DISTINCT uid FROM t
    WHERE
      t.rk <= 3
      AND t.submit_time IS NULL
  )
GROUP BY
  uid
ORDER BY
  uid,
  exam_complete_cnt

4 拼接

SELECT
  uid,
  COUNT(*) AS exam_complete_cnt
FROM
  (
    SELECT
      uid,
      start_time,
      submit_time,
      DENSE_RANK() OVER(
        PARTITION BY uid
        ORDER BY
          YEAR(start_time) DESC,
          MONTH(start_time) DESC
      ) AS rk
    FROM
      exam_record
  ) t
WHERE
  t.rk <= 3
  AND uid NOT IN (
    SELECT
      DISTINCT uid
    FROM
      (
        SELECT
          uid,
          start_time,
          submit_time,
          DENSE_RANK() OVER(
            PARTITION BY uid
            ORDER BY
              YEAR(start_time) DESC,
              MONTH(start_time) DESC
          ) AS rk
        FROM
          exam_record
      ) t
    WHERE
      t.rk <= 3
      AND t.submit_time IS NULL
  )
GROUP BY
  uid
ORDER BY
  exam_complete_cnt desc,
  uid desc

优化

第1步：优化1：可以用date_format函数：date_format(start_time, '%Y%m') 直接取出来年份和月份进行排序，不用用YEAR(start_time) DESC, MONTH(start_time) DESC排两次

SELECT
  uid,
  COUNT(*) AS exam_complete_cnt
FROM
  (
    SELECT
      uid,
      start_time,
      submit_time,
      DENSE_RANK() OVER(
        PARTITION BY uid
        ORDER BY
          DATE_FORMAT(start_time, '%Y%m') desc
      ) AS rk
    FROM
      exam_record
  ) t
WHERE
  t.rk <= 3
  AND uid NOT IN (
    SELECT
      DISTINCT uid
    FROM
      (
        SELECT
          uid,
          start_time,
          submit_time,
          DENSE_RANK() OVER(
            PARTITION BY uid
            ORDER BY
              YEAR(start_time) DESC,
              MONTH(start_time) DESC
          ) AS rk
        FROM
          exam_record
      ) t
    WHERE
      t.rk <= 3
      AND t.submit_time IS NULL
  )
GROUP BY
  uid
ORDER BY
  exam_complete_cnt desc,
  uid desc

第3步：优化2：不用这么像第3步那么麻烦，直接筛选出count(submit_time) = count(start_time)，就是近三个有记录的月份未提交试卷数为0的用户

优化后代码：

SELECT 
uid,
COUNT(submit_time) AS exam_complete_cnt
FROM(
SELECT 
uid,
start_time,
submit_time,
DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS rk
FROM 
exam_record) t
WHERE rk <= 3
GROUP BY uid
HAVING COUNT(start_time) = COUNT(submit_time) #筛选出近三个有卷子记录的月份未完成试卷数为0的记录
ORDER BY
  exam_complete_cnt desc,
  uid desc