题解 | 统计加班员工占比

select 
    department,
    concat (
        round(
            ((sum(jiaban) / count(distinct staff_id)) * 100),
            1
        ),
        '%'
    ) ratio
from
    (
        select
            a.staff_id staff_id,
            department,
            if (diff > 9.5, 1, 0) jiaban
        from
            (
                select
                    staff_id,
                    (
                        round(
                            (
                                timestampdiff (minute, first_clockin, last_clockin)
                            ) / 60,
                            1
                        )
                    ) diff
                from
                    attendent_tb
            ) a
            left join staff_tb b on a.staff_id = b.staff_id
    ) t1
group by
    1
order by
    2 desc