如何优化SQL查询以生成每月在职员工数时间线？

原脚本使用WHILE循环逐月累加统计，每次循环都全表扫描员工表，在数据量较大或时间范围较长时性能会显著下降。以下是两种更高效的优化方案，适合仪表盘这类需要重复调用的场景：

方案一：事件驱动法（最优性能） #

通过将员工入职/离职转化为「人数变化事件」，再累计求和得到每月在职人数，仅需扫描员工表2次，性能最优：

DECLARE @startDate date = '2022-11-01';

WITH EmployeeEvents AS (
    -- 入职事件：当月第一天，人数+1
    SELECT 
        DATEFROMPARTS(YEAR(start_date), MONTH(start_date), 1) AS event_month,
        1 AS employee_change
    FROM employee_example
    UNION ALL
    -- 离职事件：离职月的下一个月第一天，人数-1（离职当月仍算在职）
    SELECT 
        DATEADD(month, 1, DATEFROMPARTS(YEAR(leave_date), MONTH(leave_date), 1)) AS event_month,
        -1 AS employee_change
    FROM employee_example
    WHERE leave_date IS NOT NULL -- 排除现任员工（无离职日期）
),
DateRange AS (
    -- 递归生成指定起始日期到当前月的所有月份
    SELECT @startDate AS month_
    UNION ALL
    SELECT DATEADD(month, 1, month_)
    FROM DateRange
    WHERE DATEADD(month, 1, month_) <= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
MonthlyChanges AS (
    -- 汇总每个月的人员变化量
    SELECT 
        dr.month_,
        COALESCE(SUM(ee.employee_change), 0) AS monthly_change
    FROM DateRange dr
    LEFT JOIN EmployeeEvents ee ON dr.month_ = ee.event_month
    GROUP BY dr.month_
)
-- 累计求和得到当月在职人数
SELECT 
    month_,
    SUM(monthly_change) OVER (ORDER BY month_) AS nb_employees
FROM MonthlyChanges
ORDER BY month_
OPTION (MAXRECURSION 0); -- 当时间范围超过100个月时需设置，解除递归次数限制

优势： #

• 仅扫描员工表2次，避免循环带来的多次全表扫描
• 逻辑清晰，适合大数据量场景，性能随数据量增长的幅度远低于原脚本
• 自动处理现任员工（无离职日期）的统计

方案二：日期范围关联法（更直观） #

生成完整月份序列后，直接关联员工表判断是否在职，代码更直观，适合数据量中等的场景：

DECLARE @startDate date = '2022-11-01';

WITH DateRange AS (
    SELECT @startDate AS month_
    UNION ALL
    SELECT DATEADD(month, 1, month_)
    FROM DateRange
    WHERE DATEADD(month, 1, month_) <= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
)
SELECT 
    dr.month_,
    COUNT(ee.employee_id) AS nb_employees
FROM DateRange dr
LEFT JOIN employee_example ee 
    ON ee.start_date <= EOMONTH(dr.month_) -- 入职日期不晚于当月最后一天
    AND (ee.leave_date >= dr.month_ OR ee.leave_date IS NULL) -- 离职日期不早于当月第一天，或仍在职
GROUP BY dr.month_
ORDER BY dr.month_
OPTION (MAXRECURSION 0);

优势： #

• 代码逻辑直观，易于理解和维护
• 避免循环，仅扫描员工表1次

原脚本的问题说明 #

1. 性能瓶颈：每次循环都全表扫描员工表，时间范围越长，扫描次数越多，性能线性下降
2. 逻辑漏洞：未处理leave_date为NULL的现任员工（这类员工会被原脚本的WHERE条件排除）

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