题解 | 统计加班员工占比

WITH
t1 AS (
SELECT
    staff_id,
    TIMESTAMPDIFF(SECOND,first_clockin,last_clockin) / 3600 AS sj
FROM attendent_tb
) 

SELECT
    b.department,
    CONCAT(
        ROUND(
            AVG(CASE WHEN t1.sj > 9.5 THEN 1 ELSE 0 END) * 100
        ,1)
    ,'%') AS ratio
FROM t1
INNER JOIN staff_tb AS b ON t1.staff_id = b.staff_id
GROUP BY b.department
ORDER BY ratio DESC;