题解 | 统计加班员工占比

select
    department,
    concat (
        round(
            sum(
                case
                    when work_time > 9.5 then 1
                    else 0
                end
            ) / count(staff_id) * 100,
            1
        ),
        '%'
    ) ratio
from
    (
        select
            staff_id,
            round(
                timestampdiff (minute, first_clockin, last_clockin) / 60,
                1
            ) work_time
        from
            attendent_tb
    ) t
    join staff_tb using (staff_id)
group by
    department
order by
    ratio desc;