怎么在SQL Server中利用聚合函数高效统计员工工时利用率-SQL-PHP中文网

工时利用率必须先定义分子（有效打卡对的分钟数）和分母（实际应出勤分钟数），再聚合求比值；分母须用count(distinct work_date)×480或排班表字段，禁用固定值；需nullif防除零，两层聚合确保权重合理。

怎么在sql server中利用聚合函数高效统计员工工时利用率

不能直接用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去重，再外层聚合