网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
96、聚合函数的使用(2)
数据表:exam_record 表(uid:用户ID,exam_id:试卷ID,start_time:开始作答时间,submit_time:交卷时间, score:得分),表中数据如下:
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 | 80 |
2 | 1001 | 9001 | 2021-05-02 10:01:01 | 2021-05-02 10:30:01 | 81 |
3 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:31:01 | 84 |
4 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
5 | 1001 | 9001 | 2021-09-02 12:01:01 | (NULL) | (NULL) |
6 | 1001 | 9002 | 2021-09-01 12:01:01 | (NULL) | (NULL) |
7 | 1002 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
8 | 1002 | 9001 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 |
9 | 1003 | 9001 | 2021-02-06 12:01:01 | (NULL) | (NULL) |
10 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 88 |
11 | 1004 | 9001 | 2021-09-06 12:01:01 | (NULL) | (NULL) |
【问题】请统计出总答题次数:total_pv、试卷已完成答题数:complete_pv,已完成的试卷数:complete_exam_cnt。查询结果如下:
total_pv | complete_pv | complete_exam_cnt |
---|---|---|
11 | 7 | 2 |
表结构和数据如下:
/\*
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);
\*/
解答:
/\*
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)
97、聚合函数的使用(3)
数据表:exam_record(uid:用户ID,exam_id:试卷ID,start_time:开始作答时间,submit_time:交卷时间,score:得分),表中数据如下:
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 | 80 |
2 | 1002 | 9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
3 | 1002 | 9002 | 2021-09-02 12:01:01 | (NULL) | (NULL) |
4 | 1002 | 9003 | 2021-09-01 12:01:01 | (NULL) | (NULL) |
5 | 1002 | 9001 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
6 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 |
7 | 1003 | 9002 | 2021-02-06 12:01:01 | (NULL) | (NULL) |
8 | 1003 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 86 |
9 | 1004 | 9003 | 2021-09-06 12:01:01 | (NULL) | (NULL) |
数据表: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 | SQL | easy | 60 | 2020-02-01 10:00:00 |
3 | 9003 | 算法 | medium | 80 | 2020-08-02 10:00:00 |
【问题】请从试卷答题记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。查询结果如下:
min_score_over_avg |
---|
87 |
表结构和数据如下:
/\*
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);
\*/
解答:
/\*
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)
98、分组查询(1)
数据表:exam_record(uid:用户ID,exam_id:试卷ID,start_time:开始答题时间,submit_time:交卷时间,score:得分),表中数据如下:
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1001 | 9001 | 2021-07-02 09:01:01 | 2021-07-02 09:21:01 | 80 |
2 | 1002 | 9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 81 |
3 | 1002 | 9002 | 2021-09-02 12:01:01 | (NULL) | (NULL) |
4 | 1002 | 9003 | 2021-09-01 12:01:01 | (NULL) | (NULL) |
5 | 1002 | 9001 | 2021-07-02 19:01:01 | 2021-07-02 19:30:01 | 82 |
6 | 1002 | 9002 | 2021-07-05 18:01:01 | 2021-07-05 18:59:02 | 90 |
7 | 1003 | 9002 | 2021-07-06 12:01:01 | (NULL) | (NULL) |
8 | 1003 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 86 |
9 | 1004 | 9003 | 2021-09-06 12:01:01 | (NULL) | (NULL) |
10 | 1002 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
11 | 1005 | 9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 88 |
12 | 1006 | 9002 | 2021-09-02 12:11:01 | 2021-09-02 12:31:01 | 89 |
13 | 1007 | 9002 | 2020-09-02 12:11:01 | 2020-09-02 12:31:01 | 89 |
【问题】请计算 2021 年每个月试卷答题用户的月度活跃天数 active_days 和月度活跃人数 mau。查询结果如下:
month | active_days | mau |
---|---|---|
202107 | 3 | 2 |
202109 | 5 | 4 |
表结构和数据如下:
/\*
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);
\*/
解答:
/\*
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)
99、分组查询(2)
数据表:practice_record,表中数据如下:
id | uid | question_id | submit_time | score |
---|---|---|---|---|
1 | 1001 | 8001 | 2021-08-02 11:41:01 | 60 |
2 | 1002 | 8001 | 2021-09-02 19:30:01 | 50 |
3 | 1002 | 8001 | 2021-09-02 19:20:01 | 70 |
4 | 1002 | 8002 | 2021-09-02 19:38:01 | 70 |
5 | 1003 | 8002 | 2021-08-01 19:38:01 | 80 |
【问题】请统计出 2021 年每个月用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt(按月份升序排序)。查询结果如下:
submit_month | month_q_cnt | avg_day_q_cnt |
---|---|---|
202108 | 2 | 0.065 |
202109 | 3 | 0.100 |
表结构和数据如下:
/\*
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);
\*/
解答:
/\*
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)
100、分组查询(3)
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
------------±--------------+
2 rows in set (0.00 sec)
100、分组查询(3)
[外链图片转存中...(img-xp8uWg7d-1715808925534)]
[外链图片转存中...(img-Df2sh7FK-1715808925534)]
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化的资料的朋友,可以添加戳这里获取](https://bbs.csdn.net/topics/618668825)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**