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

#难点在于两表如何连接.二刷：1.month()只接受数据类型为时间类型的值，而date_format()输出的值是字符串

with 2025y as
(
select
job
,date
,date_format(date,'%Y-%m') as mon
,num
from resume_info
where year(date) = 2025
)
, 2025y_s as
(
select
*
,sum(num)over(partition by job,mon) as su1
from 2025y
)
, 2026y as
(
select
job
,date
,date_format(date,'%Y-%m') as mon
,num
from resume_info
where year(date) = 2026
)
, 2026y_s as
(
select
*
,sum(num)over(partition by job,mon) as su2
from 2026y
)



select
distinct s1.job as job
,s1.mon as first_year_mon
,s1.su1 as first_year_cnt
,s2.mon as second_year_mon
,s2.su2 as second_year_cnt
from 2025y_s s1 left join 2026y_s s2
on s1.job=s2.job and month(s1.date)=month(s2.date)
order by first_year_mon desc, job desc