题解 | #实习广场投递简历分析(三)#

with t as
(select distinct 
    ri.job,
    DATE_FORMAT(ri.date,'%Y-%m') as d, 
    sum(ri.num) over (partition by ri.job, DATE_FORMAT(ri.date,'%Y-%m')) as cnt,
    DATE_FORMAT(ri.date,'%m') as dd
from resume_info ri
where ri.date >= '2025-01-01' and ri.date < '2027-01-01'
order by d DESC, cnt DESC)

select 
    t1.job,
    t1.d as first_year_mon,
    t1.cnt as first_year_cnt,
    t2.d as second_year_mon,
    t2.cnt as second_year_cnt
from t t1
inner join t t2
on t2.dd = t1.dd and t1.job = t2.job
where t1.d < '2026-01' and t2.d >= '2026-01'
order by t1.d DESC, t1.job DESC