题解 | 统计加班员工占比

SELECT
    b.department,
    CONCAT(
        ROUND(
            AVG(
                CASE WHEN TIMESTAMPDIFF(SECOND,a.first_clockin,a.last_clockin)/3600 > 9.5       -- 计算加班时间是否大于9.5h
                THEN 1 ELSE 0 END) * 100.0      -- 大于9.5则记录加班+1，否则就是加班+0，*100则是为了转化为百分比格式
        ,1)
    ,'%') AS ratio
FROM attendent_tb AS a
INNER JOIN staff_tb AS b
    ON (a.staff_id = b.staff_id)
GROUP BY b.department
ORDER BY ratio DESC;