题解 | 统计加班员工占比

WITH attendent AS(
    SELECT 
        staff_id,
        if(
            TIMESTAMPDIFF(second,first_clockin,last_clockin)/3600>9.5, #需要使用secound->hour的转换，并不能直接使用hour计数
            1,
            0
        ) AS jiaban
    FROM attendent_tb
)
SELECT
    staff_tb.department,
    concat(round(sum(attendent.jiaban)/count(staff_tb.staff_id)*100,1),'%') AS ratio 
FROM staff_tb
LEFT JOIN attendent
ON staff_tb.staff_id=attendent.staff_id
GROUP BY staff_tb.department
ORDER BY sum(attendent.jiaban)/count(staff_tb.staff_id) DESC;