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

# 字符串截取，将年份、月份、年+月分别截取出来，根据年份将数据分为2025和2026,分别根据job和年月分组，聚合简历数
# 将2025和2026的分组聚合结果，根据job和月份做join键，join起来，再根据题目意思排序即可


with t_2025 as 
(
select job
      ,SUBSTR(date,1,7) as first_year_mon
	  ,SUBSTR(date,6,2) as first_mon
	  ,sum(num) as first_year_cnt
from resume_info
where SUBSTR(date,1,4)='2025'
group by job
        ,SUBSTR(date,1,7)
	    ,SUBSTR(date,6,2)
),
t_2026 as 
(
select job
      ,SUBSTR(date,1,7) as second_year_mon
	  ,SUBSTR(date,6,2) as second_mon
	  ,sum(num) as second_year_cnt
from resume_info
where SUBSTR(date,1,4)='2026'
group by job
        ,SUBSTR(date,1,7)
		,SUBSTR(date,6,2)
)
select t_2025.job
      ,first_year_mon
	  ,first_year_cnt
	  ,second_year_mon
	  ,second_year_cnt
from t_2025 
inner join t_2026
on t_2025.first_mon=t_2026.second_mon
and t_2025.job=t_2026.job
order by first_year_mon desc,job desc