各用户等级的不同得分表现占比

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	19	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客3号♂	22	0	算法	2020-01-01 10:00:00
4	1004	牛客4号	25	0	算法	2020-01-01 10:00:00
5	1005	牛客555号	2000	7	C++	2020-01-01 10:00:00
6	1006	666666	3000	6	C++	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	75
4	1001	9002	2021-09-01 12:01:01	2021-09-01 12:11:01	60
5	1001	9003	2021-09-02 12:01:01	2021-09-02 12:41:01	90
6	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
7	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
8	1001	9004	2021-09-03 12:01:01	(NULL)	(NULL)
9	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	99
10	1002	9003	2020-02-01 12:01:01	2020-02-01 12:31:01	82
11	1002	9003	2020-02-02 12:11:01	2020-02-02 12:41:01	76

为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。

由示例数据结果输出如下：

level	score_grade	ratio
3	良	0.667
3	优	0.333
0	良	0.500
0	中	0.167
0	优	0.167
0	差	0.167

解释：完成过试卷的用户有1001、1002；完成了的试卷对应的用户等级和分数等级如下：

uid	exam_id	score	level	score_grade
1001	9001	80	0	良
1001	9002	75	0	良
1001	9002	60	0	中
1001	9003	90	0	优
1001	9001	20	0	差
1001	9002	89	0	良
1002	9001	99	3	优
1002	9003	82	3	良
1002	9003	76	3	良

因此0级用户（只有1001）的各分数等级比例为：优1/6，良1/6，中1/6，差3/6；3级用户（只有1002）各分数等级比例为：优1/3，良2/3。结果保留3位小数。

示例1

输入:

drop table if exists user_info,exam_record;
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', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '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', 75),
(1001, 9002, '2021-09-01 12:01:01', '2021-09-01 12:11:01', 60),
(1001, 9003, '2021-09-02 12:01:01', '2021-09-02 12:41:01', 90),
(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, 9004, '2021-09-03 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99),
(1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9003, '2020-02-02 12:11:01', '2020-02-02 12:41:01', 76);

输出:

3|良|0.667
3|优|0.333
0|良|0.500
0|中|0.167
0|优|0.167
0|差|0.167

原站题解

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

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

with tmp2 as (
select ui.level,tmp.score_grade,count(tmp.uid) as num from
(select uid,exam_id
,(case when score >= 90 then '优'
      when score<90 and score >= 75 then '良'
      when score<75 and score >= 60 then '中'
      else '差'
      end)as score_grade
      from exam_record
      where submit_time is not null) tmp
      left join user_info ui
      on tmp.uid = ui.uid
      group by ui.level,tmp.score_grade)
      
select tmp2.level as level,tmp2.score_grade,round(tmp2.num/tmp3.total_num,3) as ratio
# ,tmp2.num,tmp3.total_num
from tmp2
      left join 
      (
      select level,sum(num) as total_num from tmp2 group by level)tmp3
      on tmp2.level = tmp3.level

      order by level desc,ratio desc

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

select level,sg,round(count(*)/cn,3)as ratio
FROM
(select t1.uid,t2.level,t1.score,
(case when score>=90 then '优' when score>=75 then '良' when score>=60 then '中' else '差' end)as sg,
count(*)over(partition by level)as cn
from exam_record as t1
join user_info as t2
using(uid)
where score is not NULL)as a
group by level,sg
order by level desc,ratio DESC;

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

WITH s1 AS (SELECT exam_record.uid , level , score,
CASE 
WHEN score<60 THEN '差'
WHEN score<75 THEN '中'
WHEN score<90 THEN '良'
ELSE '优' END score_grade
, COUNT(*) over(PARTITION BY level) cnt_level
FROM exam_record LEFT JOIN user_info
USING(uid)
WHERE score IS NOT NULL)


SELECT level , score_grade , round(COUNT(uid)/cnt_level,3) ratio
FROM 
s1
GROUP BY level,score_grade
ORDER BY level desc , ratio desc

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

# 将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间）
# 请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数）
# 未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。

SELECT l.level,
       l.score_grade,
       ROUND(COUNT(l.score_grade) / total,3) AS ratio
FROM(
    SELECT ui.uid,ui.level,
           (CASE WHEN score >= 90 THEN '优'
                WHEN score >= 75 THEN '良'
                WHEN score >= 60 THEN '中'
                ELSE '差' END) AS score_grade,
           COUNT(*) OVER(PARTITION BY ui.level) AS total
    FROM user_info ui
    JOIN exam_record er
    ON ui.uid = er.uid
    WHERE score IS NOT NULL
) AS l
GROUP BY l.level,l.score_grade
ORDER BY l.level DESC,ratio DESC

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

select level,score_grade,round(count(score_grade)/cnt,3) ratio
from(
select level
,case when score>=90 then '优'
when score>=75 then '良'
when score>=60 then '中'
else '差' end score_grade
,count(*)over(partition by level)cnt
from exam_record join user_info using(uid)
where score is not null)as t
group by level,score_grade
order by level desc,ratio desc

上一题

下一题

详情