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

#第一步：格式化日期

SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info

#第二步：窗口函数：统计各年月简历数量

SELECT DISTINCT job,year_mon,
    SUM(num) over(PARTITION BY job, year_mon) "cnt"
FROM
    (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t

#第三步:构造第一个年月的订单统计表

SELECT DISTINCT job,CASE  WHEN LEFT(year_mon,4) = "2025" THEN year_mon  END "first_year_mon", cnt "first_year_cnt"
FROM
    (SELECT DISTINCT job,year_mon,
        SUM(num) over(PARTITION BY job, year_mon) "cnt"
    FROM
        (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt

#第四步：构造第二个年月的订单统计表

SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4)="2026" THEN year_mon END "second_year_mon", cnt "second_year_cnt"
FROM
    (SELECT DISTINCT job,year_mon,
        SUM(num) over(PARTITION BY job, year_mon) "cnt"
    FROM
        (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt

#第五步：将第三步和第四步的表合并

SELECT f1.job, f1.first_year_mon,f1.first_year_cnt, f2.second_year_mon, f2.second_year_cnt
FROM
    (SELECT DISTINCT job,CASE  WHEN LEFT(year_mon,4) = "2025" THEN year_mon  END "first_year_mon", cnt "first_year_cnt"
    FROM
        (SELECT DISTINCT job,year_mon,
            SUM(num) over(PARTITION BY job, year_mon) "cnt"
        FROM
            (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt) f1
    JOIN 
    (SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4)="2026" THEN year_mon END "second_year_mon", cnt "second_year_cnt"
    FROM
        (SELECT DISTINCT job,year_mon,
            SUM(num) over(PARTITION BY job, year_mon) "cnt"
        FROM
            (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt) f2
    ON f1.job = f2.job 
WHERE LEFT(f1.first_year_mon,4) = "2025" AND LEFT(f2.second_year_mon,4) = "2026"
AND RIGHT(f1.first_year_mon,2) = RIGHT(f2.second_year_mon,2)
ORDER BY f1.first_year_mon DESC, f1.job DESC