8.5 SQL练习题汇总（二）

原创 已于 2024-04-14 18:10:08 修改 · 1.3k 阅读 · 10 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#sql #数据库 #mysql
于 2023-03-06 22:21:27 首次发布
文章详细介绍了数据库的增删改查操作、表结构优化、索引策略、复杂查询技巧以及数据分析方法，聚焦于提升数据处理效率与用户体验，特别是针对牛客平台的用户与试卷记录进行了深入探讨。

文章目录

1.增删改操作

后续案例需要执行的脚本SQL：

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS 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;
CREATE TABLE IF NOT EXISTS exam_record_before_2021 (
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;
TRUNCATE exam_record;
CREATE TABLE IF NOT EXISTS exam_record_2010 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2012 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2013 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2014 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2015 (LIKE exam_record); 
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:00:01', null, null),
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(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, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 70),
(1001, 9002, '2020-09-02 09:00:01', null, null),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1002, 9001, '2021-08-02 19:01:01', null, null),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1003, 9001, '2021-09-02 12:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(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),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:06', 89),
(1003, 9002, '2021-09-01 12:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null),
(1004, 9001, '2021-09-06 12:01:01', null, null);


CREATE TABLE IF NOT EXISTS exam_record_before_2021 (
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;
TRUNCATE exam_record_before_2021;



drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS 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_bin;
TRUNCATE examination_info;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
(9002, '算法', 'easy', 60, '2020-02-01 10:00:00'),
(9003, 'SQL', 'medium', 60, '2020-08-02 10:00:00'),
(9004, '算法', 'hard', 80, '2020-01-01 10:00:00'),
(9005, 'python', 'easy', 60, '2020-01-01 10:00:00'),
(9006, 'Python', 'medium', 80, '2020-01-01 10:00:00'),
(9007, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00');



drop table if exists user_info;
CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE user_info;
INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
(1001,'学习1号',3100,7,'算法','2020-01-01 10:00:00'),
(1002,'学习2号',2100,6,'算法','2020-01-01 10:00:00'),
(1003,'学习3号',1500,5,'算法','2020-01-01 10:00:00'),
(1004,'学习4号',1100,4,'算法','2020-01-01 10:00:00'),
(1005,'学习5号',1600,6,'C++','2020-01-01 10:00:00'),
(1006,'学习6号',3000,6,'C++','2020-01-01 10:00:00');




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.1 插入记录

1.1.1 学习中心后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下：

  • 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
  • 用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。

试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。

在这里插入图片描述

通过执行SELECT uid, exam_id, start_time, submit_time, score FROM exam_record WHERE start_time = '2021-09-01 22:11:12' OR start_time = '2021-09-04 07:01:02';语句来对比结果

正确的输出结果：
在这里插入图片描述

输入：

INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

分析：插入的表格存在自增主键，这里的id是自增主键（PRIMARY KEY)，这就意味着不需要你自己手动填入，它会跟随表格行数进行自己增加（比如这样增加id值👉1，2，3…n）。
所以我们在插入数据的时候，可以指定插入的列名， 这样就不用填写id这一列的数据，让他自增。

另外我们还可以把id的值设置为NULL或0，这样MySQL会自己处理这个自增的id列。 具体代码如下：

INSERT INTO exam_record VALUES
(NULL, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(NULL, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

其实可以直接填入id值，不过仅针对插入数据不多的时候使用，大家了解一下：

INSERT INTO exam_record VALUES
(1, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(2, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

1.1.2 现有一张试卷作答记录表exam_record，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。

在这里插入图片描述

我们已经创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。

通过执行SELECT * FROM exam_record_before_2021;语句来对比结果

正确的输出结果：
在这里插入图片描述

输入：

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

分析：
插入记录的方式汇总：

  • 普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
  • 普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
  • 多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
  • 从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

注意上面的WHERE条件简化了细节剖析里的后两个条件，用submit_time来判断可以隐含的保证已完成这个条件。如果用start_time判断可能会出错，导入未完成的作答记录。

1.1.3 现在有一套ID为9003的容易难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。

试题信息表examination_info：
在这里插入图片描述

通过执行SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info;语句来对比结果

正确的输出结果：
在这里插入图片描述

输入：

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','medium', 90, '2021-01-01 00:00:00')

分析：题目要求不论是否存在数据都要成功插入，但是对于主键值，我们必须保持唯一，所以可以先查询其是否存在，如果存在，那么删除后再插入数据。

也可以使用方法二插入：

REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','medium',90,'2021-01-01 00:00:00');

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

  • 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
  • 否则，直接插入新数据。
    要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

其中，关键字NULL可以用DEFAULT替代。关键是要掌握replace into···values的用法。

1.2 更新记录

1.2.1 现有一张试卷信息表examination_info，表结构如下图所示：

在这里插入图片描述
请把examination_info表中tag为PYTHON的tag字段全部修改为Python。

通过执行SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info;语句来对比结果。

正确结果为：
在这里插入图片描述

分析：

  • 本题采用两种修改方式均可，语义为『当tag为PYTHON时，修改tag为Python』，先用第一种:

    UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";

  • 如果采用第二种，写作：

    UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";

  • 思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：

    UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag LIKE "%PYTHON%";

1.2.2 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：submit_time为 完成时间
在这里插入图片描述
请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为’2099-01-01 00:00:00’，分数改为0。

通过执行SELECT uid,exam_id,start_time,submit_time,score FROM exam_record ORDER BY start_time;
'语句来对比结果。

正确结果为：
在这里插入图片描述

分析：
题目要求只改2021年9月1日之前开始作答的记录并且未完成的记录；将符合条件的记录改为被动完成：完成时间改为’2099-01-01 00:00:00’，分数改为0；所以我们只需要将符合条件的字段值更新即可：

UPDATE exam_record
SET submit_time='2099-01-01 00:00:00', score=0
WHERE start_time < '2021-09-01 00:00:00' AND score IS NULL;

1.3 删除记录

1.3.1 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：start_time是试卷开始时间；submit_time 是交卷，即结束时间。

在这里插入图片描述

请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；

通过执行 SELECT * FROM exam_record; 语句来对比结果。

正确结果为：
在这里插入图片描述

解释：删除了原数据中第10行及第19行。

分析：
题目要求删除作答时间小于5分钟整即提交时间-开始时间小于5分钟，并且分数不及格即分数小于60分的记录。

所以我们只需要删除同时满足俩个条件的行即可：

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
    AND score < 60;

其中timestampdiff 关键字查询两个时间以第一参数为单位的差。

1.3.2 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：start_time是试卷开始时间；submit_time 是交卷时间，即结束时间，如果未完成的话，则为空 。
在这里插入图片描述

请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。

通过执行 SELECT * FROM exam_record 语句来对比结果。

正确结果为：
在这里插入图片描述

分析：
题目要求只要满足下列条件之一就删除其所在的行：

  • 未完成作答的记录；
  • 作答时间小于5分钟整的记录中，开始作答时间最早的3条记录；

所以其SQL语句应该为：

DELETE FROM exam_record
WHERE (submit_time IS NULL OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5)
ORDER BY start_time ASC
LIMIT 3;

1.3.3 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

在这里插入图片描述
请删除exam_record表中所有记录，并重置自增主键。

通过执行 SELECT table_rows, auto_increment FROM information_schema.tables WHERE table_name='exam_record'语句来对比输出结果

正确结果：
在这里插入图片描述

分析：
删除记录的方式汇总：

  • 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
  • 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

本案例要求删除全部记录，以及重置主键自增的值，最简单的就是使用方式二进行删除：

TRUNCATE TABLE  exam_record;

也可采用第一种，不过需要手动重置自增ID，不过效率角度考虑，还是第二种方式效率更高：

DELETE FROM exam_record;
ALTER TABLE exam_record auto_increment=1;

2.表与索引操作

2.1 表的创建修改与删除

2.1.1 现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。

原来的用户信息表：
在这里插入图片描述

作为数据分析师，请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。

你应该返回的输出如下表格所示，请写出建表语句将表格中所有限制和说明记录到表里。
在这里插入图片描述

通过执行 SHOW FULL FIELDS FROM user_info_vip 语句，来对比输出结果；

正确的结果为：
在这里插入图片描述

分析：
创建表的方式有如下几种：

  1. 直接创建表：
    CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）
  2. 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old
  3. 从另一张表的查询结果创建表：CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

虽然案例中介绍，要根据此前存在的用户信息表创建新的表，但其表结构并不完全相同，所以无法直接复制创建，我们可以选择复制创建后，通过修改不一样的属性来完成，我们这里选择手动创建：

CREATE TABLE IF NOT EXISTS user_info_vip (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT UNIQUE NOT NULL COMMENT '用户ID',
    nick_name VARCHAR(64) COMMENT '昵称',
    achievement INT DEFAULT 0 COMMENT '成就值',
    `level` INT COMMENT '用户等级',
    job VARCHAR(32) COMMENT '职业方向',
    register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

具体含义：
第一行 create table user_info_vip 建立表格名字为user_info_vip （里面的内容 建立列id 数据类型：整型(11) int11 非空not null 主键 primary key 序号递增 auto_increment 备注comment’‘， 建立列uid 数据类型：整型(11) int11 非空not null 限定字符 unique key 备注comment’‘, 建立列nick_name 数据类型:字符串(64) varchar(64) 备注comment’‘, 建立列 achievement 数据类型：整型(11) 默认值为0 default 0 备注comment, 建立列 level 数据类型：整型(11) 备注:comment’‘, 建立列 job 数据类型:字符串(32) 备注:comment’‘, 建立列 register_time 数据类型:时间 datetime 默认值default CURRENT_TIMESTAM 备注comment’’ ）默认编码 charset=utf8

2.1.2 现有一张用户信息表user_info，其中包含多年来在平台注册过的用户信息。

在这里插入图片描述

请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，同时varchar字段长度变为10；achievement的默认值设置为0。

输出结果示例：
在这里插入图片描述

正确输出结果：
在这里插入图片描述

通过执行 SHOW FULL FIELDS FROM user_info 来对比输出结果；

分析：
表修改的相关操作：

  1. 添加字段alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
  2. 更换列的名称及数据类型alter table user_info change 原列名 修改列名 修改数据类型
  3. 更改数据类型alter table 表名 modify 修改列名称 数据类型 默认值等

所以最终的SQL为：

ALTER TABLE user_info ADD school VARCHAR(15) AFTER `level`;
ALTER TABLE user_info CHANGE job profession VARCHAR(10);
ALTER TABLE user_info CHANGE COLUMN achievement achievement INT DEFAULT 0;

2.1.3 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录。一般每年都会为exam_record表建立一张备份表exam_record_{YEAR}，{YEAR}为对应年份。

现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。

通过执行 SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'exam\_record\_201%'; 来对比输出结果。

正确的结果：
在这里插入图片描述

分析：
删除表：DROP TABLE [IF EXISTS] 表名1 [, 表名2];

本案例要求将2011到2014年的表全部删掉，我们直接使用删表语句进行删除即可：

DROP TABLE IF EXISTS 
	exam_record_2011, 
	exam_record_2012, 
	exam_record_2013, 
	exam_record_2014;

2.2 索引的创建与删除

2.2.1 现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：

在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

根据题意，将返回如下结果：
在这里插入图片描述
通过执行 SHOW INDEX FROM examination_info 语句来对比输出结果；

正确的输出结果为：
在这里插入图片描述

分析：
索引创建的方式有以下几种：

  1. create方式创建索引：
CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
  1. alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)
  2. drop方式删除索引：DROP INDEX <索引名> ON <表名>
  3. alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

当前表中，exam_id列已经使用了UNIQUE唯一约束：
在这里插入图片描述
当我们想创建索引时，需要先删除当前存在的唯一约束，不然最终结果会多一个唯一约束的值，与预期不符；当我们为一个表添加唯一约束时，数据库系统通常会在相应的列或列组合上自动创建一个唯一索引，以确保约束的有效性。这是因为唯一索引可以高效地帮助数据库系统检查插入或更新的数据是否违反了唯一性约束。

然而，当你尝试在已经存在唯一约束的列上创建另一个唯一索引时，你可能会遇到问题。这是因为数据库不允许在同一个列或列组合上有两个唯一索引。即使这两个唯一索引看起来是基于相同的列，数据库也会将它们视为不同的索引结构，因为索引本身可能具有不同的属性（如索引方法、存储方式等）。

因此，当你需要在已经存在唯一约束的列上创建新的唯一索引时，你需要先删除现有的唯一约束。

我们使用第一种方式在duration列创建普通索引idx_duration，使用第四种方式删除exam_id列的唯一约束，然后使用第二种方式创建唯一性索引uniq_idx_exam_id；使用第一种方式在tag列创建全文索引full_idx_tag；

CREATE INDEX idx_duration ON examination_info(duration);
ALTER TABLE examination_info DROP INDEX exam_id;
ALTER TABLE examination_info ADD UNIQUE uniq_idx_exam_id(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

2.2.2 请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

通过执行 SHOW INDEX FROM examination_info 来对比输出结果。

正确的输出结果为：
在这里插入图片描述

分析：

本题可采用第1种删除方式：

DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

也可采用第2种删除方式：

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

3.聚合分组查询

3.1 聚合函数

3.1.1 运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。
示例数据：examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
在这里插入图片描述

示例数据：exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
在这里插入图片描述
需执行数据插入脚本：

TRUNCATE exam_record;
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),
(1001,9001,'2021-07-02 09: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),
(1002,9003,'2021-09-01 12:01:01','2021-09-01 12:21:01',60),
(1002,9002,'2021-09-02 12:01:01','2021-09-02 12:31:01',70),
(1002,9001,'2021-09-05 19:01:01','2021-09-05 19:40:01',81),
(1002,9002,'2021-07-06 12:01:01',NULL,NULL),
(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),
(1003,9003,'2021-09-07 10:01:01','2021-09-07 10:31:01',86),
(1003,9003,'2021-09-08 12:01:01','2021-09-08 12:11:01',40),
(1003,9001,'2021-09-08 13:01:01',NULL,NULL),
(1003,9002,'2021-09-08 14:01:01',NULL,NULL),
(1003,9003,'2021-09-08 15:01:01',NULL,NULL),
(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),
(1005,9002,'2021-09-02 12:11:01','2021-09-02 12:31:01',89);

正确的查询结果为：
在这里插入图片描述

分析：
本案例要求计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）；
可以分为三步执行：

  1. 关联作答记录和试卷信息：join examination_info using(exam_id)；
  2. 筛选SQL高难度试卷：where tag=“SQL” and difficulty=“hard”
  3. 计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)

根据结果的示例情况，我们发现结果保留了一位小数，所以我们也要保证输出结果的值为保留一位小数的结果：

SELECT
  tag,
  difficulty,
  ROUND((SUM(score) - MAX(score) - MIN(score)) / (COUNT(score) - 2), 1) AS clip_avg_score
FROM exam_record
  JOIN examination_info
    USING (exam_id)
WHERE tag = "SQL"
    AND difficulty = "hard"

3.1.2 有一个试卷作答记录表exam_record，请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。

示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）。

正确的输出结果为：
在这里插入图片描述

分析：
本案例的查询可以分解为：

  1. 总作答次数：count(exam_id) as total_pv；
  2. 试卷已完成作答数，count(A)会忽略A的值为null的行：count(submit_time) as complete_pv；
  3. 已完成的试卷数，已完成时才计数用if判断，试卷可能被完成多次，需要去重用distinct：count(distinct if(submit_time is not null, exam_id, null)) as complete_exam_cnt

结合起来的SQL为：

SELECT
    COUNT(exam_id) AS total_pv,
    COUNT(submit_time) AS complete_pv,
    COUNT(DISTINCT IF(submit_time IS NOT NULL, exam_id, NULL)) AS complete_exam_cnt
FROM exam_record

IF(submit_time IS NOT NULL, exam_id, NULL)：这部分是一个条件函数，它检查submit_time字段是否不为NULL（即试卷是否已完成作答）。如果submit_time不为NULL，它返回对应的exam_id；否则，它返回NULL。

3.1.3 请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

  • 示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；
  • examination_info表（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；

正确的输出结果为：
在这里插入图片描述

分析：

  • 要找类别为SQL的试卷平均得分：
    • 得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
    • 从连接后的表中找到类别为SQL的试卷的分数。知识点：select…from…where…
    • 计算得分的平均值。知识点：avg()
  • 找到类别SQL的试卷得分大于平均得分的最小值：
    • 得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
    • 从连接后的表中找到类别为SQL的试卷且分数大于刚刚找到的平均分的分数。知识点：select…from…where…and…
    • 从中选出最小值。知识点：min()

首先最终的SQL为：

SELECT
  MIN(e_r.score) AS min_score_over_avg
FROM exam_record e_r
  JOIN examination_info e_i
    ON e_r.exam_id = e_i.exam_id
WHERE e_i.tag = 'SQL'
    AND score >= (SELECT
                    AVG(e1.score)
                  FROM exam_record e1
                    JOIN examination_info e2
                      ON e1.exam_id = e2.exam_id
                  WHERE tag = 'SQL')

3.2 分组查询

3.2.1 用户在学习网站作答记录存储在表exam_record中，内容如下：

  • exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，上面数据的示例输出如下：
在这里插入图片描述

解释：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天），平均活跃天数1.5；2021年9月有4人活跃，共活跃了5天，平均活跃天数1.25，结果保留2位小数。
注：此处活跃指有交卷行为。

分析：
本案例主要计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，并要求结果保留俩位小数。问题拆分后如下：

  • 根据提交时间submit_time不为空筛选活跃的的人。知识点：select…from…where…
  • 筛选每个月的平均活跃天数和总活跃人数：
    • 根据月份来选择时间。知识点：date_format() 通过这个函数匹配’%Y%m’年份和月份；
    • 计算用户平均活跃天数：
      • 根据不同的日期且不同的用户ID统计每个月用户的总活跃天数。知识点：distinct、count()、date_format()
      • 统计每个月用的总人数。知识点：distinct、count()
      • 总天数/总人数得到每个月的用户平均活跃天数；
    • 计算每月总活跃人数，直接统计每月不同的用户id数。知识点：count()、distinct
  • 按照月份分组group by date_format(submit_time, ‘%Y%m’) 知识点：group by …
  • 保留两位小数。 知识点：round(x,2)

所以最终的SQL为：

SELECT
  DATE_FORMAT(submit_time, '%Y%m') AS MONTH,
  ROUND((COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%y%m%d'))) / COUNT(DISTINCT uid), 2) AS avg_active_days,
  COUNT(DISTINCT uid) AS mau
FROM exam_record
WHERE submit_time IS NOT NULL
    AND YEAR(submit_time) = 2021
GROUP BY DATE_FORMAT(submit_time, '%Y%m')

3.2.2 现有一张题目练习记录表practice_record，请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：

在这里插入图片描述

解释：2021年8月共有2次刷题记录，日均刷题数为2/31=0.065（保留3位小数）；2021年9月共有3次刷题记录，日均刷题数为3/30=0.100；2021年共有5次刷题记录（年度汇总平均无实际意义，这里我们按照31天来算5/31=0.161）

分析：
先分别取出每个月的天数和年份，这里用到的是dayofmonth和date_format 然后分别统计每个月的刷题总数，和日均刷题数， 最后利用with rollup 进行统计，with rollup所在的行为’2021汇总’ 利用coalesce函数添加‘2021汇总’ 一般主流数据库系统都支持该coalesce函数 该函数主要用来进行空值处理，便能得到正确的SQL：

SELECT COALESCE(year_mon,'2021汇总') AS submit_month,
COUNT(question_id) AS month_q_cnt,
ROUND(COUNT(question_id)/MAX(t.days_month),3) AS avg_day_cnt
FROM
(SELECT question_id,
DAYOFMONTH(LAST_DAY(submit_time)) AS days_month,
DATE_FORMAT(submit_time,'%Y%m') AS year_mon
FROM practice_record
WHERE YEAR(submit_time)=2021) AS t
GROUP BY t.year_mon
WITH ROLLUP;

当然我们也可以使用last_day()函数，其用来获取参数日期的最后一天，这样的话就可以获取这个月的天数了，即day(last_day(subnit_time))；这样的话，我们可以通过这样来实现：

SELECT
    coalesce(DATE_FORMAT(submit_time,"%Y%m"),'2021汇总') submit_month,
    COUNT(submit_time) month_q_cnt,
    round(COUNT(submit_time) / MAX(DAY(last_day(submit_time))),3)avg_day_q_cnt
FROM
    practice_record
WHERE
    year(submit_time) = 2021
GROUP BY
    DATE_FORMAT(submit_time,"%Y%m") WITH ROLLUP;

3.2.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集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：
在这里插入图片描述

解释：2021年的作答记录中，除了1004，其他用户均满足有效用户定义，但只有1001和1003未完成试卷数大于1，因此只输出1001和1003，detail中是1001和1003作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接。

分析：
本案例要求统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。

根据题意拆解需求后得到：

  • 关联作答记录和试卷信息：left join examination_info on using(exam_id)；（题中exam_record中的exam_id在examination_info均存在，所以用left join和inner join效果一样）
  • 筛选2021年的记录：where year(start_time)=2021
  • 获取各用户的tag,start_time及未完成标记和已完成标记，如果该作答记录交卷了则已完成标记为1，未完成标记为0，否则相反：if(submit_time is null, 1, null) as incomplete
  • 按用户分组：group by uid
  • 统计未完成试卷作答数和已完成试卷作答数：count(incomplete) as incomplete_cnt
  • 统计作答过的tag集合：
    • 对于每条作答tag，用:连接日期和tag：concat_ws(‘:’, date(start_time), tag)
    • 对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(‘:’, date(start_time), tag) SEPARATOR ‘;’)
  • 筛选未完成试卷作答数大于1的有效用户：having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
    • 完成试卷作答数至少为1：complete_cnt >= 1
    • 未完成数小于5：incomplete_cnt < 5
    • 未完成试卷作答数大于1：incomplete_cnt > 1

即可得到正确的SQL：

SELECT
  uid,
  COUNT(incomplete) AS incomplete_cnt,
  COUNT(complete) AS complete_cnt,
  GROUP_CONCAT(DISTINCT CONCAT_WS(':', DATE(start_time), tag) SEPARATOR ';') AS detail
FROM (SELECT
        uid,
        tag,
        start_time,
        IF(submit_time IS NULL, 1, NULL) AS incomplete,
        IF(submit_time IS NULL, NULL, 1) AS complete
      FROM exam_record
        LEFT JOIN examination_info
          USING (exam_id)
      WHERE YEAR(start_time) = 2021) AS exam_complete_rec
GROUP BY uid
HAVING complete_cnt >= 1
    AND incomplete_cnt BETWEEN 2
    AND 4
ORDER BY incomplete_cnt DESC

4.多表查询

4.1 嵌套子查询

4.1.1 现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分）；试卷信息表examination_info（exam_id：试卷ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间）。

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

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

分析：本案例主要考察嵌套子查询的使用，所以我们先使用嵌套子查询实现其查询：

  • 筛选完成了的试卷的记录。知识点：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

首先我们查询满足：月均完成试卷数不小于3的用户

		SELECT
			uid
		FROM
			exam_record
		WHERE
			submit_time IS NOT NULL
		GROUP BY
			uid,
			DATE_FORMAT(submit_time, '%Y%m')
		HAVING
			COUNT(submit_time) > 2

筛选之后，按examination_info表的tag进行分组统计，最后降序输出就得到最终的SQL：

SELECT
	b.tag,
	count(*) tag_cnt
FROM
	exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE
	a.uid IN (
		SELECT
			uid
		FROM
			exam_record
		WHERE
			submit_time IS NOT NULL
		GROUP BY
			uid,
			DATE_FORMAT(submit_time, '%Y%m')
		HAVING
			COUNT(submit_time) > 2
	)
GROUP BY
	b.tag
ORDER BY
	tag_cnt DESC

4.1.2 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：
在这里插入图片描述

分析：

  • 获取每张SQL类别试卷发布日期，作为子查询：
    • 筛选试卷类别：WHERE tag = “SQL”
    • 获取试卷ID和发布日期：SELECT exam_id, DATE(release_time)
  • 筛选发布当天的作答记录：WHERE (exam_id, DATE(start_time)) IN (…)
  • 筛选5级以上的用户：AND uid IN (SELECT uid FROM user_info WHERE level > 5)
  • 按试卷ID分组：GROUP BY exam_id
  • 计算作答人数：count( DISTINCT uid ) AS uv
  • 计算平均分（保留1位小数）：ROUND(avg( score ), 1) AS avg_score

最终的SQL为：

SELECT
  exam_id,
  COUNT(DISTINCT uid) AS uv,
  ROUND(AVG(score),1) AS avg_score
FROM exam_record
WHERE exam_id IN(SELECT
                   exam_id
                 FROM examination_info
                 WHERE tag = 'SQL')
    AND uid IN(SELECT
                 uid
               FROM user_info
               WHERE LEVEL > 5)
    AND DATE(submit_time)IN(SELECT
                              DATE(release_time)
                            FROM examination_info
                            WHERE tag = 'SQL')
    AND submit_time IS NOT NULL
GROUP BY exam_id,DATE(submit_time)
ORDER BY uv DESC,avg_score asc

4.1.3 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答信息表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）。示例数据结果输出如下：
在这里插入图片描述

解释：9001为SQL类试卷，作答该试卷大于80分的人有1002、1003、1005共3人，6级两人，5级一人。

分析：
本案例主要要实现下列的查询：

  1. 统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序，相同数量按照等级降序
  2. 用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
  3. 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
  4. 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

将其问题拆分后即为：

  • 以每个等级分组，即level作为分组依据，便于计算每个等级的符合条件的人数。知识点：group by
  • 对于每个等级，我们只挑选类别为SQL、且得分大于80的不同的用户进行统计人数，相同的用户做了多次只统计一次：
    • 上述两个要求加上分组的等级分布在三个表中，我们可以将exam_record表根据exam_id与examination_info表连接在一起，然后将exam_record表根据uid与user_info连接在一起，这样三个表就连结在一起了。知识点：join…on…
    • 用where语句判断上述两种情况。知识点：where
  • 按照人数的降序，相同情况下等级降序输出。order by level_cnt desc, level desc 知识点：order by

最终的SQL为：

SELECT
  LEVEL,
  COUNT(DISTINCT u_i.uid) AS level_cnt
FROM exam_record e_r
  JOIN examination_info e_i
    ON e_r.exam_id = e_i.exam_id
  JOIN user_info u_i
    ON e_r.uid = u_i.uid
WHERE tag = 'SQL'
    AND score > 80
GROUP BY LEVEL
ORDER BY level_cnt DESC, LEVEL desc

4.2 合并查询

4.2.1 现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）；

请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：
在这里插入图片描述

解释：“试卷”有4人共练习16次试卷9001，4人作答10次9002，2人作答5次9003；“刷题”有2人刷3次8001，有2人刷2次8002

分析：
本案例主要考察合并查询的只是点，所以我们先已合并查询解决：

拆分案例主要信息我们可以知道：

  1. 请统计每个题目和每份试卷被作答的人数和次数，分别在试卷区和题目区按uv & pv降序显示
  2. 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），这是试卷区
  3. 题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分），这是题目区

将问题拆分后，即为：

  • 先统计试卷区每份试卷被回答的人数和次数：
    • 以试卷exam_id作为分组，便于统计每份试卷被作答的人数和次数。知识点：group by
    • 对于每一组即每一份试卷，统计作答的人数，即uid的数量，要注意去重，即同一人可能回答多次。知识点：count()、distinct
    • 对于每一组即每一份试卷，统计被作答次数，只需要统计出现多少次即可，不用去重。知识点：count()
    • 对查询结果按照先uv再pv的降序排序，order by uv desc, pv desc
  • 再统计题目区每份试卷被回答的人数和次数：
    • 以试卷question_id作为分组，便于统计每个题目被作答的人数和次数。知识点：group by
    • 对于每一组即每个题目，统计作答的人数，即uid的数量，要注意去重，即同一人可能回答多次。知识点：count()、distinct
    • 对于每一组即每个题目，统计被作答次数，只需要统计出现多少次即可，不用去重。知识点：count()
    • 对查询结果按照先uv再pv的降序排序，order by uv desc, pv desc
  • 从试卷区的选择中选出全部与从题目区的选择中选出的全部合并，select * from () exam union select * from () practice。知识点：union

最终结果为：

select * from
(select exam_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from exam_record
group by exam_id
order by uv desc, pv desc) exam
union
select * from
(select question_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from practice_record
group by question_id
order by uv desc, pv desc) practice

4.2.2 为了促进更多用户在学习平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。

现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

示例数据输出结果：
在这里插入图片描述

解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；1005最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；没有同时满足活动一和活动二的用户。

分析：
拆分案例主要信息我们可以知道：

  1. 分别筛选出2021年每次试卷得分都能到85分的人（activity1）和至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）
  2. 按照用户ID排序

将问题拆分后，即为：

  • 筛选2021年每次试卷得分都大于等于85的人和字符串’activity1’：
    • 按照uid进行分组划分，统计每个用户的得分情况。知识点：group by
    • 选出提交时间在2021年的试卷。知识点：select…from…where…、year()
    • 对于每组要求判断最小得分不小于85。知识点：having、min()
  • 筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串’activity2’：
    • 试卷信息和考试信息分布在两个表中，须将其通过exam_id连接起来。知识点：join…on…
    • 从连接后的两个表格中满足四个条件的不重复的用户ID，因为只要求至少一次下述情况(知识点：distinct、where…and…)：
      • 提交时间是2021年。year(e_r.submit_time) = 2021
      • 试卷难度是困难。e_i.difficulty = ‘hard’
      • 得分大于80。e_r.score > 80
      • 只用了试卷要求时间一半不到的时间就完成。timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
  • 将两个筛选合并。知识点：union all
  • 按照用户ID排序输出。知识点：order by uid

最终SQl为：

select uid,
       'activity1' as activity
from exam_record
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all 
select distinct uid,
       'activity2' as activity
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where year(e_r.submit_time) = 2021
and e_i.difficulty = 'hard'
and e_r.score > 80
and timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
order by uid

4.3 联结查询

后续案例需执行的脚本SQL：

drop table if exists examination_info,user_info,exam_record,practice_record;
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;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

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;

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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '学习1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '学习2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '学习3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '学习4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '学习5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '学习6号', 2000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

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),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1004, 8002, '2021-08-02 19:58:01', 94),
(1004, 8003, '2021-08-02 19:38:01', 70),
(1004, 8003, '2021-08-02 19:48:01', 90),
(1004, 8003, '2021-08-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);

4.3.1 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）;试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）;试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）;题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）.

请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

示例数据输出如下：
在这里插入图片描述

解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；1001完成了1次试卷1001，练习了2次题目；1003完成了2次试卷9001、9002，未练习题目（因此计数为0）

分析：
本案例主要查询找到高难度SQL试卷得分平均值大于80并且是7级红名大佬的2021年试卷完成数和题目练习数；

将问题拆分后，即为：

  • 本题主要是考察知识点：join、case when 、group by等，重点是对逻辑的理解！！！
  • 先查出平均值大于80并且是7级红名大佬的的uid，得到t1
  • t1分别与t2、t3关联，注意要用left join，因为有些uid可能没做某个试卷或练习，也要保留记录
  • 不能在关联后统一卡where条件，因为因为有些uid可能没做某个试卷或练习，所以submit_time可能为NULL，使用where and后的结果则不会含有submit_time为NULL的记录，会使结果变少，比如uid1003!!!使用where or的结果可能会使结果变多，下面有错误的代码结果，见2，3！！所以需要在count中用case when卡submit_time !!
  • count(distinct )时，要以id区分，不能以exam_id区分，因为存在一个uid可能对同一个试卷或练习做过多次！！比如1006对8003做过多次！！
  • 最后分组排序

则最后的SQL为：

select 
t1.uid,
-- count(distinct case when year(t2.submit_time) = '2021' then t2.exam_id else null end) as exam_cnt, -- 不对
-- count(distinct case when year(t3.submit_time) = '2021' then t3.question_id else null end) as question_cnt -- 不对
count(distinct case when year(t2.submit_time) = '2021' then t2.id else null end) as exam_cnt, -- 正确	
count(distinct case when year(t3.submit_time) = '2021' then t3.id else null end) as question_cnt -- 正确
from 
(
	select 
	uid
	from exam_record where uid in (select uid from user_info where level  = 7 ) 
	      and exam_id in (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard')
	group by uid 
	having sum(score) / count(score) > 80 
) t1 
 -- join -- 使用左联结
left join 
exam_record t2 
on t1.uid = t2.uid 
 -- join -- 使用左联结
left join 
practice_record t3 
on t1.uid = t3.uid 
-- where year(t2.submit_time) = '2021' and  year(t3.submit_time) = '2021' -- 不能在这里统一用where卡条件！！
group by t1.uid
order by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序，按题目练习数降序

4.3.2 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）。

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：
在这里插入图片描述

解释：6/7级用户共有5个，其中1001在共2个月活跃过，2021年活跃的日期有2天，在题目练习区活跃了1天。

分析：
拆分案例主要信息我们可以知道：

  1. 统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序
  2. 用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
  3. 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
  4. 题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）

将问题拆分后，即为：

  • 试卷以开始答题时间作为活跃时间，只要用户答题了就代表活跃，不管有没有完成。先筛选出用户ID，开始答题时间作为活跃时间，开始答题的月份作为活跃月份，开始答题的日期作为活跃天，标记tag为’exam’。select uid, start_time as act_time, date_format(start_time, ‘%Y%m’) as act_month, date_format(start_time, ‘%Y%m%d’) as act_day, ‘exam’ as tag
  • 题目以提交时间作为活跃时间，因为表格中没有开始时间。先筛选出用户ID，提交时间作为活跃时间，提交的月份作为活跃月份，提交的日期作为活跃天，标记tag为’question’。select uid, submit_time as act_time, date_format(submit_time, ‘%Y%m’) as act_month, date_format(submit_time, ‘%Y%m%d’) as act_day, ‘question’ as tag
  • 将上述两个筛选结果合并作为新表exam_and_practice。知识点：union all
  • 活跃信息在新表exam_and_practice中，用户等级信息在user_info中，因此在表user_info右边连接新表，以uid为准。知识点：left join…on… 使用left join是因为要使没有活跃的用户在连接后的表中显示活跃信息为空而不是消失。
  • 从连接后的表中筛选出等级大于等于6的用户，然后统计每个用户的活跃信息：
    • 每个用户都要统计，因此要对uid分组。知识点：group by
    • 统计每个uid下在连接后的表中的不同的活跃月份数。count(distinct act_month) as act_month_total 知识点：distinct、count()
    • 统计每个uid下在连接后的表中不同的活跃天数，前提是活跃时间的年份等于2021. count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021 知识点：case when…then…end
    • 统计每个id在连接后表中活跃时间的年份等于2021且标签为’exam’的不同活跃天数。count(distinct case when year(act_time) = 2021 and tag = ‘exam’ then act_day end) as act_days_2021_exam 知识点：case when…and…then…end
    • 统计每个id在连接后表中活跃时间的年份等于2021且标签为’question’的不同活跃天数。count(distinct case when year(act_time) = 2021 and tag = ‘question’ then act_day end) as act_days_2021_question 知识点：case when…and…then…end
  • 对选择的信息按照总活跃月份数、2021年活跃天数降序排序输出。order by act_month_total desc, act_days_2021 desc 知识点：order by

最终的SQL为：

SELECT u_i.uid AS uid,
       COUNT(DISTINCT act_month) AS act_month_total,
       COUNT(DISTINCT CASE 
             WHEN YEAR(act_time) = 2021 
             THEN act_day 
             END) AS act_days_2021,
       COUNT(DISTINCT CASE 
             WHEN YEAR(act_time) = 2021 
             AND tag = 'exam' 
             THEN act_day 
             END) AS act_days_2021_exam,
        COUNT(DISTINCT CASE
             WHEN YEAR(act_time) = 2021
             AND tag = 'question'
             THEN act_day
             END) AS act_days_2021_question
FROM user_info u_i
LEFT JOIN (SELECT uid,
             start_time AS act_time,
             DATE_FORMAT(start_time, '%Y%m') AS act_month,
             DATE_FORMAT(start_time, '%Y%m%d') AS act_day,
             'exam' AS tag
      FROM exam_record
      UNION ALL 
      SELECT uid,
             submit_time AS act_time,
             DATE_FORMAT(submit_time, '%Y%m') AS act_month,
             DATE_FORMAT(submit_time, '%Y%m%d') AS act_day,
             'question' AS tag
      FROM  practice_record
      ) exam_and_practice
ON exam_and_practice.uid = u_i.uid
WHERE u_i.level >= 6
GROUP BY uid
ORDER BY act_month_total DESC, act_days_2021 DESC

5.窗口函数

5.1 专用窗口函数

5.1.1 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：
在这里插入图片描述

解释：有作答得分记录的试卷tag有C++,SQL和算法，C++试卷用户先按最高得分排名再按最低得分排名取前三为1003、1005、1002。SQL试卷用户按最高得分排名再按最低得分排名取前三为1003、1004。算法试卷用户按最高得分排名再按最低得分排名取前三只有1006。

分析：
拆分案例主要信息我们可以知道：

  1. 到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大
  2. 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
  3. 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

将问题拆分后，即为：

  • 筛选出一个各类标签与用户及排名的表格：
    • 标签信息和得分信息分布在两个表格，需要将其用exam_id连接在一起。知识点：join…on…
    • 排名是以每个标签每个用户为组的，因此要分组。group by tag, e_r.uid 知识点：group by
    • 对每类标签使用分组聚合排名。知识点：row_number() over partition by 排名优先级先是每个用户的最大得分降序，然后是每个用户的最低得分降序，最后用户ID降序。知识点：order by、min()、max()
  • 从上述表格中选出排名小于等于3的标签、用户ID及排名。知识点：select…from…where…

所以最终SQL为：

SELECT tag, uid, ranking
FROM(
    SELECT tag, e_r.uid,
    row_number() over (PARTITION BY tag ORDER BY tag, MAX(score) DESC, MIN(score) DESC, e_r.uid DESC) AS ranking
    FROM exam_record e_r JOIN examination_info e_i
    ON e_r.exam_id = e_i.exam_id
    GROUP BY tag, e_r.uid
)ranktable
WHERE ranking <= 3

5.1.2 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；

此次作答需执行SQL脚本：

drop table if exists examination_info,exam_record;
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;

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, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:51:01', 78),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：
在这里插入图片描述

解释：试卷9001被作答用时有50分钟、50分钟、30分1秒、11分钟、10分钟，第二快和第二慢用时之差为50分钟-11分钟=39分钟，试卷时长为60分钟，因此满足大于试卷时长一半的条件，输出试卷ID、时长、发布时间。

分析：
拆分案例主要信息我们可以知道：

  1. 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序
  2. 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
  3. 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

将问题拆分后，即为：

  • 找到每份试卷的ID、限制时间、发布时间以及每份试卷被完成的耗时、耗时降序排名和增序排名。这里的不用去重，也不用分组，因为每一份都要计算耗时：
    • 试卷信息与做题信息分布在两个表中，因此要将两个表以exam_id连接。知识点：join…on…
    • 只查询有提交时间的时间，筛掉没做完的试卷。知识点：where
    • 试卷ID、限制时间、发布时间直接获取，完成的耗时使用timestampdiff函数根据开始时间和提交时间计算分钟数差值。知识点：timestampdiff()
    • 利用分组聚合排名对每一种试卷的完成耗时分别进行增序排名和降序排名。知识点：row_number() over partition by
    • 查询出的表格记为table1
  • 根据上面筛选出来的信息查询每份试卷的限制时间、发布时间及第二快与第二慢的差值：
    • 每张不同的试卷都会有一个数据，因此以试卷ID作为分组。知识点：group by
    • 每组试卷的ID、限制时间、发布时间都可以由table1直接查询到。
    • 将每组试卷的完成时间累加，只有当最慢排名为2时才加正值，最快排名为2时加负值，其余情况加0.这样刚好是用时第二多减去用时第二少。 sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub 知识点：sum()、case when…then…when…then…else…end
    • 查询出的结果记为table2
  • 最后从table2出筛选出大于等于限制时间一半的试卷ID，限制时间和发布时间
  • 输出按照试卷ID的降序排列。知识点：order by

所以最终的SQL为：

select distinct exam_id, duration, release_time
from
    (select exam_id as exam_id, duration, release_time,
           sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub
    from (
        select e_i.exam_id, duration, release_time,
        timestampdiff(minute, start_time, submit_time) as costtime,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2
        from exam_record e_r join examination_info e_i
        on e_r.exam_id = e_i.exam_id
        where submit_time is not null 
    ) table1
    group by exam_id
) table2
where sub * 2 >= duration
order by exam_id desc

5.1.3 现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）。

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：
在这里插入图片描述

解释：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；

分析：
本案例主要计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷； 按最大时间窗和平均做答试卷套数倒序排序。

将问题拆分后，即为：

  • 统计2021年每人总作答数、最早最晚相隔天数、最大连续作答间隔，生成子表 t_exam_record_stat:
    • 生成2021年每次作答试卷的下次作答时间，生成子表 t_exam_record_lead:
      • 筛选2021年的作答记录：WHERE YEAR(start_time)=2021
      • 生成下次作答时间，按用户分区按作答时间升序：
        • lead(start_time) over(PARTITION BY uid ORDER BY start_time) as next_start_time
    • 按用户分组：GROUP BY uid
    • 统计此人作答的总试卷数：count(start_time) as exam_cnt
    • 统计最早一次作答和最晚一次作答的相差天数：DATEDIFF(max(start_time), min(start_time))+1 as diff_days
    • 统计两次作答的最大时间窗：max(DATEDIFF(next_start_time, start_time))+1 as days_window
  • 筛选最早最晚相差天数大于1，即至少活跃两天的记录：WHERE diff_days > 1
  • 计算平均能做多少套试卷：ROUND(days_window * exam_cnt / diff_days, 2) as avg_exam_cnt

所以最终的SQL为：

SELECT uid, days_window, ROUND(days_window * exam_cnt / diff_days, 2) as avg_exam_cnt
FROM (
    SELECT uid,
        count(start_time) as exam_cnt,  -- 此人作答的总试卷数
        DATEDIFF(max(start_time), min(start_time))+1 as diff_days, -- 最早一次作答和最晚一次作答的相差天数
        max(DATEDIFF(next_start_time, start_time))+1 as days_window -- 两次作答的最大时间窗
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time) over(
                PARTITION BY uid ORDER BY start_time) as next_start_time -- 将连续的下次作答时间拼上
        FROM exam_record
        WHERE YEAR(start_time)=2021
    ) as t_exam_record_lead
    GROUP BY uid
) as t_exam_record_stat
WHERE diff_days > 1
ORDER BY days_window DESC, avg_exam_cnt DESC;

5.1.4 现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）。

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：
在这里插入图片描述

分析：
本案例主要查找每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名

将问题拆分后，即为：

  • 先从表exam_record中筛选出用户ID、答题开始时间、得分以及月份的降序排列：
    • 用户ID、开始时间、得分可以直接获取。
    • 月份降序我们用分组连续排名。知识点：dense_rank() over()、date_format() 对每个用户ID内进行排名，因为一个月可能出现多次，所以要采用连续排名，月份大的在前面月份小的在后，符合离现在最近的月份在前。dense_rank() over(partition by uid order by date_format(start_time, ‘%Y%m’) desc) as recent_months
  • 从上述结果中筛选出每个用户近三个的答题数：
    • 对于每个用户进行筛选，因此要以uid分组。知识点：group by
    • 只筛出近三个月的内容，因此上述排名我们只要排名小于等于3的。知识点：where
    • 过滤掉未完成试卷的用户，需要再分组后判断每组用户ID出现次数和得分出现次数是否一致，因为有得分才代表完成了试卷。知识点：having、count()
    • 统计上述没有过滤掉的结果中，每人的得分的总数，代表完成了多少试卷。知识点：count()
  • 根据先答题数后用户ID的降序次序输出。order by exam_complete_cnt desc, uid desc 知识点：order by…desc

所以最终的SQL为：

select uid,
       count(score) as exam_complete_cnt
from(
    select uid, start_time, score,
           dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
    from exam_record
) recent_table
where recent_months <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc

5.1.5 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）。

请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
由示例数据结果输出如下：
在这里插入图片描述

分析：
拆分案例主要信息我们可以知道：

  1. 统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目
  2. 筛选的时候用户按未完成率降序排名，相同情况按uid降序

将问题拆分后，即为：

  • 先筛选出每个用户的ID、未完成的试卷数、总试卷数：
    • 要计算每个用户的信息，因此要以uid分组。知识点：group by
    • 用户ID直接选择，每个人总试卷数统计每个人的开始时间出现的次数。知识点：count()
    • 每个用户的未完成的试卷数，我们统计得分，如果得分为null记为1，否则记为0，将每个人的这个if值求和就是得分为null出现的次数即每个人未完成试卷的份数。sum(if(score is null, 1, 0)) as incomplete_cnt 知识点：if()、sum()
    • 筛选出的信息记为incomplete_cnt_table
  • 从incomplete_cnt_table中筛选出每个用户ID、未完成率排名：
    • incomplete_cnt_table已经分组了，所以是每个ID对应一份数据，直接选出ID，不用分组。
    • 利用上述筛选出的每人的未完成试卷数和总试卷数计算未完成率，然后以先未完成率降序、再uid降序排名。row_number() over(order by (incomplete_cnt / total_cnt) desc, uid desc) as incomplete_rank 知识点：row_number()over()、order by
    • 筛选出的信息记为incomplete_rate_table
  • 对exam_record表中的不同的uid计数，统计总共多少用户做了试卷，计数信息记为表t_u。 select count(distinct uid) as total_user from exam_record 知识点：distinct、count()
  • 因为我们需要用户等级信息、总人数信息及未完成率排名，因此要将incomplete_rate_table与t_u直接连接在一起，然后通过uid再与user_info连接在一起。知识点：join
  • 筛选出未完成率较高的50%及用户等级为6或7级的用户ID：
    • 从三个连接的表中选出用户ID，条件是level>=6，并且排名小于等于总人数除2的向上取整（按照题目的意思，奇数人数要取中间因此向上取整）。where level >= 6 and incomplete_rank <= ceiling(total_user / 2) 知识点：where、ceiling()
  • 从exam_record中筛选出用户ID、开始做试卷时间、得分及做题月份的降序排名：
    • 用户ID、开始做题时间、得分可以直接得到
    • 做题月份的降序排名我们使用dense_rank()over()，因为可能会出现重复月份，我们要的是最大的三个月份，排名以uid分组，统计每个人的月份排名。dense_rank() over(partition by uid order by date_format(start_time, ‘%Y%m’) desc) as recent_months 知识点：dense_rank() over()、partition by、order by、date_format()
    • 筛选出的结果记为recent_table
  • 从recent_table中筛选出满足条件的用户ID及及其最近三个月的做题情况：
    • 需要统计每个用户每个月的情况，因此要以uid和start_month为分组。知识点：group by
    • 对于每个分组统计完成总题数，直接对recent_table中的开始做题时间计数；统计完成的题目数，直接对recent_table中的得分次数做计数。 知识点：count()
    • 当然筛选还有条件，就是月份排名小于等于3，即最近三个月，然后uid要位于上述筛选出来的前50%的uid中。知识点：where…and … in…
  • 最后按照用户ID排序输出即可。知识点：group by

最终的SQL为：

SELECT uid, 
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       COUNT(start_time) AS tatol_cnt,
       COUNT(score) AS complete_cnt
FROM(
    SELECT uid, start_time, score,
           dense_rank() over(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS recent_months
    FROM exam_record
) recent_table
WHERE recent_months <= 3
AND uid IN(
     SELECT incomplete_rate_table.uid
     FROM(
         SELECT uid,
            row_number() over(ORDER BY (incomplete_cnt / total_cnt) DESC, uid DESC) AS incomplete_rank
         FROM(
            SELECT uid,
                   SUM(IF(score IS NULL, 1, 0)) AS incomplete_cnt,
                   COUNT(start_time) AS total_cnt
            FROM exam_record
            GROUP BY uid
             ) incomplete_cnt_table
     ) incomplete_rate_table JOIN(
         SELECT COUNT(DISTINCT uid) AS total_user
         FROM exam_record
     ) t_u
     JOIN user_info
     ON incomplete_rate_table.uid = user_info.uid
     WHERE LEVEL >= 6
     AND incomplete_rank <= CEILING(total_user / 2)
)
GROUP BY uid, start_month
ORDER BY uid
数据分析师笔试试卷五：SQL、Python——（YH）
rubyw的博客
08-05 1586
现有用户购买行为表记录了客户ID、购买的产品ID和每次购买的金额，如下表，请统计每个产品购买金额前3的用户，输出产品ID、客户ID和购买金额。1.3-请用SQL语句查询每一天申请的订单数量，通过的数量，通过率，放款总金额，放款件均（base_apl 左关联 base_loan）接下来，可以先尝试 1.5 的平方，即 1.5×1.5 = 2.25，因为 2.25 小于 3，所以 大于 1.5。两个对象完全独立，互不影响。首先，我们知道 1 的平方是 1，2 的平方是 4，所以 的值在 1 到 2 之间。
经典SQL练习题(MySQL版)
热门推荐
廖致君的博客
09-08 21万+
原文首发于简书于[2018.07.30] 网上有一篇关于SQL的经典文章，超经典SQL练习题，做完这些你的SQL就过关了，引用和分析它的人很多，于是今天复习SQL的时候找来练了练手。原作者用的是SQL Server 2008，我在这里用的是MySQL 8.0.11（二者语法差别不大），文本编辑器用的是Atom 1.28.2（不知道大家用什么，反正用Atom写SQL确实丝质顺滑）。 题目顺序...
PTA-sql补题（3）
夏日漱石的博客
11-13 820
删除delete：
SQL笔试经典40题
weixin_44458771的博客
03-23 1万+
SQL是我接触的第二门编程语言，学得挺早，但到现在已经好几年没用过SQL了，因为搞机器学习用python（numpy/pandas），最近刷笔试题遇到SQL题都有点忘了，故再次拾起，回顾回顾。本文所使用的数据库为Oracle19c。SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL 语句可以单行或多行书写，以分号结尾；
经典 SQL 数据库笔试题及答案整理
百晓生说测试的博客
07-09 5407
最近有蛮多小伙伴在跳槽找工作，但对于年限稍短的软件测试工程师，难免会需要进行笔试，而在笔试中，基本都会碰到一道关于数据库的大题，今天这篇文章呢，就收录了下最近学员反馈上来的一些数据库笔试题，包含答案！
SQL试题及答案
ElliotCai的博客
07-10 3930
一、单项选择题 1 ．以下聚合函数求数据总和的是( ) A ． MAX B ． SUM C ． COUNT D ． AVG 答案： B 2 ．可以用 ( ) 来声明游标 A ． CREATE CURSOR B ． ALTER CURSOR C ． SET CURSOR D ． DECLARE CURSOR 答案： D 3 ． SELECT 语句的完整语法较复杂，但至少包括的部分是 ( ) A ．仅 SELE
精选36道SQL练习题解析 from（原50道SQL练习题）
若如初见
05-29 1万+
精选36道MySQL练习题解析 from 原50道SQL练习题 对网络上流传的50道SQL练习题做了筛选，去掉了一些重复的题，剩下36道，总结分为6大类，几乎囊括了SQL查询绝大部分知识点，并给出了不同方法及解析，掌握这36道题，SQL查询语句基本上没啥大问题了
spark sql的练习题
汉子一枚
04-18 3546
1、使用Structured Streaming读取Socket数据，把单词和单词的反转组成 json 格式写入到当前目录中的file文件夹中 2、请使用Structured Streaming读取student_info文件夹写的csv文件， 2.1、统计出文件中的男女生各有多少人 2.2、统计出姓“王”男生和女生的各有多少人 3、请使用Structured Streaming读取departm...
SQL128 未完成试卷数大于1的有效用户
m0_59757040的博客
07-12 329
现有试卷作答记录表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），输出用
SQL 50 题（MySQL 版，包括建库建表、插入数据等完整过程，适合复习 SQL 知识点）
代码星辰的博客
11-16 2万+
本文整理了经典的 50 道 SQL 题目，文本分为建库建表、插入数据以及 SQL 50 题这三个部分。
MySQL--牛客习题（二）-- 时间差问题
sdau_20171819的博客
02-07 829
描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表： 作答记录表exam_record： start_time是试卷开始时间 submit_time 是交卷，即结束时间 请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录； 后台会通过 SELECT * FROM exam_record; 语句来对比结果。 #方式一 delete from exam_record where timediff(submit_time,sta
SQL(入门实战06)
weixin_44464850的博客
04-26 2770
1.创建一张新表 2.修改表 3.删除表 4.创建索引 5.删除索引 6. SQL类别高难度试卷得分的截断平均值
SQL 进阶挑战（1 - 5）
村雨遥
06-12 1218
牛客网 SQL 进阶挑战
【SQL】SQL常见面试题总结（3）
Hanson的博客
05-16 1182
sql由浅入深，面试题，【SQL】SQL常见面试题总结（3）
SQL 题目及答案
殇沫流年的专栏
11-17 1500
SQL 题目及答案 学生成绩表(stuscore)： 姓名：name     课程：subject     分数：score     学号：stuid 张三     数学     89     1 张三     语文     80     1 张三     英语     70     1 李四     数学     90     2 李四     语文     70
sql语句练习50题(含解析)
m0_45210394的博客
09-21 2万+
sql练习笔记
50道SQL练习题
最新发布
weixin_75189493的博客
04-15 951
-1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表--TId 教师编号,Tname 教师姓名--4.成绩表--SId 学生编号,CId 课程编号,score 分数。
SQL经典50题
雷神乐乐的博客
12-08 1万+
SQL经典50题——mysql8.0版本
【牛客】SQL133 分别满足两个活动的人
talle2021的博客
03-02 715
假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值