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

with t1 as (
    select tag,
    count(if(year(submit_time) = 2020 and month(submit_time) <= 6,1,null)) exam_cnt_20,
    count(if(year(submit_time) = 2021 and month(submit_time) <= 6,1,null)) exam_cnt_21
    from exam_record
    left join examination_info using(exam_id)
    group by tag
),

t2 as (
    select tag,exam_cnt_20,exam_cnt_21,
    rank() over(order by exam_cnt_20 desc) exam_cnt_rank_20,
    rank() over(order by exam_cnt_21 desc) exam_cnt_rank_21
    from t1
)

select tag,exam_cnt_20,exam_cnt_21, growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,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,
    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 t2
) t3
where exam_cnt_20 > 0 and exam_cnt_21 > 0
order by growth_rate desc, exam_cnt_rank_21 desc

SELECT
    tag
    ,exam_cnt_20
    ,exam_cnt_21
    ,growth_rate
    ,exam_cnt_rank_20
    ,exam_cnt_rank_21
    ,rank_delta
FROM
(
SELECT
    tag
    ,start_year
    ,lag(exam_cnt,1)over(partition by tag order by start_year) as exam_cnt_20
    ,exam_cnt as exam_cnt_21
    ,concat(round((exam_cnt/lag(exam_cnt,1)over(partition by tag order by start_year)-1)*100,1),"%") as growth_rate
    ,lag(exam_cnt_rank,1)over(partition by tag order by start_year) as exam_cnt_rank_20
    ,exam_cnt_rank as exam_cnt_rank_21
    ,cast(exam_cnt_rank AS SIGNED)-cast(lag(exam_cnt_rank,1)over(partition by tag order by start_year)AS SIGNED) as rank_delta
FROM
(
SELECT
    tag
    ,2020 as start_year
    ,count(score) as exam_cnt
    ,rank()over(order by count(score) desc) as exam_cnt_rank
FROM examination_info t1
LEFT JOIN exam_record t2 
ON t1.exam_id = t2.exam_id
WHERE date_format(submit_time,"%Y-%m-%d") between "2020-01-01" and "2020-07-01"
GROUP BY tag,start_year
UNION ALL
SELECT
    tag
    ,2021 as start_year
    ,count(score) as exam_cnt
    ,rank()over(order by count(score) desc) as exam_cnt_rank
FROM examination_info t1
LEFT JOIN exam_record t2 
ON t1.exam_id = t2.exam_id
WHERE date_format(submit_time,"%Y-%m-%d") between "2021-01-01" and "2021-07-01"
GROUP BY tag,start_year
)t1)t2
WHERE start_year = 2021 AND growth_rate is not null
ORDER BY growth_rate desc,rank_delta desc

with t as (
    select
        tag,
        count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) exam_cnt_20,
        count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) exam_cnt_21,
        rank() over(order by count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_20,
        rank() over(order by count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_21
    from examination_info ei 
    join exam_record er
    on ei.exam_id = er.exam_id
    group by tag
)
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round((exam_cnt_21 - exam_cnt_20) * 100 / exam_cnt_20,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 t
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc,exam_cnt_rank_21 desc;

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 (
    select
        t2.tag
        ,sum(if(date_format(t1.start_time, '%Y-%m') between '2020-01' and '2020-06' and t1.submit_time is not null, 1, 0)) as exam_cnt_20
        ,sum(if(date_format(t1.start_time, '%Y-%m') between '2021-01' and '2021-06' and t1.submit_time is not null, 1, 0)) as exam_cnt_21
        ,rank() over(order by sum(if(date_format(t1.start_time, '%Y-%m') between '2020-01' and '2020-06' and t1.submit_time is not null, 1, 0)) desc) as exam_cnt_rank_20
        ,rank() over(order by sum(if(date_format(t1.start_time, '%Y-%m') between '2021-01' and '2021-06' and t1.submit_time is not null, 1, 0)) desc) as exam_cnt_rank_21
    from exam_record as t1
    left join examination_info as t2
        on t1.exam_id = t2.exam_id
    group by
        t2.tag
) as t1
where exam_cnt_20 * exam_cnt_21 != 0
order by
     growth_rate desc
    ,exam_cnt_rank_21 desc

with t1 as(
select tag, '2020' year, count(score) 2020_count, rank()over(order by count(score) desc) rk1
from exam_record left join examination_info using(exam_id)
where year(start_time)=2020 and month(start_time)<=6
group by tag
)
,t2 as(
select tag, '2021' year, count(score) 2021_count, rank()over(order by count(score) desc) rk2
from exam_record left join examination_info using(exam_id)
where year(start_time)=2021 and month(start_time)<=6
group by tag
)
select tag, 2020_count, 2021_count, concat(round(((2021_count-2020_count)/2020_count)*100,1),"%") growth_rate, rk1, rk2, cast(rk2 as signed)-cast(rk1 as signed) rk_delta
from t1 join t2 using(tag)
where 2021_count!=0 and 2020_count!=0
order by growth_rate desc,rk_delta desc

select t1.tag
    ,exam_cnt_2020
    ,exam_cnt_2021
    ,concat(round((exam_cnt_2021-exam_cnt_2020)/exam_cnt_2020*100,1),'%') as growth_rate
    ,exam_cnt_rank_20
    ,exam_cnt_rank_21
    ,if(exam_cnt_rank_21>=exam_cnt_rank_20,exam_cnt_rank_21-exam_cnt_rank_20,-(exam_cnt_rank_20-exam_cnt_rank_21)) as rank_delta
from 
    (   select tag,count(tag) as exam_cnt_2021
              ,rank()over(order by count(tag) desc ) as exam_cnt_rank_21
        from exam_record as r 
        join examination_info as e 
        on r.exam_id=e.exam_id
        where score is not null and date_format(start_time,'%Y-%m')<='2021-06'
        and date_format(start_time,'%Y-%m')>='2021-01'
        group by tag 
    ) t1 
join 
    (
        select tag,count(tag) as exam_cnt_2020
            ,rank()over(order by count(tag) desc) as exam_cnt_rank_20
        from exam_record as r 
        join examination_info as e 
        on r.exam_id=e.exam_id
        where score is not null and date_format(start_time,'%Y-%m')<='2020-06'
        and date_format(start_time,'%Y-%m')>='2020-01'
        group by tag    
    ) t2 
on t1.tag=t2.tag
order by growth_rate desc,exam_cnt_rank_21 desc

##1.计算2020年和2021上半年tag,exam_cnt_20,exam_cnt_rank_20
with t1 as (
    SELECT *,rank() over ( order by exam_cnt_20 DESC) as exam_cnt_rank_20
    from (
SELECT tag,count(score) as exam_cnt_20
FROM exam_record JOIN examination_info using(exam_id)
WHERE DATE_FORMAT(submit_time,'%Y%m%d') between '20200101' and '20200631'
group by tag) table1
inner join 
(
 SELECT *,rank() over (order by exam_cnt_21 DESC) as exam_cnt_rank_21
    from (
SELECT tag,count(score) as exam_cnt_21
FROM exam_record JOIN examination_info using(exam_id)
WHERE DATE_FORMAT(submit_time,'%Y%m%d') between '20210101' and '20210631'
group by tag) table2) table3 using(tag)
)
SELECT tag,exam_cnt_20,exam_cnt_21,
concat(round(100*(cast(exam_cnt_21 as signed) - cast(exam_cnt_20 as signed))/cast(exam_cnt_20 as signed),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 t1
order by growth_rate DESC,exam_cnt_rank_21 DESC;

不知道为什么 这样算总是排名和正确答案对不上？

SELECT
    tag,
    MAX(IF(year = 2020, exam_cnt, 0)) AS exam_cnt_20,
    MAX(IF(year = 2021, exam_cnt, 0)) AS exam_cnt_21,
    CONCAT(
        ROUND((MAX(IF(year = 2021, exam_cnt, 0)) - MAX(IF(year = 2020, exam_cnt, 0))) / MAX(IF(year = 2020, exam_cnt, 0)) * 100, 1), '%'
    ) AS growth_rate,
    MAX(IF(year = 2020, exam_cnt_rank, 0)) AS exam_cnt_rank_20,
    MAX(IF(year = 2021, exam_cnt_rank, 0)) AS exam_cnt_rank_21,
    MAX(IF(year = 2021, exam_cnt_rank, 0)) - MAX(IF(year = 2020, exam_cnt_rank, 0)) AS rank_delta
FROM
    (
        SELECT
            tag,
            YEAR(submit_time) AS year,
            COUNT(*) AS exam_cnt,
            RANK() OVER (PARTITION BY YEAR(submit_time) ORDER BY COUNT(*) DESC) AS exam_cnt_rank
        FROM
            exam_record
            LEFT JOIN examination_info USING (exam_id)
        WHERE
            MONTH(submit_time) <= 6
        GROUP BY
            tag, YEAR(submit_time)
    ) t
GROUP BY
    tag
HAVING
    COUNT(DISTINCT year) = 2
ORDER BY
    growth_rate DESC, exam_cnt_rank_21 DESC

select
    tmp1.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 (
-- 2020上半年完成数和排名
select
    t2.tag as tag,
    count(t1.score) as exam_cnt_20,
    cast(rank() over (order by count(t1.score) desc) as signed) as exam_cnt_rank_20
from exam_record as t1
left join examination_info as t2
    on t1.exam_id = t2.exam_id
where date_format(t1.start_time, '%Y-%m') between '2020-01' and '2020-06'
group by t2.tag
having count(t1.score)<>0
) as tmp1
-- 用inner join，需要该tag在两个时段之内都要有作答数据
join (
-- 2021上半年的完成数和排名
select
    t2.tag as tag,
    count(t1.score) as exam_cnt_21,
    cast(rank() over (order by count(t1.score) desc) as signed) as exam_cnt_rank_21
from exam_record as t1
left join examination_info as t2
    on t1.exam_id = t2.exam_id
where date_format(t1.start_time, '%Y-%m') between '2021-01' and '2021-06'
group by t2.tag
having count(t1.score)<>0
) as tmp2
    on tmp1.tag = tmp2.tag
order by growth_rate desc, exam_cnt_rank_21 desc;

with t1 as
(select tag,year(submit_time) year2,month(submit_time) month2
from exam_record e1
left join examination_info e2
on e1.exam_id=e2.exam_id)

select 
a.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,year2 year1,count(year2) exam_cnt_21,rank()over(order by count(year2) desc) exam_cnt_rank_21
from t1
where year2=2021 and month2<=6
group by tag) a
join 
(select tag,year2 year2,count(year2) exam_cnt_20,rank()over(order by count(year2) desc) exam_cnt_rank_20
from t1
where year2=2020 and month2<=6
group by tag) b
on a.tag=b.tag
order by growth_rate desc,exam_cnt_rank_21 desc