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

select a.job,a.date as first_year_mon,a.hj as first_year_cnt,b.date as second_year_mon,b.hj2 as second_year_cnt
from
(select distinct job,date,coalesce(first_year_mon,second_year_mon) as hj   -- 2025年
from (
    select job,substr(date,1,7) as date,sum(case when substr(date,1,4) = '2025' then num end) over(partition by job,substr(date,1,7)) as first_year_mon,sum(case when substr(date,1,4) = '2026' then num end) over(partition by job,substr(date,1,7)) as second_year_mon
    from resume_info
    where year(date) in (2025)) a ) a
join (
    select distinct job,date,coalesce(first_year_mon,second_year_mon) as hj2  -- 2026 年
from (
    select job,substr(date,1,7) as date,sum(case when substr(date,1,4) = '2025' then num end) over(partition by job,substr(date,1,7)) as first_year_mon,sum(case when substr(date,1,4) = '2026' then num end) over(partition by job,substr(date,1,7)) as second_year_mon
    from resume_info
    where year(date) in (2026)) b
) b
on a.job = b.job and substr(a.date,-2) = substr(b.date,-2)
order by substr(a.date,-2) desc,a.job desc