如何用SQL编写按不同工作日统计员工出勤率

在实际业务中，统计不同工作日的出勤率是HR系统里的高频需求。如果直接按日期函数分组，很容易掉进语言环境、索引失效或分母口径的坑里。下面就来拆解具体的实现要点。

必须用 CASE WHEN 将日期映射为固定 weekday 标签（如 'Mon'）再分组，避免语言环境导致的分组断裂；需过滤 DOW IN (1,2,3,4,5) 排除周末，并确保分母为应出勤天数而非打卡次数，日期条件须避免函数包裹以保障索引生效。

用 CASE WHEN 区分工作日再聚合统计

如果直接按 DAYOFWEEK() 或 EXTRACT(DOW FROM ...) 分组，周一到周五的数据会被揉成一团，算出来的只是一个笼统的总出勤率。但业务真正需要的是「每个工作日分别算」——周一、周二、周三各是多少。必须先用 CASE WHEN 把日期映射成明确的 weekday 标签（如 'Mon'、'Tue'），再按这个标签分组，才能得到独立的结果。

PostgreSQL 示例：

SELECT   CASE EXTRACT(DOW FROM check_in_time)    WHEN 1 THEN 'Mon'    WHEN 2 THEN 'Tue'    WHEN 3 THEN 'Wed'    WHEN 4 THEN 'Thu'    WHEN 5 THEN 'Fri'    ELSE 'Non-workday'  END AS weekday,  COUNT(*) FILTER (WHERE status = 'present') * 100.0 / COUNT(*) AS attendance_rateFROM attendanceWHERE check_in_time >= '2024-01-01'  AND EXTRACT(DOW FROM check_in_time) IN (1,2,3,4,5)GROUP BY weekdayORDER BY MIN(check_in_time);

• EXTRACT(DOW FROM ...) 在 PostgreSQL 中周日=0，周一=1…周六=6；MySQL 用 DAYOFWEEK()（周日=1，周一=2），注意偏移差异
• COUNT(*) FILTER (WHERE ...) 是 PostgreSQL 9.4+ 的写法；MySQL 得用 SUM(IF(status='present', 1, 0))
• 务必加 WHERE ... IN (1,2,3,4,5) 过滤，否则周末数据会进到 ELSE 分支，拉低工作日统计基数

避免用 DATE_FORMAT() 或 TO_CHAR() 直接分组

看起来很方便：DATE_FORMAT(check_in_time, '%a') 或 TO_CHAR(check_in_time, 'Dy') 可以直接输出 'Mon'，但隐患很大——语言环境（lc_time）会影响缩写结果。中文环境可能返回 '周一'，法语环境是 'lun.'，分组直接断裂，统计数据也跟着跑偏。

• MySQL 中设置 SET lc_time_names = 'en_US'; 可控，但上线后容易被其他会话覆盖
• PostgreSQL 中 TO_CHAR(..., 'Dy') 受 lc_messages 影响，且不同 locale 下长度不一致（如德语 'Mo' vs 英语 'Mon'）
• 更稳妥的做法始终是用数字 weekday（0–6 或 1–7）做逻辑判断，再映射为固定字符串

员工维度叠加时小心分母口径

如果要算「每位员工在各工作日的出勤率」，不能只用 COUNT(*) 当分母——因为某员工可能某天根本没打卡记录（缺勤），这时分母应该是「该员工应出勤的工作日天数」，而非实际打卡次数。

• 需先生成员工 × 工作日的笛卡尔积（可用 GENERATE_SERIES + CROSS JOIN 或递归 CTE），再 LEFT JOIN 到考勤表
• 否则直接 GROUP BY employee_id, weekday 会导致缺勤日被忽略，分母变小，出勤率虚高
• 例如：员工 A 周一、周三打卡，周二没记录 → 若只统计有记录的两天，出勤率=2/2=100%；但正确分母应是 3（周一至周五中他本该出勤的天数），需额外查排班表或按制度默认全勤

性能关键点：日期字段必须有索引，且别用函数包裹

写 WHERE DAYOFWEEK(check_in_time) = 1 或 WHERE EXTRACT(DOW FROM check_in_time) = 1 会让索引失效，全表扫描不可避免。

• 正确做法是把范围条件写成可走索引的形式：WHERE check_in_time >= '2024-01-01' AND check_in_time < '2024-02-01'，再在 WHERE 子句外过滤 weekday
• 如果高频按工作日查询，可建函数索引（PostgreSQL）：CREATE INDEX idx_attendance_dow ON attendance (EXTRACT(DOW FROM check_in_time));
• MySQL 8.0+ 支持函数索引，但需显式定义：CREATE INDEX idx_dow ON attendance ((WEEKDAY(check_in_time)));

真实场景里最常卡住的不是语法，而是「缺勤是否等于无记录」这个业务假设——排班表没同步、节假日未剔除、弹性工作制下某天本不该上班却被计入分母，都会让数字失真。先对齐业务规则，再写 SQL。