题解 | 统计加班员工占比

# 查询后面各种函数嵌套得有点多而已，计算思路清晰的话其实还好
# 计算间隔时长多少个小时，timestampdiff以分钟为单位再除以60，这样相对准确
# 以小时为单位，会出现因取舍导致的误差
select
    s.department,
    concat(
        round(
            sum(
                if(
                    (
                        timestampdiff(minute, first_clockin, last_clockin) / 60
                    ) > 9.5,
                    1,
                    0
                )
            ) * 100 / count(s.staff_id),
            1
        ),
        '%'
    ) as ratio
from
    staff_tb s
    left join attendent_tb a on s.staff_id = a.staff_id
group by
    s.department
order by
    ratio desc;