题解 | 分析每个员工在不同项目中的绩效情况

WITH
t1 AS (     -- 关联两表清洗数据，并计算出每个员工上半年的绩效总分
SELECT
    yg.employee_id,
    yg.employee_name,
    yg.department,
    xm.project_name AS project_group,
    jx.project_id,
    ROUND(COALESCE(SUM(jx.performance_score),0),0) AS first_half_2024_score
FROM employees AS yg
LEFT JOIN performance AS jx
    ON yg.employee_id = jx.employee_id
LEFT JOIN projects AS xm
    ON jx.project_id = xm.project_id
WHERE xm.start_date >= '2024-01-01' AND xm.start_date < '2024-07-01'
GROUP BY yg.employee_id,yg.employee_name,yg.department,xm.project_name,jx.project_id
)

SELECT      -- 用窗口函数算出员工在所属项目的绩效排名后依题意输出数据
    employee_id,
    employee_name,
    first_half_2024_score,
    RANK() OVER(PARTITION BY project_group ORDER BY first_half_2024_score DESC) AS project_group_rank,
    department,
    project_group
FROM t1
ORDER BY project_id,project_group_rank,employee_id;