如何自动化实现月度员工离职差异计算的SQL查询

现有表结构及数据 #

employees表存储员工月度在岗数据，数据如下：

期望输出 #

需要统计每月离职数（员工在下月不在岗则计为当月离职），输出格式如下：

当前手动实现的SQL #

目前通过手动编写多组JOIN对比相邻月份的方式实现，代码如下：

WITH MONTHLY_COUNT(DTE, EMP_COUNT) AS(SELECT    [DATE],    COUNT(1)    AS [COUNT]FROM    employees AGROUP BY    [DATE]),raw_data(EMPLOYEE, DATE) AS(SELECT    A.EMPLOYEE,    [DATE]FROM    employees A),RANKING(DTE, EMP_COUNT, RANKING) AS(SELECT    A.DTE,    A.EMP_COUNT,    A.RANKINGFROM(    SELECT        TOP(6) ---pick the most recent x months        A.DTE,        A.EMP_COUNT,        RANK() OVER(ORDER BY A.DTE DESC) AS [RANK]    FROM         MONTHLY_COUNT AS A)A(DTE, EMP_COUNT, RANKING))SELECT    A.DTE,    CASE        WHEN A.DTE = B.DTE THEN B.SEPARATIONS        WHEN A.DTE = C.DTE THEN C.SEPARATIONS        WHEN A.DTE = D.DTE THEN D.SEPARATIONS    END AS SEPARATIONSFROM(    SELECT        TOP(6) --pick the most recent x months        A.DTE    FROM        MONTHLY_COUNT A    ORDER BY        A.DTE DESC)A--COMPARE MONTH RANK 2 AND 1LEFT JOIN(    SELECT        A.DTE,        SUM(CASE WHEN B.EMPLOYEE IS NULL THEN 1 ELSE 0 END) AS SEPARATIONS    FROM(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE         WHERE A.RANKING = '2'  ----this is what I want to automate    )A    LEFT JOIN(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE          WHERE A.RANKING = '1' ----this is what I want to automate    )B ON A.Employee = B.Employee    GROUP BY        A.DTE)B ON B.DTE = A.DTE--COMPARE MONTH RANK 3 AND 2LEFT JOIN(    SELECT        A.DTE,        SUM(CASE WHEN B.EMPLOYEE IS NULL THEN 1 ELSE 0 END) AS SEPARATIONS    FROM(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE         WHERE A.RANKING = '3'    )A    LEFT JOIN(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE        WHERE A.RANKING = '2'    )B ON A.Employee = B.Employee    GROUP BY        A.DTE)C ON C.DTE = A.DTE--COMPARE MONTH RANK 4 AND 3LEFT JOIN(    SELECT        A.DTE,        SUM(CASE WHEN B.EMPLOYEE IS NULL THEN 1 ELSE 0 END) AS SEPARATIONS    FROM(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE         WHERE A.RANKING = '4'    )A    LEFT JOIN(        SELECT *        FROM            RANKING A        LEFT JOIN             raw_data B ON A.DTE = B.DATE         WHERE A.RANKING = '3'    )B ON A.Employee = B.Employee    GROUP BY        A.DTE)D ON D.DTE = A.DTE ORDER BY A.DTE DESC

优化后的自动化SQL #

以下方案通过窗口函数LEAD自动获取相邻月份，无需手动编写多组JOIN：

WITH all_months AS (
    -- 获取所有唯一月份，并自动获取每个月份的下一个月份
    SELECT 
        DISTINCT [date],
        LEAD([date]) OVER (ORDER BY [date]) AS next_month
    FROM employees
),
monthly_departures AS (
    -- 统计当月员工中，下一月未在岗的数量（即离职数）
    SELECT
        am.[date],
        COUNT(DISTINCT e.[employee id]) AS departures
    FROM all_months am
    -- 关联当月在岗员工
    JOIN employees e ON e.[date] = am.[date]
    -- 左连接下一月同员工的记录
    LEFT JOIN employees e_next 
        ON e_next.[employee id] = e.[employee id] 
        AND e_next.[date] = am.next_month
    -- 过滤出下一月无记录的员工（即离职），且排除无下一月的最新月份
    WHERE am.next_month IS NOT NULL
        AND e_next.[employee id] IS NULL
    GROUP BY am.[date]
)
-- 关联所有月份与离职数，最新月份无下一月则显示NULL
SELECT
    am.[date],
    md.departures
FROM all_months am
LEFT JOIN monthly_departures md ON am.[date] = md.[date]
ORDER BY am.[date] DESC;

优化逻辑说明 #

1. all_months CTE：提取所有唯一的在岗月份，通过LEAD函数按日期排序自动获取每个月份的下一个月份，无需手动指定月份排名。
2. monthly_departures CTE：关联当月员工和下一月的员工记录，统计当月存在但下一月不存在的员工数量，即为当月离职数。
3. 最终查询：左连接所有月份和离职统计结果，最新月份因无下一月数据，departures自动显示为NULL，完全匹配期望输出。

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