题解 | 统计加班员工占比

with temp as (
select  department,sum(case when 
timestampdiff(second,first_clockin,last_clockin)/3600>9.5 then 1 else 0 end) as ot_count ,count(*) as staff_count
from attendent_tb a
join staff_tb s on s.staff_id	=a.staff_id	
group by department)
select department ,concat(round(ot_count/staff_count*100,1),'%' )as ratio
from temp
order by ot_count/staff_count desc
 ;