无需使用LAG/LEAD函数，获取最后一位负责人的首次分配日期

问题描述 #

我需要编写SQL查询，获取工作任务分配给最后一位负责人的首次日期。例如给定AssignedTo表数据，WorkId为1的任务最后负责人是1，首次分配日期为2020-01-03。我已写出使用LAG函数的查询，但该函数性能损耗较大，请问能否不使用LAG/LEAD函数实现相同效果？

原表结构与数据 #

DROP TABLE IF EXISTS [dbo].[AssignedTo]

CREATE TABLE [dbo].[AssignedTo]
(
    [WorkId] [INT] NOT NULL,
    [OwnerId] [INT] NOT NULL,
    [ValidFrom] [DATETIME] NULL
) ON [PRIMARY];

INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-01');
INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 2, '2020-01-02');
INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-03');
INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-04');
INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (2, 3, '2020-01-05');
INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (3, 4, '2020-01-06');

原使用LAG的查询 #

;WITH AssignedToWithPrevOwner AS 
(
    SELECT
        WorkId, ValidFrom, OwnerId, 
        LAG(OwnerId) OVER (ORDER BY ValidFrom) AS PrevOwnerID
    FROM
        dbo.AssignedTo
),
DatesAssigned AS 
(
    SELECT atwo.WorkId, MAX(atwo.ValidFrom) AS DateAdded
    FROM AssignedToWithPrevOwner AS atwo
    WHERE atwo.PrevOwnerID <> atwo.OwnerId OR atwo.PrevOwnerID IS NULL
    GROUP BY atwo.WorkId
)
SELECT * 
FROM DatesAssigned;

替代方案（无LAG/LEAD） #

方案一：基于子查询筛选最后负责人的首次分配日期

-- 第一步：获取每个任务的最后负责人
WITH LastOwnerPerWork AS (
    SELECT WorkId, OwnerId
    FROM dbo.AssignedTo
    WHERE ValidFrom = (SELECT MAX(ValidFrom) FROM dbo.AssignedTo t WHERE t.WorkId = AssignedTo.WorkId)
),
-- 第二步：获取最后负责人的所有分配记录，以及任务其他负责人的最晚分配日期
OwnerAssignments AS (
    SELECT 
        a.WorkId, 
        a.OwnerId, 
        a.ValidFrom,
        -- 计算当前任务下其他负责人的最晚分配时间
        (SELECT MAX(ValidFrom) FROM dbo.AssignedTo t WHERE t.WorkId = a.WorkId AND t.OwnerId <> a.OwnerId) AS LastOtherOwnerDate
    FROM dbo.AssignedTo a
    JOIN LastOwnerPerWork lopw 
        ON a.WorkId = lopw.WorkId 
        AND a.OwnerId = lopw.OwnerId
)
-- 第三步：筛选出最后负责人首次接手的日期（晚于其他负责人最晚分配时间的最小日期）
SELECT 
    WorkId, 
    MIN(ValidFrom) AS FirstAssignedDateToLastOwner
FROM OwnerAssignments
WHERE ValidFrom > ISNULL(LastOtherOwnerDate, '1900-01-01')
GROUP BY WorkId;

方案二：使用NOT EXISTS过滤无效记录

-- 先获取每个任务的最后负责人
WITH LastOwnerPerWork AS (
    SELECT WorkId, OwnerId
    FROM dbo.AssignedTo
    WHERE ValidFrom = (SELECT MAX(ValidFrom) FROM dbo.AssignedTo t WHERE t.WorkId = AssignedTo.WorkId)
)
-- 筛选最后负责人的分配记录中，没有其他负责人在之后接手的最早日期
SELECT 
    lopw.WorkId,
    MIN(a.ValidFrom) AS FirstAssignedDateToLastOwner
FROM LastOwnerPerWork lopw
JOIN dbo.AssignedTo a 
    ON lopw.WorkId = a.WorkId 
    AND lopw.OwnerId = a.OwnerId
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.AssignedTo t 
    WHERE t.WorkId = a.WorkId 
      AND t.OwnerId <> a.OwnerId 
      AND t.ValidFrom > a.ValidFrom
)
GROUP BY lopw.WorkId;

逻辑说明 #

两种方案的核心思路一致：

1. 先定位每个任务的最后负责人（即该任务最新分配记录对应的OwnerId）。
2. 从该负责人的所有分配记录中，找到最后一次接手任务的起始日期——也就是不存在其他负责人在该日期之后接手任务的最早记录。

性能优化建议 #

建议在AssignedTo表上创建复合索引：CREATE INDEX IX_AssignedTo_WorkId_ValidFrom ON dbo.AssignedTo(WorkId, ValidFrom)，可以大幅提升子查询和关联操作的效率。

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