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

本题可拆解为两步：

• 分别得到2025、2026年的数据
• join连接同种job及相同month的数据

  2025、2026的数据

  代码与上题相同

  select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
  from resume_info
  where year(date)=2025  -- 2025/2026
  group by job,mon
  order by mon desc,cnt desc

  连接

  方法一:日期差为一年

• period_diff(date1,date2) 可作月份之差
  其中date1 和 date2 参数是只能是YYYYMM或YYMM，故还需得到这种格式的月份变量，也因此不能使用聚合函数，可改为聚合窗口函数
  完整代码如下：

  select distinct q1.job,first_year_mon,first_year_cnt,
  second_year_mon,second_year_cnt
  from 
  (select job,date_format(date,'%Y-%m') as first_year_mon,
  date_format(date,'%Y%m') as mon,
  sum(num) over (partition by job,date_format(date,'%Y-%m'))
               as first_year_cnt
  from resume_info
  where year(date)=2025) as q1
  join
  (select job,date_format(date,'%Y-%m') as second_year_mon,
  date_format(date,'%Y%m') as mon,
  sum(num) over (partition by job,date_format(date,'%Y-%m'))
          as second_year_cnt
  from resume_info
  where year(date)=2026) as q2
  on q1.job=q2.job and period_diff(q2.mon,q1.mon)=12
  order by first_year_mon desc,job desc

  易错点：由于内层表格未用聚合函数，外层取变量时要去重！！！

  方法二：字符串截取

• right(string,n)
  两年同月份——两表中的mon后两个字符相同——right(mon,2)
  完整代码如下：

  select q1.job,first_year_mon,first_year_cnt,
  second_year_mon,second_year_cnt
  from 
  (select job,date_format(date,'%Y-%m') as first_year_mon,
  sum(num) first_year_cnt
  from resume_info
  where year(date)=2025
  group by job,first_year_mon) as q1
  join
  (select job,date_format(date,'%Y-%m') as second_year_mon,
  sum(num) as second_year_cnt
  from resume_info
  where year(date)=2026
  group by job,second_year_mon) as q2
  on q1.job=q2.job and right(q1.first_year_mon,2)=right(q2.second_year_mon,2)
  order by first_year_mon desc,job desc