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

#岗位+25每月简历数+26每月简历数 按25每月简历数降序job降序
#2025年

select
    distinct
    b.job
     ,first_year_month
     ,first_year_cnt
     ,second_year_month
     ,second_year_cnt
from (
         select
             distinct
             job
              ,mon first_year_month
              ,sum(num)over(partition by job,mon) first_year_cnt#每月简历数
         from (
                  select
                      job
                       ,date_format(date,'%Y-%m') mon#新增列年+月
                       ,num
                  from resume_info
                  where date>date_sub('2025-01-01',interval 1 day)
                    and date<date_add('2027-12-31',interval 1 day)#2025-2026
              ) a
         where left(mon,4)=2025#2025年表
     ) b
         join (
                select
                    distinct
                    job
                     ,mon second_year_month
                    ,date_format(date_sub(date,interval 1 year),'%Y-%m') y#辅助列对齐年月
                     ,sum(num)over(partition by job,mon) second_year_cnt#每月简历数
                from (
                         select
                             job
                              ,date
                              ,date_format(date,'%Y-%m') mon#新增列年+月
                              ,num
                         from resume_info
                         where date>date_sub('2025-01-01',interval 1 day)
                           and date<date_add('2027-12-31',interval 1 day)#2025-2026
                     ) c
                where left(mon,4)=2026#2026年表
                ) d
              on d.job=b.job
                and d.y=b.first_year_month#辅助对齐年月
order by first_year_month desc,b.job desc#按25每月简历数降序job降序