题解 | SQLW26 统计加班员工占比

WITH t1 AS (
    SELECT a.staff_id,
           TIMESTAMPDIFF(SECOND ,first_clockin, last_clockin) / 3600 AS hour,
           b.department
    FROM attendent_tb a
    LEFT JOIN staff_tb b
    ON a.staff_id = b.staff_id
),
t2 AS (
    SELECT department,
           count(staff_id) AS dep_total
    FROM t1
    GROUP BY department
),
t3 AS (
    SELECT department,
           count(staff_id) AS dep_more
    FROM t1
    WHERE hour > 9.5
    GROUP BY department
),
t4 AS (
    SELECT t2.department,
           ROUND(
               IFNULL(100 * t3.dep_more / t2.dep_total,0)
               ,1) AS ratio
    FROM t2
    LEFT JOIN t3
    ON t2.department = t3.department
    ORDER BY ratio DESC
),
t5 AS (
    SELECT department,
           CONCAT(ratio,'%') ratio
    FROM t4
)SELECT * FROM t5;

# 这道题的关键在于先转化为秒，再转化为小时。