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

select t2025.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
from(
select job,first_year_mon,2025_month,max(first_year_cnt) over (partition by first_year_mon,job) as first_year_cnt
from(
select distinct job,date as first_year_mon,2025_month,num as first_year_cnt
from(
select job,DATE_FORMAT(date, '%Y-%m') as date,month(date) as 2025_month,
sum(num) over (partition by job,DATE_FORMAT(date, '%Y-%m')) as num 
from resume_info
) t1
) t2
where substring(first_year_mon,1,4) = 2025) t2025
join
(
select job,second_year_mon,2026_month,max(second_year_cnt) over (partition by second_year_mon,job) as second_year_cnt
from(
select distinct job,date as second_year_mon,2026_month,num as second_year_cnt
from(
select job,DATE_FORMAT(date, '%Y-%m') as date,month(date) as 2026_month,
sum(num) over (partition by job,DATE_FORMAT(date, '%Y-%m')) as num 
from resume_info
) t1
) t2
where substring(second_year_mon,1,4) = 2026) t2026
on t2025.job = t2026.job and t2025.2025_month = t2026.2026_month
order by first_year_mon desc,job desc