题解 | 各个部门实际平均薪资和男女员工实际平均薪资

# 临时表存储各部门男女平均实际薪水，如果一个部门没有某一性别的员工，则平均薪资取0.00,并计算总人数
with tab as(
select distinct department,case when  dep_male != 0 then round(dep_male_sum/dep_male,2) when  dep_male = 0 then 0.00 end as average_actual_salary_male,case when  dep_female != 0 then round(dep_female_sum/dep_female,2) when  dep_female = 0 then 0.00 end as average_actual_salary_female,sum(actual) over(partition by department) as sums,dep_male + dep_female as dep_num
from(
# 统计各部门男女人数和男女总实际薪资
select department,staff_gender,actual,count(case when staff_gender = 'male' then 1 else null end) over(partition by department) as dep_male,count(case when staff_gender = 'female' then 1 else null end) over(partition by department) as dep_female,sum(case when staff_gender = 'male' then actual else 0 end) over(partition by department) as dep_male_sum,sum(case when staff_gender = 'female' then actual else 0 end) over(partition by department) as dep_female_sum
from(
# 连接表格，整合信息，计算实际薪资
select s.staff_id,s.staff_gender,s.post,s.department,sa.normal_salary,sa.dock_salary,sa.normal_salary-sa.dock_salary as actual
from staff_tb as s
left join salary_tb as sa on s.staff_id=sa.staff_id
) as temp
) as temp2
)
# 计算各部门平均薪水，调整顺序
select department,round(sums/dep_num,2) as average_actual_salary,average_actual_salary_male,average_actual_salary_female
from tab
order by average_actual_salary desc