题解 | 统计加班员工占比

with staff_overtime as (
    select staff_id, first_clockin, last_clockin
    from attendent_tb as a
    where (timestampdiff(minute, a.first_clockin, a.last_clockin)/60) > 9.5
    
)

select  distinct sn.department as department,
    concat(round(sum(if(so.staff_id is not null, 1, 0)) * 100 / count(sn.staff_id), 1), '%') as ratio
from staff_tb as sn 
    left join attendent_tb as a on sn.staff_id = a.staff_id
    left join staff_overtime as so on sn.staff_id = so.staff_id
group by sn.department
order by ratio desc
#需要注意使用timestampdiff直接使用hour可能会带来精确小时计算的误差，所以，使用minute*60更稳妥