0级用户高难度试卷的平均用时和平均得分

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	10	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	2100	6	算法	2020-01-01 10:00:00

试卷信息表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-01-01 10:00:00
3	9004	算法	medium	80	2020-01-01 10:00:00

试卷作答记录表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:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
4	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
5	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90

请输出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。由示例数据结果输出如下：

uid	avg_score	avg_time_took
1001	33	36.7

解释：0级用户有1001，高难度试卷有9001，1001作答9001的记录有3条，分别用时20分钟、未完成（试卷时长60分钟）、30分钟（未满31分钟），分别得分为80分、未完成（0分处理）、20分。因此他的平均用时为110/3=36.7（保留一位小数），平均得分为33分（取整）

示例1

输入:

drop table if exists examination_info,user_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 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 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号', 10, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00');

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-01-01 10:00:00'),
  (9004, '算法', 'medium', 80, '2020-01-01 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:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(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, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90);

输出:

1001|33|36.7

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6452KB, 提交时间: 2022-01-24

-- 每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。

SELECT
    ui.uid,
    ROUND(AVG(IFNULL(score,0)),0),
    ROUND(AVG(IFNULL(TIMESTAMPDIFF(minute,start_time,submit_time),ei.duration)),1)
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
LEFT JOIN examination_info ei USING(exam_id)
WHERE level = 0
AND difficulty = 'hard'
GROUP BY ui.uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2022-01-23

select er.uid,
       round(avg(if(score is null,0,score)),0) as avg_score, 
       round(avg(if(submit_time is null,duration,TIMESTAMPDIFF(minute,start_time,submit_time))),1) as avg_time_took
from exam_record er
join examination_info ei
     on er.exam_id=ei.exam_id
        and difficulty="hard"
join user_info ui
     on er.uid=ui.uid
        and LEVEL=0
group by er.uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-11-30

select uid,
       round(avg(new_score), 0) as avg_score,
       round(avg(cost_time), 1) as avg_time_took
from(
    select e_r.uid as uid,
           if(score is not null, score, 0) as new_score,
           if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time
    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 level = 0
    and difficulty = 'hard'
    ) new_table
group by uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2021-12-07

select uid,
       round(sum(if(score is not null,score,0))/count(1),0) as avg_score,
       round(sum(if(score is NULL,duration,timestampdiff(minute,start_time,submit_time)))/count(1),1) as avg_time_took
from exam_record t
join examination_info t1 on t.exam_id = t1.exam_id
where uid in (select uid from user_info where level = 0) and difficulty = 'hard'
group by uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-14

select uid,floor(avg(sr)) avg_score,round(avg(dur),1) avg_time_took
from 
    (select er.*,
           if(score,score,0) sr,
            if(submit_time is null,duration,floor(timestampdiff(MINUTE,start_time,submit_time))) dur
    from exam_record er left join user_info  on  er.uid=user_info.uid
                        left join examination_info ei on er.exam_id=ei.exam_id
    where level=0 and difficulty='hard') t
group by uid

上一题

下一题

详情