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

/*处理2024上半年*/
with
    project as (
        select
            *
        from
            projects
        where
            substring(start_date, 1, 4) = "2024"
            /*字符串带引号*/
            and (
                substring(start_date, 6, 2) between "01" and "06"
                or (
                    substring(start_date, 6, 2) = "07"
                    and substring(start_date, 9, 2) = "01"
                )
            )
    )

select t1.employee_id
,employee_name
,round(avg(performance_score) over(partition by t1.employee_id),0) as first_half_2024_score
,row_number() over(partition by t1.project_id order by performance_score desc) as project_group_rank
,department
,project_name as project_group
from performance t1
join project t2 on t1.project_id = t2.project_id
join employees t3 on t1.employee_id = t3.employee_id
order by t1.project_id
,project_group_rank
,employee_id