试卷完成数同比2020年的增长率及排名变化

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

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-01-01 10:00:00
2	9002	C++	hard	80	2021-01-01 10:00:00
3	9003	算法	hard	80	2021-01-01 10:00:00
4	9004	PYTHON	medium	70	2021-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-08-02 10:01:01	2020-08-02 10:31:01	89
2	1002	9001	2020-04-01 18:01:01	2020-04-01 18:59:02	90
3	1001	9001	2020-04-01 09:01:01	2020-04-01 09:21:59	80
5	1002	9001	2021-03-02 19:01:01	2021-03-02 19:32:00	20
8	1003	9001	2021-05-02 12:01:01	2021-05-02 12:31:01	98
13	1003	9001	2020-01-02 10:01:01	2020-01-02 10:31:01	89
9	1001	9002	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1002	9002	2021-02-02 12:01:01	2020-02-02 12:43:01	81
11	1001	9002	2020-01-02 19:01:01	2020-01-02 19:59:01	69
16	1002	9002	2020-02-02 12:01:01
17	1002	9002	2020-03-02 12:11:01
18	1001	9002	2021-05-05 18:01:01
4	1002	9003	2021-01-20 10:01:01	2021-01-20 10:10:01	81
6	1001	9003	2021-04-02 19:01:01	2021-04-02 19:40:01	89
15	1002	9003	2021-01-01 18:01:01	2021-01-01 18:59:02	90
7	1004	9004	2020-05-02 12:01:01	2020-05-02 12:20:01	99
12	1001	9004	2021-09-02 12:11:01
14	1002	9004	2020-01-01 12:11:01	2020-01-01 12:31:01	83

请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

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

tag	exam_cnt_20	exam_cnt_21	growth_rate	exam_cnt_rank_20	exam_cnt_rank_21	rank_delta
SQL	3	2	-33.3%	1	2	1

解释：2020年上半年有3个tag有作答完成的记录，分别是C++、SQL、PYTHON，它们被做完的次数分别是3、3、2，做完次数排名为1、1（并列）、3；

2021年上半年有2个tag有作答完成的记录，分别是算法、SQL，它们被做完的次数分别是3、2，做完次数排名为1、2；具体如下：

tag	start_year	exam_cnt	exam_cnt_rank
C++	2020	3	1
SQL	2020	3	1
PYTHON	2020	2	3
算法	2021	3	1
SQL	2021	2	2

因此能输出同比结果的tag只有SQL，从2020到2021年，做完次数3=>2，减少33.3%（保留1位小数）；排名1=>2，后退1名。

示例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, '2020-01-01 10:00:00'),
  (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-08-02 10:01:01', '2020-08-02 10:31:01', 89),
(1002, 9001, '2020-04-01 18:01:01', '2020-04-01 18:59:02', 90),
(1001, 9001, '2020-04-01 09:01:01', '2020-04-01 09:21:59', 80),
(1002, 9003, '2021-01-20 10:01:01', '2021-01-20 10:10:01', 81),
(1002, 9001, '2021-03-02 19:01:01', '2021-03-02 19:32:00', 20),
(1001, 9003, '2021-04-02 19:01:01', '2021-04-02 19:40:01', 89),
(1004, 9004, '2020-05-02 12:01:01', '2020-05-02 12:20:01', 99),
(1003, 9001, '2021-05-02 12:01:01', '2021-05-02 12:31:01', 98),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9004, '2021-09-02 12:11:01', null, null),
(1003, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9004, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9003, '2021-01-01 18:01:01', '2021-01-01 18:59:02', 90),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9002, '2021-05-05 18:01:01', null, null);

输出:

SQL|3|2|-33.3%|1|2|1

原站题解

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

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

Select tag
,exam_cnt_20
,exam_cnt_21
,growth_rate
,exam_cnt_rank_20
,exam_cnt_rank_21
,Cast(exam_cnt_rank_21 As signed) - Cast(exam_cnt_rank_20 As signed) As rank_delta#signed为整型默认属性
From
(
	Select tag
	,exam_cnt_20
	,exam_cnt_21
	,Concat(Round((exam_cnt_21- exam_cnt_20)/exam_cnt_20*100,1),'%') As growth_rate
	,Rank() Over(Order By exam_cnt_20 Desc) As exam_cnt_rank_20
	,Rank() Over(Order By exam_cnt_21 Desc) As exam_cnt_rank_21
	From
	(
		Select tag
		,Count(Case When Date(submit_time) Between '2020-01-01' And '2020-06-30' Then 1 Else Null End) As exam_cnt_20
		,Count(Case When Date(submit_time) Between '2021-01-01' And '2021-06-30' Then 1 Else Null End) As exam_cnt_21
		From exam_record
		Join examination_info
		Using(exam_id)
		Group By tag
	) As d
) As u
Where exam_cnt_20 != 0
And exam_cnt_21 != 0
Order By growth_rate Desc,exam_cnt_rank_21 Desc;

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

with info_2020 as (
select ei.tag, count(er.score) as exam_cnt ,rank() over(order by count(er.score) desc) as exam_cnt_rank
from exam_record as er
right join examination_info as ei
on er.exam_id=ei.exam_id
where year(er.start_time)=2020 and month(er.start_time)<=6
group by ei.tag
having count(er.score)>0),
info_2021 as (
select ei.tag, count(er.score) as exam_cnt ,rank() over(order by count(er.score) desc) as exam_cnt_rank
from exam_record as er
right join examination_info as ei
on er.exam_id=ei.exam_id
where year(er.start_time)=2021 and month(er.start_time)<=6
group by ei.tag
having count(er.score)>0),
exam_year_info as (
select i20.tag, i20.exam_cnt as exam_cnt_20, i21.exam_cnt as exam_cnt_21,
       i20.exam_cnt_rank as exam_cnt_rank_20, i21.exam_cnt_rank as exam_cnt_rank_21
from info_2020 as i20
join info_2021 as i21
on i20.tag=i21.tag)

select tag, exam_cnt_20, exam_cnt_21,
       concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100, 1), '%') as growth_rate,
       exam_cnt_rank_20, exam_cnt_rank_21, (cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed)) as rank_delta
from exam_year_info
order by growth_rate desc, exam_cnt_rank_21 desc;

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

Select tag
,exam_cnt_20
,exam_cnt_21
,growth_rate
,exam_cnt_rank_20
,exam_cnt_rank_21
,Cast(exam_cnt_rank_21 As signed) - Cast(exam_cnt_rank_20 As signed) As rank_delta
From
(
	Select tag
	,exam_cnt_20
	,exam_cnt_21
	,Concat(Round((exam_cnt_21- exam_cnt_20)/exam_cnt_20*100,1),'%') As growth_rate
	,Rank() Over(Order By exam_cnt_20 Desc) As exam_cnt_rank_20
	,Rank() Over(Order By exam_cnt_21 Desc) As exam_cnt_rank_21
	From
	(
		Select tag
		,Count(Case When Date(submit_time) Between '2020-01-01' And '2020-06-30' Then 1 Else Null End) As exam_cnt_20
		,Count(Case When Date(submit_time) Between '2021-01-01' And '2021-06-30' Then 1 Else Null End) As exam_cnt_21
		From exam_record
		Join examination_info
		Using(exam_id)
		Group By tag
	) As d
) As u
Where exam_cnt_20 != 0
And exam_cnt_21 != 0
Order By growth_rate Desc,exam_cnt_rank_21 Desc;

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

select A.tag,exam_cnt_20,exam_cnt_21,CONCAT(round((exam_cnt_21-exam_cnt_20) * 100 / exam_cnt_20,1),'%') as growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,cast(exam_cnt_rank_21 as SIGNED) - cast(exam_cnt_rank_20 as SIGNED) as exam_cnt_rank
from
(select distinct tag,count(submit_time) as exam_cnt_20,rank()over(order by count(submit_time) desc) as exam_cnt_rank_20
FROM exam_record left join examination_info using(exam_id)
where submit_time is not null
and year(start_time)=2020 and month(start_time)<=6
group by tag) A
join (
select distinct tag,count(submit_time) as exam_cnt_21,rank()over(order by count(submit_time) desc) as exam_cnt_rank_21
FROM exam_record left join examination_info using(exam_id)
where submit_time is not null
and year(start_time)=2021 and month(start_time)<=6
group by tag) B using(tag)
order by growth_rate desc,exam_cnt_rank_21 desc

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

with t1 as(select ei.tag,count(er.submit_time) as exam_cnt,rank() over(order by count(er.submit_time) desc) as exam_cnt_rank,cast("2020" as signed) as start_year
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where date_format(er.submit_time,"%Y%m") between "202001" and "202006"
group by ei.tag
union 
(select ei.tag,count(er.submit_time) as exam_cnt,rank() over(order by count(er.submit_time) desc) as exam_cnt_rank,cast("2021" as signed) as start_year
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where date_format(er.submit_time,"%Y%m") between "202101" and "202106"
group by ei.tag))

select p1.tag,p1.exam_cnt as exam_cnt_20,p2.exam_cnt as exam_cnt_21,
concat(round(((p2.exam_cnt-p1.exam_cnt)/p1.exam_cnt*100),1),"%") as growth_rate,
p1.exam_cnt_rank as exam_cnt_rank_20,p2.exam_cnt_rank as exam_cnt_rank_21,
cast(p2.exam_cnt_rank as signed)-cast(p1.exam_cnt_rank as signed) as rank_delta
from t1 as p1
inner join t1 as p2
on p1.tag=p2.tag and p1.start_year=p2.start_year-1
order by growth_rate desc,exam_cnt_rank_21 desc

上一题

下一题

详情