[大数据]SQL(进阶实战02)

1. 分别满足两个活动的人

为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到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；1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2。

示例1

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, '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:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(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);

输出

1001|activity2
1003|activity1
1004|activity1
1004|activity2

备注

按用户ID排序输出

题解

思路：
(1)筛选2021年每次试卷得分都大于等于85的人和字符串'activity1'：
1.按照uid进行分组划分，统计每个用户的得分情况。知识点：group by
2.选出提交时间在2021年的试卷。知识点：select...from...where...、year()
3.对于每组要求判断最小得分不小于85。知识点：having、min()

(2)筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串'activity2'：
1.试卷信息和考试信息分布在两个表中，须将其通过exam_id连接起来。知识点：join...on...
2.从连接后的两个表格中满足四个条件的不重复的用户ID，
因为只要求至少一次下述情况(知识点：distinct、where...and...)：
3.提交时间是2021年。year(e_r.submit_time) = 2021
4.试卷难度是困难。e_i.difficulty = 'hard'
5.得分大于80。e_r.score > 80
6.只用了试卷要求时间一半不到的时间就完成。
timestampdiff(minute,start_time,submit_time) * 2 <duration


select uid, activity
from (
select uid,'activity1'as activity
from examination_info t1
inner join 
exam_record t2
on t1.exam_id=t2.exam_id and year(start_time)='2021' 
group by uid
having min(score)>=85
union all
select uid,'activity2'as activity
from examination_info t1
inner join 
exam_record t2
on t1.exam_id=t2.exam_id and year(start_time)='2021' 
where 
 timestampdiff(Minute,start_time,submit_time)<(duration/2)
 and difficulty='hard'
 and score>80
group by uid
)t
order by uid

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得分）：

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

示例数据输出如下：

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

示例1

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),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);

输出

1001|1|2
1003|2|0

思路

1.先查出高难度SQL试卷得分平均值大于80并且是7级的红名大佬,统计他们的2021年试卷
SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND YEAR ( submit_time ) = 2021 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
2.2021年试卷总完成次数和题目总练习次数 
left join exam_record,left join  practice_record

题解

方式一：
SELECT DISTINCT
	uid,
	ifnull( t1.exam_cnt, 0 ) AS exam_cnt,
	ifnull( t2.question_cnt, 0 ) AS question_cnt 
FROM
	(
	SELECT
		uid,
		count( exam_id ) exam_cnt 
	FROM
		exam_record 
	WHERE
		YEAR ( submit_time ) = 2021 
		AND uid IN (
		SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND YEAR ( submit_time ) = 2021 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
		) 
	GROUP BY uid 
	) t1
	LEFT JOIN (
	SELECT
		uid,
		count( question_id ) question_cnt 
	FROM
		practice_record 
	WHERE
		YEAR ( submit_time ) = 2021 
		AND uid IN (
		SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
		) 
	GROUP BY uid 
	) t2 USING ( uid ) 
ORDER BY
	exam_cnt,
	question_cnt DESC
	
方式二：
select 
t1.uid,
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
			examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY
			uid 
		HAVING
			avg( score )> 80
		)t1
left join exam_record t2 
on t1.uid = t2.uid 
left join practice_record t3 
on t1.uid = t3.uid 
group by t1.uid
order by exam_cnt asc , question_cnt desc ;	

3.每个6/7级用户活跃情况

现有用户信息表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个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

示例1

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号', 2600, 7, '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++', 'easy', 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),
(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),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-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', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1005, 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),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);

备注：

按照总活跃月份数、2021年活跃天数降序排序

思路:

1.获取用户的试卷作答活跃天数、答题活跃天数的数据。
需要拼接exam_record和practice_record的数据，并作出区分

select uid, 
date_format(start_time,'%Y%m') as months,
date_format(start_time,'%Y%m%d') as day_tag,
start_time as days,
'exam' as tag
from exam_record
union all
select uid,
date_format(submit_time,'%Y%m') as months,
date_format(submit_time,'%Y%m%d') as day_tag,
submit_time as days, 
'question' as tag
from practice_record

2.统计2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，
注意需要去重同一天的试卷做题、答题,
count(distinct  case when year(days)=2021 and tag='exam' then day_tag end)
count(distinct  case when year(days)=2021 and tag='question' then day_tag end) as act_days_2021_question

为什么是day_tag，因为day_tag格式化成%Y%m%d。
相当与count(distinct day_tag),去重同一天的试卷做题、答题

3.判断是否是2021年
 case when year(days)=2021 then day_tag end

题解

select uid,
count(distinct months) as act_month_total,
count(distinct  case when year(days)=2021 then day_tag end) as act_days_2021,
count(distinct  case when year(days)=2021 and tag='exam' then day_tag end) as act_days_2021_exam,
count(distinct  case when year(days)=2021 and tag='question' then day_tag end) as act_days_2021_question
from user_info
left join 
(
select 
    uid, 
    date_format(start_time,'%Y%m') as months, 				  	  	
    date_format(start_time,'%Y%m%d') as day_tag,
    start_time as days, 'exam' as tag
from exam_record
union all
select 
    uid, 
    date_format(submit_time,'%Y%m') as months,
    date_format(submit_time,'%Y%m%d') as day_tag,
    submit_time as days, 'question' as tag
from practice_record
)exam
using(uid)
where `level`>=6
group by uid
order by act_month_total desc,act_days_2021 desc

4.每类试卷得分前3名

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

试卷作答记录表：

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

解释：有作答得分记录的试卷tag有SQL和算法，SQL试卷用户1001、1002、1003、1004有作答得分，最高得分分别为81、81、89、85，最低得分分别为78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。

示例1

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, 'SQL', '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:31:00', 78),
(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, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);

题解

思路：
1.根据最大分数，最小分数，uid，并排名每类试卷
row_number()over(partition by xxx order by xxxx)
讲解和实战：(3.找出每个学校GPA最低的同学的拓展)
https://blog.csdn.net/weixin_44464850/article/details/124386872


select tag, uid, ranking
from(
    select tag, 
    uid,
    row_number() over (partition by tag
                       order by tag,
                       max(score) desc,
                       min(score) desc, 
                       uid desc) as ranking
    from exam_record 
		inner join examination_info 
    using(exam_id)
    group by tag, uid
)ranktable
where ranking <= 3

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖