如何用SQL统计当月全勤员工人数？

Question

如何用SQL统计当月全勤员工人数？一个常见问题是：在考勤表中，员工每日打卡记录可能存在缺失或异常（如某天无打卡数据），直接通过 COUNT 或 GROUP BY 统计容易误判出勤状态。若仅统计打卡天数是否等于当月工作日总数，未考虑节假日剔除或补卡情况，会导致结果不准确。此外，如何高效关联员工表与考勤表，确保未打卡的员工也能被纳入统计而非遗漏，是实现全勤判断的关键难点。需结合日历表、LEFT JOIN 和条件聚合，正确识别“应出勤但未缺勤”的员工。

Answer 1

一、问题背景与核心挑战

在企业人力资源管理系统中，统计“当月全勤员工人数”是一个高频需求。表面上看，只需统计每位员工当月打卡天数是否等于应出勤天数即可，但实际业务中存在多个复杂因素：

• 员工可能某天未打卡（数据缺失），但已申请补卡或系统标记为正常出勤；
• 节假日、调休、年假等非工作日需从“应出勤日”中剔除；
• 考勤表仅记录打卡行为，未打卡不等于缺勤，直接使用 COUNT(*) 易造成误判；
• 部分员工整月未打卡，若仅 INNER JOIN 员工表与考勤表，会导致该类员工被遗漏。

因此，准确识别“应出勤但未缺勤”的员工，是实现全勤统计的关键。

二、基础概念解析：全勤的定义与数据模型

全勤并非“打卡天数 = 当月总天数”，而是“在所有应出勤工作日均有有效出勤记录”。这涉及三个核心数据源：

1. 员工表（employees）：存储员工基本信息；
2. 考勤表（attendance）：记录每日打卡时间，可能存在空值或异常；
3. 日历表（calendar_dim）：标记每一天是否为工作日（含节假日调整）。

典型表结构如下：

表名	字段	说明
employees	emp_id, name	员工ID、姓名
attendance	emp_id, att_date, clock_in, status	打卡日期、打卡时间、状态（正常/迟到/缺勤/补卡）
calendar_dim	cal_date, is_workday	日历日期、是否为工作日

三、技术难点拆解与解决思路

要精准统计全勤人数，必须解决以下三大技术难点：

1. 应出勤日计算：结合日历表过滤掉周末和法定节假日；
2. 缺勤判断逻辑：不能仅靠打卡记录是否存在，而应依据 status 字段或补卡机制；
3. 数据完整性保障：使用 LEFT JOIN 确保即使无打卡记录的员工也能参与统计。

解决方案框架如图所示：

-- 示例：构建当月应出勤日视图
WITH work_days AS (
  SELECT cal_date 
  FROM calendar_dim 
  WHERE cal_date BETWEEN '2024-04-01' AND '2024-04-30'
    AND is_workday = 1
),
emp_work_days AS (
  SELECT e.emp_id, wd.cal_date
  FROM employees e
  CROSS JOIN work_days wd
)
SELECT 
  e.emp_id,
  COUNT(a.att_date) AS present_days,
  COUNT(wd.cal_date) AS should_attend_days
FROM emp_work_days wd
LEFT JOIN attendance a 
  ON wd.emp_id = a.emp_id 
  AND wd.cal_date = a.att_date 
  AND a.status IN ('正常', '补卡')
GROUP BY e.emp_id
HAVING COUNT(wd.cal_date) = COUNT(a.att_date);

四、完整SQL实现方案

以下是完整的 SQL 查询语句，用于统计2024年4月全勤员工人数：

WITH monthly_workdays AS (
  -- 获取指定月份的所有工作日
  SELECT cal_date
  FROM calendar_dim
  WHERE cal_date >= '2024-04-01'
    AND cal_date < '2024-05-01'
    AND is_workday = 1
),
employee_calendar AS (
  -- 为每个员工生成当月所有应出勤日的组合
  SELECT e.emp_id, e.name, mwd.cal_date
  FROM employees e
  CROSS JOIN monthly_workdays mwd
),
attendance_status AS (
  -- 标准化考勤状态：只有“正常”或“补卡”才算出勤
  SELECT emp_id, att_date
  FROM attendance
  WHERE status IN ('正常', '补卡')
    AND att_date >= '2024-04-01'
    AND att_date < '2024-05-01'
)
SELECT 
  COUNT(*) AS full_attendance_count
FROM (
  SELECT 
    ec.emp_id,
    COUNT(ec.cal_date) AS total_workdays,
    COUNT(asl.att_date) AS attended_days
  FROM employee_calendar ec
  LEFT JOIN attendance_status asl 
    ON ec.emp_id = asl.emp_id AND ec.cal_date = asl.att_date
  GROUP BY ec.emp_id
  HAVING COUNT(ec.cal_date) = COUNT(asl.att_date)
) t;

五、流程图：全勤统计逻辑流

graph TD
    A[开始] --> B[加载员工表]
    B --> C[生成当月工作日列表]
    C --> D[笛卡尔积生成员工-工作日矩阵]
    D --> E[左连接考勤表]
    E --> F[判断每日是否有效出勤]
    F --> G[按员工聚合出勤天数]
    G --> H[比较应出勤与实出勤]
    H --> I[筛选全勤员工]
    I --> J[输出全勤人数]

六、进阶优化建议

对于高并发或大数据量场景，可考虑以下优化策略：

• 将 monthly_workdays 抽象为物化视图，避免重复计算；
• 在 attendance 表上建立复合索引：(emp_id, att_date, status)；
• 引入“出勤标记表”预处理每日出勤状态，减少实时计算压力；
• 支持多班次、弹性工作制时，需扩展日历表字段（如 shift_type）；
• 增加异常预警机制，自动识别长期零打卡员工。

此外，可通过参数化查询支持动态月份输入，提升复用性。