数据库系统原理与应用教程（069）—— MySQL 练习题：操作题 95-100（十三）：分组查询与聚合函数的使用

95、聚合函数的使用（1）

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

数据表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始答题时间，submit_time：交卷时间，score：得分），表中数据如下：

【问题】从 exam_record 数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。查询结果如下：

数据表：exam_record，表结构和数据如下：

/*
drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 50);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

解答：

/*
select 'SQL' tag, 'hard' difficulty,
round(avg(score),1) clip_avg_score from exam_record where exam_id in
(select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard')
and score <>
(select max(score) from exam_record where exam_id in
(select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'))
and score <>
(select min(score) from exam_record where exam_id in
(select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'));
*/

mysql> select 'SQL' tag, 'hard' difficulty,
    -> round(avg(score),1) clip_avg_score from exam_record where exam_id in
    -> (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard')
    -> and score <>
    -> (select max(score) from exam_record where exam_id in
    -> (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'))
    -> and score <>
    -> (select min(score) from exam_record where exam_id in
    -> (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'));
+-----+------------+----------------+
| tag | difficulty | clip_avg_score |
+-----+------------+----------------+
| SQL | hard       |           81.7 |
+-----+------------+----------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

96、聚合函数的使用（2）

数据表：exam_record 表（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间， score：得分），表中数据如下：

【问题】请统计出总答题次数：total_pv、试卷已完成答题数：complete_pv，已完成的试卷数：complete_exam_cnt。查询结果如下：

表结构和数据如下：

/*
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9001, '2021-09-06 12:01:01', null, null);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

解答：

/*
select count(*) total_pv,
       count(submit_time) complete_pv,
       count(distinct if(submit_time is not null, exam_id, null)) complete_exam_cnt
from exam_record;
*/
mysql> select count(*) total_pv,
    ->        count(submit_time) complete_pv,
    ->        count(distinct if(submit_time is not null, exam_id, null)) complete_exam_cnt
    -> from exam_record;
+----------+-------------+-------------------+
| total_pv | complete_pv | complete_exam_cnt |
+----------+-------------+-------------------+
|       11 |           7 |                 2 |
+----------+-------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

97、聚合函数的使用（3）

数据表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

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

【问题】请从试卷答题记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。查询结果如下：

表结构和数据如下：

/*
drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

解答：

/*
select min(score) min_score_over_avg from exam_record where exam_id in
    (select exam_id from examination_info where tag = 'SQL') and score >=
    (select avg(score) min_score_over_avg from exam_record where exam_id in
        (select exam_id from examination_info where tag = 'SQL'));
*/
mysql> select min(score) min_score_over_avg from exam_record where exam_id in
    ->     (select exam_id from examination_info where tag = 'SQL') and score >=
    ->     (select avg(score) min_score_over_avg from exam_record where exam_id in
    ->         (select exam_id from examination_info where tag = 'SQL'));
+--------------------+
| min_score_over_avg |
+--------------------+
|                 87 |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

98、分组查询（1）

数据表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始答题时间，submit_time：交卷时间，score：得分），表中数据如下：

【问题】请计算 2021 年每个月试卷答题用户的月度活跃天数 active_days 和月度活跃人数 mau。查询结果如下：

表结构和数据如下：

/*
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89),
(1007, 9002, '2020-09-02 12:11:01', '2020-09-02 12:31:01', 89);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

解答：

/*
select concat(left(start_time,4), substr(start_time, 6, 2)) month,
       count(distinct uid) active_days,
       count(distinct date(submit_time)) mau
from exam_record
where year(start_time) = 2021
group by month;
*/
mysql> select concat(left(start_time,4), substr(start_time, 6, 2)) month,
    ->        count(distinct uid) active_days,
    ->        count(distinct date(submit_time)) mau
    -> from exam_record
    -> where year(start_time) = 2021
    -> group by month;
+--------+-------------+-----+
| month  | active_days | mau |
+--------+-------------+-----+
| 202107 |           3 |   2 |
| 202109 |           5 |   4 |
+--------+-------------+-----+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

99、分组查询（2）

数据表：practice_record，表中数据如下：

【问题】请统计出 2021 年每个月用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt（按月份升序排序）。查询结果如下：

表结构和数据如下：

/*
drop table if exists practice_record;
CREATE TABLE  practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-08-01 19:38:01', 80);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

解答：

/*
select submit_month,
       count(*) month_q_cnt,
       round(count(*)/day(last_day(concat(submit_month,'01'))),3) avg_day_q_cnt
from
(select *,concat(left(submit_time,4),substr(submit_time,6,2)) submit_month
from practice_record
where year(submit_time) = 2021) a
group by submit_month
order by submit_month;
*/
mysql> select submit_month,
    ->        count(*) month_q_cnt,
    ->        round(count(*)/day(last_day(concat(submit_month,'01'))),3) avg_day_q_cnt
    -> from
    -> (select *,concat(left(submit_time,4),substr(submit_time,6,2)) submit_month
    -> from practice_record
    -> where year(submit_time) = 2021) a
    -> group by submit_month
    -> order by submit_month;
+--------------+-------------+---------------+
| submit_month | month_q_cnt | avg_day_q_cnt |
+--------------+-------------+---------------+
| 202108       |           2 |         0.065 |
| 202109       |           3 |         0.100 |
+--------------+-------------+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

100、分组查询（3）

数据表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

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

【问题】统计 2021 年每个未完成试卷作答数大于 1 的有效用户的数据（有效用户指完成试卷作答数至少为 1 且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序。查询结果如下：

表结构和数据如下：

/*
drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

解答：

/*
select e.uid, 
       sum(if(e.submit_time is null, 1, 0)) incomplete_cnt,
       sum(if(e.submit_time is null, 0, 1))complete_cnt,
       group_concat(concat(date(e.submit_time),':',if(e.submit_time is null, null, i.tag)) separator ';') detail
from exam_record e join examination_info i on e.exam_id = i.exam_id
where year(start_time) = 2021
group by e.uid 
having incomplete_cnt > 1 and incomplete_cnt < 5;
*/
mysql> select e.uid, 
    ->        sum(if(e.submit_time is null, 1, 0)) incomplete_cnt,
    ->        sum(if(e.submit_time is null, 0, 1))complete_cnt,
    ->        group_concat(concat(date(e.submit_time),':',if(e.submit_time is null, null, i.tag)) separator ';') detail
    -> from exam_record e join examination_info i on e.exam_id = i.exam_id
    -> where year(start_time) = 2021
    -> group by e.uid 
    -> having incomplete_cnt > 1 and incomplete_cnt < 5;
+------+----------------+--------------+--------------------------------------------------------+
| uid  | incomplete_cnt | complete_cnt | detail                                                 |
+------+----------------+--------------+--------------------------------------------------------+
| 1002 |           2 |       4 | 2021-07-02:SQL;2021-09-05:SQL;2021-07-05:SQL;2021-09-01:算法   |
+------+----------------+--------------+--------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24