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

/* 请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下


2025年投递简历的每个岗位，每一个月内收到简历的数目
和对应的2026年的同一个月同岗位，收到简历的数目
最后的结果先按first_year_mon月份降序，再按job降序排序显示


拆开两年，每年以月份分组，组内加和，两表拼接
*/

with resume_info_m
as
(
select
*
,date_format(date,'%Y-%m') as date_m
,date_format(date,'%m') as date_m_pure
from
resume_info
)
,2025r as
(
select
*
,sum(num)over(partition by job, date_m) as sum_mon_2025
from
resume_info_m
where date_m between '2025-01' and '2025-12'
)
,2026r as
(
select
*
,sum(num)over(partition by job, date_m) as sum_mon_2026
from
resume_info_m
where date_m between '2026-01' and '2026-12'
)
,2025r_d as
(
select 
distinct job,date_m,sum_mon_2025,date_m_pure
#distinct+多字段，其会将字段拼接起来，只筛选唯一字段，此题需要无重复的job和月份的笛卡尔积
from 2025r
)
,2026r_d as
(
select 
distinct job,date_m,sum_mon_2026,date_m_pure
from 2026r
)

select
r1.job as job
,r1.date_m as first_year_mon
,r1.sum_mon_2025 as first_year_cnt
,r2.date_m as second_year_mon
,r2.sum_mon_2026 as second_year_cnt
from 2025r_d r1 left join 2026r_d r2
on r1.job = r2.job and r1.date_m_pure=r2.date_m_pure
order by first_year_mon desc, job desc