基于动态偏移LAG函数获取前一个非NULL值的T-SQL实现问询

需求说明 #

需要将员工部门生效日期数据集转换为新增PreviousNonNULLDepartmentIfAvailable列的目标数据集，该列规则如下：

• 按EmployeeName分区、EffectiveDate升序排序
• 动态查找当前行之前该员工最近的非NULL部门值
• 分区内首行该列固定为NULL，若之前无符合条件的非NULL值也显示NULL

此前尝试固定偏移的LAG函数无法满足动态查找需求，现提供可行实现方案。

源表与目标表脚本 #

CREATE TABLE #source
(
EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100)
);

INSERT INTO #source
VALUES
('Lisa','2017-06-25','Catering'),
('Lisa','2018-08-17',NULL),
('Lisa','2021-12-05','Gardening'),
('Melissa','2015-08-27',NULL),
('Melissa','2017-11-29','Office'),
('Melissa','2020-10-10','Driving'),
('Melissa','2022-07-11',NULL),
('Omar','2019-01-03',NULL),
('Omar','2020-04-07','Retail'),
('Omar','2021-03-29',NULL),
('Pat', '2012-09-12','Laundry'),
('Pat', '2013-10-30',NULL),
('Pat', '2014-11-29',NULL),
('Pat', '2015-08-16',NULL),
('Pat', '2016-11-05',NULL)

CREATE TABLE #destination
(
EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100),
PreviousNonNULLDepartmentIfAvailable varchar(100)
);

INSERT INTO #destination
VALUES
('Lisa','2017-06-25','Catering',NULL),
('Lisa','2018-08-17',NULL,'Catering'),
('Lisa','2021-12-05','Gardening','Catering'),
('Melissa','2015-08-27',NULL,NULL),
('Melissa','2017-11-29','Office',NULL),
('Melissa','2020-10-10','Driving','Office'),
('Melissa','2022-07-11',NULL,'Driving'),
('Omar','2019-01-03',NULL,NULL),
('Omar','2020-04-07','Retail',NULL),
('Omar','2021-03-29',NULL,'Retail'),
('Pat', '2012-09-12','Laundry',NULL),
('Pat', '2013-10-30',NULL,'Laundry'),
('Pat', '2014-11-29',NULL,'Laundry'),
('Pat', '2015-08-16',NULL,'Laundry'),
('Pat', '2016-11-05',NULL,'Laundry')

-- 查看源表
SELECT * FROM #source ORDER BY EmployeeName, EffectiveDate
-- 查看目标表
SELECT * FROM #destination ORDER BY EmployeeName, EffectiveDate

实现方案 #

方案一：SQL Server 2022及以上版本（支持IGNORE NULLS） #

利用LAST_VALUE函数结合IGNORE NULLS参数，指定窗口范围为当前行之前的所有行，直接获取最近的非NULL部门值：

SELECT
    EmployeeName,
    EffectiveDate,
    CurrentDepartment,
    -- 获取当前行之前最近的非NULL部门值，首行自动为NULL
    LAST_VALUE(CurrentDepartment) IGNORE NULLS OVER (
        PARTITION BY EmployeeName 
        ORDER BY EffectiveDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS PreviousNonNULLDepartmentIfAvailable
FROM #source
ORDER BY EmployeeName, EffectiveDate;

方案二：SQL Server 2019及以下版本（不支持IGNORE NULLS） #

通过CTE分组标记的方式，先将连续的NULL行与最近的非NULL行归为同一组，再关联获取上一组的部门值：

WITH EmployeeGroups AS (
    SELECT
        *,
        -- 按员工分区，累计计数非NULL部门行，生成分组ID
        SUM(CASE WHEN CurrentDepartment IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY EmployeeName 
            ORDER BY EffectiveDate
        ) AS GroupId
    FROM #source
),
GroupDepartments AS (
    SELECT
        EmployeeName,
        GroupId,
        -- 每个分组对应的非NULL部门值
        MAX(CurrentDepartment) AS Department
    FROM EmployeeGroups
    GROUP BY EmployeeName, GroupId
)
SELECT
    s.EmployeeName,
    s.EffectiveDate,
    s.CurrentDepartment,
    -- 关联上一个分组的部门值，首行无分组则为NULL
    gd_previous.Department AS PreviousNonNULLDepartmentIfAvailable
FROM #source s
JOIN EmployeeGroups eg ON s.EmployeeName = eg.EmployeeName AND s.EffectiveDate = eg.EffectiveDate
LEFT JOIN GroupDepartments gd_previous ON eg.EmployeeName = gd_previous.EmployeeName AND eg.GroupId = gd_previous.GroupId + 1
ORDER BY s.EmployeeName, EffectiveDate;

验证结果 #

两种方案执行后均可生成与#destination表完全一致的结果，满足需求中动态查找最近非NULL部门值的要求。

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