题解 | 统计加班员工占比

with t1 as(
select sb.department,count(*) as people from staff_tb as sb
left join attendent_tb as ab 
on sb.staff_id=ab.staff_id
group by sb.department) ,
t2 as(
select sb.department,count(*) as people from staff_tb as sb
left join attendent_tb as ab 
on sb.staff_id=ab.staff_id
where timestampdiff (minute,ab.first_clockin,last_clockin)/60 >9.5
group by sb.department)
select t1.department ,concat(ifnull(round(t2.people*100 /t1.people,1),0.0),'%')
as ratio
from t1 
left join t2
on t1.department=t2.department
order by ratio desc
;