工时利用率必须先定义分子（有效打卡对的分钟数）和分母（实际应出勤分钟数），再聚合求比值；分母须用COUNT(DISTINCT work_date)×480或排班表字段，禁用固定值；需NULLIF防除零，两层聚合确保权重合理。
不能直接用SUM或AVG算“利用率”，必须先定义分子分母再聚合，否则结果无业务意义。
什么是工时利用率的合理SQL表达
工时利用率 = 实际工作时长 / 应出勤时长（或标准工时）。它不是原始字段，而是两个聚合值的比值，且分母不能为0。常见错误是把打卡时间差直接SUM后除以固定小时数，忽略了请假、调休、排班变动等业务逻辑。
- 分子应来自有效打卡对：用
LEAD配对上下卡，过滤间隔在 4–12 小时之间的记录，再DATEDIFF(MINUTE, in_time, out_time)求分钟数 - 分母不能硬写 8*60，而要查排班表或按部门取
standard_work_minutes字段；若无排班表，可用COUNT(DISTINCT work_date) * 480（假设每日 8 小时）作粗略基准 - 必须用
NULLIF(denominator, 0)防止除零错误，否则整组查询会失败
按部门统计平均利用率的典型写法
核心是两层聚合：内层算每人每日利用率，外层按部门AVG。不能在GROUP BY department里直接SUM(worked)/SUM(standard)——那会掩盖个体差异，且权重失真。
- 内层子查询需
PARTITION BY emp_id, CAST(check_time AS DATE)确保LEAD不跨天 - 用
CASE WHEN DATEDIFF(MINUTE, in_time, out_time) BETWEEN 240 AND 720 THEN ... END过滤异常打卡对（如午休误打、设备重复上报） - 外层
AVG(CAST(utilization AS DECIMAL(5,3)))显式转精度，避免整数除法截断
SELECT
department,
AVG(CAST(utilization AS DECIMAL(5,3))) AS avg_utilization
FROM (
SELECT
p.department,
CAST(SUM(CASE WHEN valid_pair = 1 THEN work_mins ELSE 0 END) AS FLOAT)
/ NULLIF(SUM(CASE WHEN valid_pair = 1 THEN 480 ELSE 0 END), 0) AS utilization
FROM people p
INNER JOIN (
SELECT
emp_id,
check_time AS in_time,
LEAD(check_time) OVER (PARTITION BY emp_id, CAST(check_time AS DATE) ORDER BY check_time) AS out_time,
CASE
WHEN DATEDIFF(MINUTE, check_time, LEAD(check_time) OVER (PARTITION BY emp_id, CAST(check_time AS DATE) ORDER BY check_time))
BETWEEN 240 AND 720 THEN 1
ELSE 0
END AS valid_pair,
DATEDIFF(MINUTE, check_time, LEAD(check_time) OVER (PARTITION BY emp_id, CAST(check_time AS DATE) ORDER BY check_time)) AS work_mins
FROM attendance
) a ON p.emp_id = a.emp_id
GROUP BY p.department, CAST(a.in_time AS DATE)
) t
GROUP BY department;为什么COUNT(DISTINCT ...)在这里比SUM更关键
利用率统计最常被忽略的是「分母的覆盖范围」。例如某员工当月只出勤 10 天，但系统默认按 22 天算标准工时，会导致分母虚高、利用率被低估。此时COUNT(DISTINCT work_date)才是真实应出勤天数。
- 若考勤表有
status字段（如 'present', 'leave', 'absent'），应优先用COUNT(DISTINCT CASE WHEN status = 'present' THEN work_date END)作分母 - 不要用
WHERE status = 'present'预过滤再COUNT(*)，否则会漏掉“当天有打卡但状态为 leave”的边界情况 - SQL Server 中
COUNT(DISTINCT ...)在大数据量下性能较差，可考虑先用GROUP BY emp_id, work_date去重，再外层聚合
实际部署时最容易崩的三个点
不是语法错，而是数据质量与假设冲突：
-
LEAD没加PARTITION BY emp_id, CAST(check_time AS DATE)——张三的下班卡被当李四的上班卡算，工时翻倍 - 时间字段是
DATETIME2但没处理毫秒级抖动，导致LEAD拉出的时间差为 0.001 分钟，被误判为无效对 - 没对
work_mins做NULLIF检查就直接参与除法，一整批NULL值让AVG结果变成NULL，而非跳过该记录
真正难的从来不是写聚合函数，而是把业务规则翻译成不可绕过的SQL约束。每一条CASE分支，都该对应一个HR确认过的考勤制度条款。
