试卷完成数同比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

牛客410554848号

select tag, exam_cnt_20, exam_cnt_21, 
 concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
 exam_cnt_rank20, exam_cnt_rank21,
(cast(exam_cnt_rank21 as signed)-cast( exam_cnt_rank20 as signed)) rank_delta
from
(select tag,
sum(if( start_year=2020 , exam_cnt ,0)) exam_cnt_20,
sum(if(start_year=2021,exam_cnt,0)) exam_cnt_21,
sum(if(start_year=2020,exam_cnt_rank,0)) exam_cnt_rank20,
sum(if(start_year=2021,exam_cnt_rank,0)) exam_cnt_rank21
from

(select tag,year(submit_time) start_year ,count(*) exam_cnt ,
rank() over(partition by year(submit_time) order by count(*)  desc) exam_cnt_rank
from  exam_record e_r join examination_info e_i
using(exam_id) 
where submit_time is not null 
 and year(submit_time) BETWEEN 2020 and 2021 
 and month(submit_time)<=6
group by tag,year(submit_time))mm

group by tag
)nn
where exam_cnt_21 <>0
and exam_cnt_20 <>0
order by growth_rate desc, exam_cnt_rank21 desc

看其他答主代码优化的，
运行时间和内存咱用超越98%代码

编辑于 2021-12-17 11:18:51 回复(0)

Mysql

啊坡糍嘚

with cnt_2021_2020 AS
(
select tag,start_year,exam_cnt,rank()over(partition by start_year order by exam_cnt desc) as exam_cnt_rank
from 
 (select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt
 from exam_record er left join examination_info ef on er.exam_id=ef.exam_id
 where year(start_time)='2021' and month(start_time)<=6
 union all 
 select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt
 from exam_record er left join examination_info ef on er.exam_id=ef.exam_id
 where year(start_time)='2020' and month(start_time)<=6) t2
)


select t3.tag
,t3.exam_cnt as exam_cnt_20
,t4.exam_cnt as exam_cnt_21
,concat(left((t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt*100,5),'%') as growth_rate
,t3.exam_cnt_rank as exam_cnt_rank_20
,t4.exam_cnt_rank as exam_cnt_rank_21
,if(t4.exam_cnt_rank>t3.exam_cnt_rank,t4.exam_cnt_rank-t3.exam_cnt_rank,-(t3.exam_cnt_rank-t4.exam_cnt_rank)) as rank_delta
from (select * from cnt_2021_2020 where start_year='2020') t3
left join 
(select * from cnt_2021_2020 where start_year='2021') t4 on t3.tag=t4.tag
where t4.exam_cnt>=1
order by growth_rate desc,exam_cnt_rank_21 desc

发表于 2021-11-13 21:35:41 回复(0)

Mysql

后街小行家

先说我自己的答案，不用cast（）字段类型转换函数的话，需要两个sql语句，不太会把这两个语句合成一个，会的大佬请留言

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
select * ,exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta
from(
 select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,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 i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20,
count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21
from exam_record r left join examination_info i on r.exam_id=i.exam_id
 group by i.tag
 ) a  )b
 where exam_cnt_20*exam_cnt_21>0 
 order by growth_rate desc,exam_cnt_rank_21 desc
 ;

第二种就是规规矩矩的标准题解啦，也是我这道题的收获，cast函数：

select *,cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
from(
 select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,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 i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20,
count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21
from exam_record r left join examination_info i on r.exam_id=i.exam_id
 group by i.tag
 ) a  )b
 where exam_cnt_20*exam_cnt_21>0
 order by growth_rate desc,exam_cnt_rank_21 desc
 ;

结构清晰，代码精简，高效执行是我写sql的三大追求，诚与诸君共勉。

发表于 2021-11-19 19:26:20 回复(7)

Mysql

酸菜鱼土豆大侠

【场景】：百分比格式

【分类】：专用窗口函数、select条件

分析思路

难点：

长整型的数据类型要求不能有负号产生，用cast函数转换数据类型为signed。

（1）统计2020年、2021年上半年各类试卷的做完次数和做完次数排名

[条件]：if(date(start_time) between '20200101' and '20200630',start_time,null) 和 if(date(start_time) between '20210101' and '20210630',start_time,null)
[使用]：rank() over (order by 做完次数 desc)

（2）计算同期的增长率以及做完次数排名变化，按增长率和21年排名降序输出

[条件]：exam_cnt_21*exam_cnt_20 <> 0
[使用]：order by growth_rate desc, exam_cnt_rank_21 desc

注意：
1.增长率计算公式：(exam_cnt_21-exam_cnt_20)/exam_cnt_20
2.做完次数排名变化（2021年和2020年比排名升了或者降了多少）
3.计算公式：exam_cnt_rank_21-exam_cnt_rank_20
4.条件：exam_cnt_20、exam_cnt_21都不为空
5.长整型的数据类型要求不能有负号产生，所以用cast函数转换数据类型为signed。如果不用cast函数两数相减时一旦产生负数会报错：BIGINT UNSIGNED（长整型数字无符号）。所以要转换数据类型为signed类型，保证产生负数也不会报错。对比增长率，明明相减的时候它也会产生负数，为什么就不用cast处理呢？因为round函数对正数、负数都可以做处理。

最终结果

select 查询结果 [试卷类型;2020年做完次数;2021年做完次数;增长率;2020年做完次数排名;2021年做完次数排名;做完次数排名变化]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [2020年和2021年做完次数不为空]
group by 分组条件 [用户ID;月份]
order by 对查询结果排序 [增长率、21年排名降序];

题解里面隐藏了4种判断 2020年上半年的方法哦

求解代码

方法一

with子句 + 一步步拆解

with 
    main as(
        #2020年上半年各类试卷的做完次数
        select
            tag,
            '2020' as start_year_20,
            count(start_time) as exam_cnt_20
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and 7 > month(start_time)
        and year(start_time) = 2020
        and submit_time is not null
        group by tag
    ),
    attr as(
        #2021年上半年各类试卷的做完次数
        select
            tag,
            '2021' as start_year_21,
            count(start_time) as exam_cnt_21
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and 7 > month(start_time)
        and year(start_time) = 2021
        and submit_time is not null
        group by tag
    ),
    main1 as(
        #2020上半年做完次数排名
        select
            tag,
            start_year_20,
            exam_cnt_20,
            rank() over (order by exam_cnt_20 desc) as exam_cnt_rank_20
        from main
    ),
    attr1 as(
        #2021上半年做完次数排名
        select
            tag,
            start_year_21,
            exam_cnt_21,
            rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21
        from attr
    )

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/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 rank_delta
from main1
join attr1 using(tag)
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

代码优化

将条件 (2020年、2021年上半年) 放在 select 语句中

with 
    main as(
        #2020年、2021年上半年各类试卷的做完次数和做完次数排名
        select
            tag,
            count(if(date(start_time) between '20200101' and '20200630',start_time,null)) as exam_cnt_20,
            count(if(date(start_time) between '20210101' and '20210630',start_time,null)) as exam_cnt_21,
            rank() over (order by count(if(date(start_time) between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20,
            rank() over (order by count(if(date(start_time) between '20210101' and '20210630',start_time,null)) desc) as exam_cnt_rank_21
        from examination_info
        join exam_record using(exam_id)
        where submit_time is not null
        group by tag
    )

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/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 rank_delta
from main
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

main 表输出结果：

1    算法|0|3|4|1    1
2    SQL|3|2|1|2        
3    C++|3|0|1|3        
4    PYTHON|2|0|3|3

方法二

from 子查询

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/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 rank_delta
from(
    #2020年、2021年上半年各类试卷的做完次数和做完次数排名
    select
        tag,
        count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) as exam_cnt_20,
        count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) as exam_cnt_21,
        rank() over (order by count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20,
        rank() over (order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) as exam_cnt_rank_21
    from examination_info
    join exam_record using(exam_id)
    where submit_time is not null
    group by tag
) main
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

发表于 2022-11-11 22:30:00 回复(0)

Mysql

牛客95544686号

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,
        (exam_cnt_rank_21 - exam_cnt_rank_20) as rank_delta
from(
    select tag,
        sum(if(start_year = 2020, exam_cnt, 0)) as exam_cnt_20,
        sum(if(start_year = 2021, exam_cnt, 0)) as exam_cnt_21,
        sum(if(start_year = 2020, exam_cnt_rank, 0)) as exam_cnt_rank_20,
        sum(if(start_year = 2021, exam_cnt_rank, 0)) as exam_cnt_rank_21
    from(
        SELECT tag, 
            year(start_time) as start_year,
            count(submit_time) as exam_cnt,
            rank() over(order by count(submit_time) desc) as exam_cnt_rank
        from exam_record er right join examination_info ei
        on er.exam_id = ei.exam_id
        where year(start_time) = 2020 and month(start_time) <= 6
        group by tag

        union ALL

        SELECT tag, 
            year(start_time) as start_year,
            count(submit_time) as exam_cnt,
            rank() over(order by count(submit_time) desc) as exam_cnt_rank
        from exam_record er right join examination_info ei
        on er.exam_id = ei.exam_id
        where year(start_time) = 2021 and month(start_time) <= 6
        group by tag
    ) as table1
    group by tag
) as table2
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc, exam_cnt_rank_21 desc

发表于 2021-11-09 13:57:01 回复(0)

Mysql

飞翔QWQ

#1.先分步骤走，思路会非常轻松，主要思路写完，
#2.然后逐渐完善细节，自测通过，
#3.提交后继续扣细节最后完成
#当然如果细节能提前想到会更好，如果不能也没事

# 2021年上半年各类试卷的做完次数相比2020年上半年
# 同期的增长率（百分比格式，保留1位小数）
with temp1 as(
select exam_id,
    count(score) exam_cnt_20,
    rank() over(order by count(score) desc) exam_cnt_rank_20
from exam_record
where year(submit_time)=2020 and month(submit_time)<7
group by exam_id
),
    temp2 as(
select exam_id,
    count(score) exam_cnt_21,
    rank() over(order by count(score) desc) exam_cnt_rank_21
from exam_record
where year(submit_time)=2021 and month(submit_time)<7
group by exam_id
)

# 以及做完次数排名变化，
select tag,
        exam_cnt_20,
        exam_cnt_21,
        concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),"%") growth_rate,
        exam_cnt_rank_20,
        exam_cnt_rank_21,
        cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed)  rank_delta
from temp1 join temp2 on temp1.exam_id=temp2.exam_id
            join examination_info e on temp1.exam_id=e.exam_id
order by growth_rate desc,exam_cnt_rank_21 desc
# 按增长率和21年排名降序输出。

发表于 2022-12-14 10:52:33 回复(0)

Mysql

冷凡社长

select tag
,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20
,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_cnt_21
,concat(round((sum(case when start_year =2021 then exam_cnt else 0 end)-sum(case when start_year =2020 then exam_cnt else 0 end))
/sum(case when start_year =2020 then exam_cnt else 0 end)*100,1),'%') as growth_rate
,sum(case when start_year =2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) as exam_cnt_rank_21
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) - sum(case when start_year =2020 then exam_cnt_rank else 0 end) as rank_delta 
from (-- 关键是这里求出完成次数级排名 要使用rank美式排名
    select tag,year(start_time) as start_year,
    count(submit_time) as exam_cnt,
    rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank
    from exam_record t join examination_info t1 using(exam_id)
    where month(start_time)<=6
    group by tag,year(start_time)
) t
group by tag
having exam_cnt_20 >0 and exam_cnt_21>0
order by growth_rate desc,exam_cnt_rank_21 desc

先求出完成次数级排名要使用rank美式排名

然后在这个基础之上，进行大量的case when的操作，新建列。

发表于 2022-08-23 19:10:54 回复(1)

Mysql

不想睡觉zzz

优点麻烦，自测的时候没错误，提交的时候rank_delta时候老是报错，参考了评论里面的大神用的cast，解决了
select a.tag,a.cnt1 exam_cnt_20,b.cnt2 exam_cnt_20,concat(round((b.cnt2 - a.cnt1)*100/a.cnt1,1),'%') growth_rate,
a.t_rank1 exam_cnt_rank_20,b.t_rank2 exam_cnt_rank_21,cast(b.t_rank2 as signed) - cast(a.t_rank1 as signed) as rank_delta from
(select ei.tag,count(er.submit_time) cnt1,
rank() over (order by count(er.submit_time) desc) t_rank1 from exam_record er 
inner join examination_info ei on ei.exam_id = er.exam_id
where date_format(er.submit_time,'%Y%m') between '202001' and '202006'
group by ei.tag) as a inner join 
(select ei.tag,count(er.submit_time) cnt2,
rank() over (order by count(er.submit_time) desc) t_rank2
from exam_record er inner join examination_info ei on ei.exam_id = er.exam_id
where date_format(er.submit_time,'%Y%m') between '202101' and '202106'
group by ei.tag) as b on a.tag = b.tag
group by a.tag
order by growth_rate desc,exam_cnt_rank_21 desc

发表于 2021-11-20 15:25:50 回复(2)

Mysql

牛客504513895号

SELECT *,
cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
FROM
(SELECT a.tag,
COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) AS exam_cnt_20,
COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) AS exam_cnt_21,
CONCAT(ROUND((COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL))-COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)))/COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL))*100,1),'%') AS growth_rate,
RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_20,
RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_21
FROM examination_info a INNER JOIN exam_record b 
ON a.exam_id=b.exam_id
GROUP BY a.tag) t1
WHERE exam_cnt_20*exam_cnt_21>0
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC

发表于 2022-07-13 10:30:55 回复(0)

Mysql

牛客876728443号

select 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 rank_delta
from
   (
    select *
    from 
        (select tag,year(start_time) as 2020_years,count(submit_time) as exam_cnt_20,rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank_20
        from exam_record left join examination_info using(exam_id)
        where year(start_time) = 2020 and month(start_time) <=6 
        group by tag,year(start_time)
        having count(submit_time) != 0 ) a #将2020年上半年需要的数据提取出来
    join
        (select tag,year(start_time) as 2021_years,count(submit_time) as exam_cnt_21,rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank_21
         from exam_record left join examination_info using(exam_id)
         where year(start_time) = 2021 and month(start_time) <=6 
         group by tag,year(start_time) 
         having count(submit_time) != 0 ) b #将2021年上半年需要的数据提取出来
    using(tag)
    ) c #内连接，将两个时期都有的记录提取出来
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC  #按要求计算数值并排序

#

期待大家指出不足之处

发表于 2022-03-28 19:59:31 回复(3)

Mysql

SandyFlyHigh

有大神可以解释下为什么要用CAST(exam_cnt_rank_20 AS signed)？什么情况下需要这个处理呢？

SELECT
    t1.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
    (SELECT
        tag,
        COUNT(submit_time) AS exam_cnt_20,
        RANK() OVER(ORDER BY COUNT(submit_time) DESC) AS exam_cnt_rank_20
    FROM exam_record AS er
    RIGHT JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    WHERE
        YEAR(submit_time) = 2020
        AND
        MONTH(submit_time) < 7
    GROUP BY tag) AS t1
JOIN
    (SELECT
        tag,
        COUNT(submit_time) AS exam_cnt_21,
        RANK() OVER(ORDER BY COUNT(submit_time) DESC) AS exam_cnt_rank_21
    FROM exam_record AS er
    RIGHT JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    WHERE
        YEAR(submit_time) = 2021
        AND
        MONTH(submit_time) < 7
    GROUP BY tag) AS t2
ON t1.tag = t2.tag
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC

发表于 2021-11-27 12:58:46 回复(6)

Mysql

War张

with t as (
    select
        tag,
        count(if(date_format(submit_time,'%Y-%m') between '2020-01' and '2020-06',submit_time,null)) exam_cnt_20,
        count(if(date_format(submit_time,'%Y-%m') between '2021-01' and '2021-06',submit_time,null)) exam_cnt_21,
        concat(round((count(if(date_format(submit_time,'%Y-%m') between '2021-01' and '2021-06',submit_time,null)) / 
                      count(if(date_format(submit_time,'%Y-%m') between '2020-01' and '2020-06',submit_time,null)) - 1) * 100,1),'%') growth_rate,
        rank() over(order by count(if(date_format(submit_time,'%Y-%m') between '2020-01' and '2020-06',submit_time,null)) desc) exam_cnt_rank_20,
        rank() over(order by count(if(date_format(submit_time,'%Y-%m') between '2021-01' and '2021-06',submit_time,null)) desc) exam_cnt_rank_21,
        cast(rank() over(order by count(if(date_format(submit_time,'%Y-%m') between '2021-01' and '2021-06',submit_time,null)) desc) as signed) -
        cast(rank() over(order by count(if(date_format(submit_time,'%Y-%m') between '2020-01' and '2020-06',submit_time,null)) desc) as signed) rank_delta
    from examination_info ei
    join exam_record er
    on ei.exam_id = er.exam_id
    group by tag
)
select * from t 
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc,exam_cnt_rank_21 desc;

发表于 2023-10-31 13:35:49 回复(0)

牛客563312411号

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select tag,
       exam_cnt_20,
			 exam_cnt_21,
			 concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
			 exam_cnt_rank_20,
			 exam_cnt_rank_21,
			 cast(exam_cnt_rank_21 as signed )-cast(exam_cnt_rank_20 as signed) rank_deIta
from (
			select tag,
			       count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) exam_cnt_20,
			       count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) exam_cnt_21, 
			       rank() over(order by count(if(date_format(start_time,'%Y%md') between '20200101' and '20200630',start_time,null)) desc) exam_cnt_rank_20,
			       rank() over(order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) exam_cnt_rank_21 
			from exam_record 
			join examination_info using(exam_id)
			where score is not null  
			group by tag 
									) t 
where exam_cnt_20*exam_cnt_21 <>0 
order by 4 desc,6 desc

2.signed有符号整数，可表示正、负整数和0，用cast(……as signed)把排名转化成有符号整数（原因：rank_delta的数值类型是unsigned（也就是无符号，无正负之分），但是如果rank_delta的值是负数，就会矛盾、报错；所以要把数值类型从unsigned改为signed）

year(start_time)='2020'and month(start_time) between 1 and 6

year(start_time)='2021'and month(start_time)<7

select 
tag,
exam_cnt_20,
exam_cnt_21,
concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
exam_cnt_rank_20,
exam_cnt_rank_21,
cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta
from 
(select 
tag,
count(if(year(start_time)='2020'and month(start_time) between 1 and 6,score,null)) exam_cnt_20,
count(if(year(start_time)='2021'and month(start_time)<7,score,null)) exam_cnt_21,
rank()over(order by (count(if(year(start_time)='2020' and month(start_time)<7,score,null))) desc) exam_cnt_rank_20,
rank()over(order by (count(if(year(start_time)='2021' and month(start_time)<7,score,null))) desc) exam_cnt_rank_21
from examination_info
join exam_record
using(exam_id)
where score is not null
group by 1) t 
where exam_cnt_20* exam_cnt_21!=0
order by 4 desc,6 desc

编辑于 2023-12-19 18:27:25 回复(0)

孤独的西红柿在打卡

with a as

(select tag,

year(submit_time) as start_year,

count(submit_time) as exam_cnt,

rank()over(partition by year(submit_time) order by count(submit_time) desc) as rk

from exam_record er

left join examination_info ei using(exam_id)

where month(submit_time)<=6

group by tag, start_year

)

select a1.tag,

a1.exam_cnt as exam_cnt_20,

a2.exam_cnt as exam_cnt_21,

concat(round((a2.exam_cnt-a1.exam_cnt)*100/a1.exam_cnt,1),'%') as growth_rate,

a1.rk as exam_cnt_rank_20,

a2.rk as exam_cnt_rank_21,

cast(a2.rk as signed)-cast(a1.rk as SIGNED) as rank_delta

from a a1

join a a2 on a2.start_year = a1.start_year +1 and a1.tag = a2.tag

order by growth_rate desc, a2.rk desc

发表于 2023-07-09 13:16:05 回复(0)

Mysql

牛客332799475号

with e_e as (
    select er.exam_id, tag, score, start_time, submit_time
    from exam_record as  er left join examination_info as ei
    on er.exam_id = ei.exam_id
)
select t21.tag
, exam_cnt_20
, exam_cnt_21
, concat(round(100*(exam_cnt_21 - exam_cnt_20)/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  rank_delta
from 
(
select tag
, count(score) as exam_cnt_20
, rank() over(order by count(score) desc) exam_cnt_rank_20
from e_e
where date_format(submit_time,'%Y%m') between '202001' and '202006'
and submit_time is not null
group by tag) as t20 inner join 
(
select tag
, count(score) as exam_cnt_21
, rank() over(order by count(score) desc) exam_cnt_rank_21
from e_e
where date_format(submit_time,'%Y%m') between '202101' and '202106'
and submit_time is not null
group by tag) as t21  on t20.tag = t21.tag
order by growth_rate desc, exam_cnt_21 asc;

思路：

1. 先将exam_record表与examination_info表两表绑定

2. 再分别根据submit_time 年月条件，和submite_time非空 not null 生成表t20 和表t21; 两表分别对应的列有

t20: tag、exam_cnt_20、exam_cnt_rank_20

t21: tag、exam_cnt_21、exam_cnt_rank_21

3. 最后将两表合并，条件设置。由条件易知应该使用 inner join 来连接 t20 和 t21；并抽取列

t21.tag、exam_cnt_20、exam_cnt_21、growth_rate、 exam_cnt_rank_20、 exam_cnt_rank_21、rank_delta

其中 growth_rate和rank_delta要另外计算得到

growth_rate = concat(round(100*(exam_cnt_21 - exam_cnt_20)/exam_cnt_20,1),'%')

rank_delta = (cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed))

两个函数中要注意的点是mysql好像不支持百分数计算，需要使用concat函数拼接

rank_delta函数中的两个年度变量的rank是一个unsigned（无符号数值，即只能

为正数）,需要使用cast(rank as signed) 调整为带符号数值，才能避免“BIGINT UNSIGNED value is out of range”

发表于 2023-03-15 15:24:43 回复(0)

Mysql

牛客734478131号

WITH t0 AS
(SELECT tag,
        year(submit_time) yr,
        count(score) cnt,
        rank() over (partition by year(submit_time) order by count(score) desc) rk
 FROM examination_info a INNER JOIN exam_record b
      ON a.exam_id=b.exam_id
 WHERE month(submit_time)<=6
 GROUP BY tag,year(submit_time)
 HAVING cnt!=0)

SELECT t1.tag,
       t1.cnt exam_cnt_20,
       t2.cnt exam_cnt_21,
       concat(round((t2.cnt-t1.cnt)*100/t1.cnt,1),'%') growth_rate,
       t1.rk exam_cnt_rank_20,
       t2.rk exam_cnt_rank_21,
       (ifnull(t2.rk,0)-ifnull(t1.rk,0)) rank_delta
FROM (SELECT * FROM t0 WHERE yr=2020) t1
     INNER JOIN (SELECT * FROM t0 WHERE yr=2021) t2
     ON t1.tag=t2.tag
ORDER BY 4 desc,6 desc;

第18行这个ifnull愁死我了,找好久才找到哪里错了

发表于 2023-02-27 17:48:58 回复(1)

寂寞城市又再探戈

with t2 as(

select t1.*,

rank()over(partition by start_year order by exam_cnt desc) exam_cnt_rank

from (

select tag,

year(start_time) start_year,

count(score) exam_cnt

from exam_record a join

examination_info b using(exam_id)

where month(start_time)<7

group by tag,year(start_time)

having count(score) != 0

)t1

)

select tag,

max(case when start_year=2020 then exam_cnt else 0 end) exam_cnt_20,

max(case when start_year=2021 then exam_cnt else 0 end) exam_cnt_21,

concat((round((max(case when start_year=2021 then exam_cnt else 0 end)-max(case when start_year=2020 then exam_cnt else 0 end))/max(case when start_year=2020 then exam_cnt else 0 end)*100,1)),'%') growth_rate,

max(case when start_year=2020 then exam_cnt_rank else 0 end) exam_cnt_rank_20,

max(case when start_year=2021 then exam_cnt_rank else 0 end) exam_cnt_rank_21,

(max(case when start_year=2021 then exam_cnt_rank else 0 end)-max(case when start_year=2020 then exam_cnt_rank else 0 end)) rank_delta

from t2

group by tag

having exam_cnt_20 != 0 and exam_cnt_21 != 0

order by growth_rate desc,exam_cnt_rank_21 desc

发表于 2023-01-16 17:43:04 回复(1)

兔霸哥

附加题：修改下面这段代码，使之运行准确

select 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 rank_delta
from (
    select *,
    rank()over(partition by tag order by exam_cnt_20 desc) exam_cnt_rank_20,
    rank()over(partition by tag order by exam_cnt_21 desc) exam_cnt_rank_21
    from(
        (select tag,
        count(submit_time) as exam_cnt_20
        from exam_record
        join examination_info using(exam_id)
        where year(submit_time)=2020 and month(submit_time) in (1,6)  ) as a
        cross join
        (select tag,
        count(submit_time) as exam_cnt_21
        from exam_record
        join examination_info using(exam_id)
        where year(submit_time)=2021 and month(submit_time) in (1,6)  ) as b  
        using(tag) 
        )    as c
where exam_cnt_20 !=0 and exam_cnt_21 !=0  
order by growth_rate desc, exam_cnt_rank_21 desc

发表于 2022-09-27 10:24:15 回复(2)

Mysql

lool.

WITH t AS 
	(SELECT # 2020 和 2021 上半年 各科完成次数情况
			YEAR(submit_time) as exam_year
			,tag
			,COUNT(submit_time) as exam_cnt
		FROM exam_record left JOIN examination_info USING (exam_id)
		WHERE MONTH(submit_time) BETWEEN 1 and 6
		GROUP BY exam_year ,tag
	)

SELECT #在t2表基础上求 增长率，rank变化
	tag
	,exam_cnt_20
	,exam_cnt_21
	,CONCAT( ROUND((exam_cnt_21/exam_cnt_20 - 1)*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
	(
	SELECT #在t1表基础上 按tag 分2020和2021 取cnt 和rk
		*
		,FIRST_VALUE(exam_cnt) over (PARTITION by tag order by exam_year asc) as exam_cnt_20
		,lead(exam_cnt,1,null) over (PARTITION by tag order by exam_year asc) as exam_cnt_21
		,FIRST_VALUE(rk) over (PARTITION by tag order by exam_year asc) as exam_cnt_rank_20
		,lead(rk,1,null) over (PARTITION by tag order by exam_year asc) as exam_cnt_rank_21
	FROM
		(
		SELECT #在t表基础上 增加完成次数排名
			exam_year 
			,tag
			,exam_cnt
			,RANK() over (PARTITION by exam_year order by exam_cnt desc) as rk
		FROM t) as t1
	) as t2
WHERE exam_cnt_21 is not null
ORDER BY  growth_rate desc, exam_cnt_rank_21 desc

发表于 2022-09-03 11:24:36 回复(0)

Mysql

鱼鱼得水

简简单单内连接

坑就坑在题目里数据有个bug ，表exam_record的id为10的数据提交时间为2020年，开始时间为2021年😅😅

with t0 as (
select 
	tag,
	year(start_time) as start_year,
    count(submit_time) as exam_cnt,
    rank() over (partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank
from exam_record join examination_info using(exam_id)
where 
	(date_format(start_time,'%Y%m') between 202001 and 202006 and date_format(submit_time,'%Y%m') between 202001 and 202006)
	or (date_format(start_time,'%Y%m') between 202101 and 202106 and date_format(submit_time,'%Y%m') between 202101 and 202106)
group by tag, year(start_time)
order by year(start_time)
		)
select 
	t1.tag,
	t1.exam_cnt as exam_cnt_2020,
    t2.exam_cnt as exam_cnt_2021,
    concat(round((t2.exam_cnt - t1.exam_cnt)/t1.exam_cnt*100,1),'%')  as growth_rate,
    t1.exam_cnt_rank as exam_cnt_rank_20,
    t2.exam_cnt_rank as exam_cnt_rank_21,
    cast(t2.exam_cnt_rank as signed) - cast(t1.exam_cnt_rank as signed) as rank_detla
from t0 as t1 ,t0 as t2
where t1.tag = t2.tag and t1.start_year = t2.start_year-1
order by growth_rate desc , exam_cnt_rank_21 desc;

发表于 2022-08-03 16:01:16 回复(1)

提交观点

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

输入

输出

分析思路

题解里面隐藏了4种判断 2020年上半年的方法哦

求解代码

问题信息

热门推荐

通过挑战的用户

相关试题

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

输入

输出

分析思路

题解里面隐藏了4种判断 2020年上半年的 方法哦

求解代码

问题信息

热门推荐

通过挑战的用户

相关试题

题解里面隐藏了4种判断 2020年上半年的方法哦