SQL如何统计员工相邻两次病假记录间行数并计算平均间隔

DevOps 小满

2026-9-2

问题说明

现有员工考勤表包含3个字段：

Employee：员工标识
Date：考勤日期
SickDay：病假标记，值为1时代表当日为病假

表的生成规则：无论员工当日是否到岗，每日都会生成一条对应记录。
需求为统计每位员工相邻两次病假记录之间的条目数量，最终计算两次病假之间的平均条目数。例：某员工共20条记录，病假记录分别在第1行、第10行、第20行，需输出该员工对应病假间隔的平均值。

当前编写的查询语句未达到预期效果，原尝试代码如下：

SELECT count(*)
FROM Employees
WHERE EXISTS (
        SELECT *
        FROM employees
        WHERE SickDay = 1
            AND Employee = James
        )

错误原因

原语句逻辑存在明显问题：只要表中存在员工James的病假记录，就会返回全表总条数，完全没有实现病假记录定位、相邻病假间隔计算的逻辑。

正确实现方案

使用窗口函数可以高效实现该需求，兼容MySQL 8.0+、PostgreSQL、SQL Server等主流支持SQL标准的数据库，实现步骤如下：

先按员工分组、考勤日期升序，给每条记录打上连续行号
筛选所有病假记录，通过窗口函数错位拿到同一名员工上一次病假的行号
计算相邻两次病假的行号差值，即为两次病假之间的条目数，最终按员工求平均值即可

对应SQL代码：

WITH ranked_records AS (
    SELECT
        Employee,
        SickDay,
        ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Date) AS row_num
    FROM Employees
),
sick_with_prev AS (
    SELECT
        Employee,
        row_num AS current_sick_row,
        LAG(row_num) OVER (PARTITION BY Employee ORDER BY Date) AS last_sick_row
    FROM ranked_records
    WHERE SickDay = 1
)
SELECT
    Employee,
    AVG(current_sick_row - last_sick_row) AS avg_entries_between_sickleave
FROM sick_with_prev
WHERE last_sick_row IS NOT NULL
GROUP BY Employee;

针对举例的场景：病假行号为1、10、20，计算得到的间隔分别为9、10，最终平均间隔为9.5，符合预期。如果需要统计全公司所有员工病假的整体平均间隔，去掉GROUP BY Employee和SELECT子句里的Employee字段即可。

内容的提问来源于stack exchange，提问作者Classified Mystery