地址

描述

现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分），示例数据如下：

id uid exam_id start_time submit_time score
1 1001 9001

| 2021-07-02 09:01:01

| (NULL)

| (NULL) | | 2 | 1002

| 9003

| 2021-09-01 12:01:01

| 2021-09-01 12:21:01

| 60 | | 3 | 1002

| 9002

| 2021-09-02 12:01:01

| 2021-09-02 12:31:01 | 70

| | 4 | 1002

| 9001

| 2021-09-05 19:01:01

| 2021-09-05 19:40:01

| 81

| | 5 | 1002

| 9002

| 2021-07-06 12:01:01

| (NULL)

| (NULL) | | 6 | 1003

| 9003

| 2021-09-07 10:01:01

| 2021-09-07 10:31:01 | 86 | | 7 | 1003

| 9003

| 2021-09-08 12:01:01

| 2021-09-08 12:11:01

| 40

| | 8 | 1003

| 9001

| 2021-09-08 13:01:01

| (NULL)

| (NULL) | | 9 | 1003

| 9002

| 2021-09-08 14:01:01

| (NULL)

| (NULL)

| | 10 | 1003

| 9003

| 2021-09-08 15:01:01

| (NULL)

| (NULL) | | 11 | 1005

| 9001

| 2021-09-01 12:01:01

| 2021-09-01 12:31:01

| 88 | | 12 | 1005

| 9002

| 2021-09-01 12:01:01

| 2021-09-01 12:31:01

| 88 | | 13 | 1005

| 9002

| 2021-09-02 12:11:01

| 2021-09-02 12:31:01

| 89

|

试卷信息表examination_info（exam_id：试卷ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

id exam_id tag difficulty **duration
 release_time
1 9001 SQL hard 60 2020-01-01 10:00:00

| | 2 | 9002 | C++ | easy

| 60

| 2020-02-01 10:00:00

| | 3 | 9003

| 算法

| medium

| 80

| 2020-08-02 10:00:00

|

请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：

tag tag_cnt
C++ 4
SQL 2
算法 1

解释：用户1002和1005在2021年09月的完成试卷数目均为3，其他用户均小于3；然后用户1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。

示例1

  1. drop table if exists examination_info,exam_record;
  2. CREATE TABLE examination_info (
  3.   id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  4.   exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
  5.   tag varchar(32) COMMENT '类别标签',
  6.   difficulty varchar(8) COMMENT '难度',
  7.   duration int NOT NULL COMMENT '时长',
  8.   release_time datetime COMMENT '发布时间'
  9. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  11. CREATE TABLE exam_record (
  12.   id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  13.   uid int NOT NULL COMMENT '用户ID',
  14.   exam_id int NOT NULL COMMENT '试卷ID',
  15.   start_time datetime NOT NULL COMMENT '开始时间',
  16.   submit_time datetime COMMENT '提交时间',
  17.   score tinyint COMMENT '得分'
  18. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  20. INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  21. (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  22. (9002, 'C++', 'easy', 60, '2020-02-01 10:00:00'),
  23. (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');
  25. INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
  26. (1001, 9001, '2021-07-02 09:01:01', null, null),
  27. (1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
  28. (1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
  29. (1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
  30. (1002, 9002, '2021-07-06 12:01:01', null, null),
  31. (1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
  32. (1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
  33. (1003, 9001, '2021-09-08 13:01:01', null, null),
  34. (1003, 9002, '2021-09-08 14:01:01', null, null),
  35. (1003, 9003, '2021-09-08 15:01:01', null, null),
  36. (1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
  37. (1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
  38. (1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);
  40. 输出：
  41. C++|4
  42. SQL|2
  43. 算法|1

解答

明确题意：

统计月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出

注意：月均完成试卷数不小于3，是 总完成次数/哪些月份提交了数据

  • 如共完成了12次，这10次的记录是在3，5，7这三个月里的；那月平均完成次数就是：12/3=4；

    问题拆解：

  • 筛选完成了的试卷的记录。知识点：where

  • 筛选月均完成数不小于3的用户。知识点：
    • 按用户分组group by uid；
    • 统计当前用户完成试卷总数count(exam_id)；
    • 统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, “%Y%m”))；
    • 分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, “%Y%m”)) >= 3；
  • 关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
  • 筛选满足条件的用户。知识点：where uid in (…)
  • 统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
  • 按次数降序输出。知识点：order by tag_cnt desc

标准答案：

  1. select tag, count(tag) as tag_cnt
  2. from exam_record
  3. join examination_info using(exam_id)
  4. where uid in (
  5.     select uid
  6.     from exam_record
  7.     where submit_time is not null 
  8.     group by uid
  9.     having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
  10. )
  11. group by tag
  12. order by tag_cnt desc

自己提交的答案：

  1. select examination_info.tag,count(exam_record.exam_id) as tag_cnt 
  2. from exam_record 
  3. left join examination_info 
  4. on exam_record.exam_id=examination_info.exam_id 
  5. where uid in(
  6.     select uid 
  7.     from exam_record  
  8.     where submit_time is not null 
  9.     group by uid 
  10.     # having count(exam_record.exam_id)>=3
  11.     HAVING COUNT(*) / COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m")) >= 3
  12. ) 
  14. group by  exam_record.exam_id 
  15. order by count(exam_record.exam_id) desc