如何对实现demand-actual FTE-pipeline计算的Stored procedure做清理优化

背景 #

本文是SQL查询相关问题的延伸内容，核心需求是对现有可运行的存储过程做代码清理和优化，基于4张表完成资源需求计算，核心逻辑为：
需求值 – 实际FTE – 招聘管道人数
该逻辑用于基于招聘流程中待入职人数计算剩余需要招聘的资源量，其中招聘管道人数指处于招聘流程中的人员总数，从tblResource表统计得到。

涉及表结构 #

tblResource #

tblDemand #

tblActual_FTE #

tblDepartment #

原有存储过程信息 #

运行结果 #

原有代码 #

CREATE PROCEDURE spGetTotalDeficit
AS
    SELECT
        z.department AS Department,
        z.w_shift AS [Shift],
        SUM(z.dmnd - z.fte - z.pipeline) AS Need
    FROM
        (SELECT
             y.department,
             y.departmentID,
             y.w_shift,
             y.pipeline,
             y.fte,
             [tblDemand].[Demand] AS dmnd
         FROM
             (SELECT
                  x.department,
                  x.DepartmentID,
                  w_shift,
                  x.pipeline,
                  tblActual_FTE.fte
              FROM
                  (SELECT
                       dpt.[Department],
                       rsrc.[DepartmentID],
                       rsrc.[shift] AS w_shift,
                       COUNT(rsrc.[shift]) AS pipeline
                   FROM
                       [tblResource] rsrc
                   JOIN
                       tblDepartment dpt ON rsrc.[DepartmentID] = dpt.DepartmentID
                   WHERE 
                       dpt.Department = @Dept
                   GROUP BY
                       dpt.Department, rsrc.[Shift], rsrc.[DepartmentID]) x
              LEFT JOIN
                  tblActual_FTE ON x.[DepartmentID] = tblActual_FTE.[DepartmentID]
                                AND x.w_shift = tblActual_FTE.[Shift]
              GROUP BY
                  x.Department, x.DepartmentID,
                  w_shift, x.pipeline, tblActual_FTE.FTE) y
         LEFT JOIN 
             tblDemand ON y.DepartmentID = tblDemand.DepartmentID
                       AND y.w_shift = tblDemand.[Shift]) z
    GROUP BY
        z.Department, z.w_shift
END

优化方案 #

优化后代码 #

CREATE PROCEDURE spGetTotalDeficit
    @Dept NVARCHAR(50) -- 补全原代码缺失的输入参数
AS
BEGIN
    SET NOCOUNT ON; -- SQL Server存储过程通用优化，减少网络传输开销

    -- CTE替代嵌套子查询，逻辑拆分更清晰
    WITH PipelineStats AS (
        -- 统计各部门各班次有效招聘管道人数
        SELECT
            dpt.Department,
            rsrc.DepartmentID,
            rsrc.Shift,
            COUNT(*) AS Pipeline
        FROM tblResource rsrc
        INNER JOIN tblDepartment dpt 
            ON rsrc.DepartmentID = dpt.DepartmentID
        WHERE 
            dpt.Department = @Dept
            AND rsrc.Removed = 0 -- 排除已作废的人员记录，原逻辑漏了该过滤条件
        GROUP BY dpt.Department, rsrc.DepartmentID, rsrc.Shift
    )
    SELECT
        p.Department,
        p.Shift,
        -- 空值处理，缺失值默认按0计算避免返回NULL，业务需要保留NULL可去掉对应ISNULL
        ISNULL(d.Demand, 0) - ISNULL(f.FTE, 0) - ISNULL(p.Pipeline, 0) AS Need
    FROM PipelineStats p
    LEFT JOIN tblActual_FTE f
        ON p.DepartmentID = f.DepartmentID AND p.Shift = f.Shift
    LEFT JOIN tblDemand d
        ON p.DepartmentID = d.DepartmentID AND p.Shift = d.Shift
    -- 去掉冗余外层GROUP BY：CTE已经按部门+班次聚合，每个组合仅对应一行数据
END

核心优化说明 #

• 去掉三层冗余嵌套子查询，用CTE拆分统计逻辑，可读性大幅提升
• 删除无意义的重复GROUP BY操作，执行效率更高
• 补全原代码缺失的@Dept参数声明，避免运行报错
• 新增Removed = 0过滤条件，统计招聘管道人数时排除已作废的人员记录，结果更准确
• 新增ISNULL空值处理，不存在的需求/实际FTE默认按0计算，避免返回无意义的NULL值
• 表关联使用更简短的别名，代码更简洁，无冗余字段查询

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