在T-SQL中获取CustNum对应前后行RowId的最优方案

现有一张约50万条记录的表，Date + CustNum为唯一索引，表结构及数据如下：

需要为每条记录获取对应CustNum的前一行RowId（CustPrevRowId）和后一行RowId（CustNextRowId），期望结果如下：

此前尝试用子查询实现，但因数据量较大出现性能问题，原代码如下：

SELECT T1.*,
    (SELECT TOP 1 RowID FROM T T2 WHERE T2.CustNum = T1.CustNum AND T2.Date < T1.Date ORDER BY DATE DESC) AS CustPrevRowId,
    (SELECT TOP 1 RowID FROM T T2 WHERE T2.CustNum = T1.CustNum AND T2.Date > T1.Date ORDER BY DATE ) AS CustNextRowId
 FROM T T1

最优实现方案 #

使用SQL窗口函数LAG()和LEAD()可以高效解决这个问题，这两个函数专门用于在分组内获取当前行的上一行和下一行数据，只需扫描一次表，性能远优于子查询（子查询会对每条记录执行两次查询，50万条数据会产生百万级别的查询次数）。

实现代码如下：

SELECT 
    RowId,
    Date,
    CustNum,
    LAG(RowId) OVER (PARTITION BY CustNum ORDER BY Date) AS CustPrevRowId,
    LEAD(RowId) OVER (PARTITION BY CustNum ORDER BY Date) AS CustNextRowId
FROM T
ORDER BY RowId;

说明 #

• PARTITION BY CustNum：按客户编号分组，确保只在同一客户的记录中查找相邻行
• ORDER BY Date：因为Date + CustNum是唯一索引，按日期排序可以保证相邻记录的顺序正确
• LAG(RowId)：获取分组内当前行的上一行RowId，如果是组内第一行则返回NULL
• LEAD(RowId)：获取分组内当前行的下一行RowId，如果是组内最后一行则返回NULL

该方案只需对表进行一次全表扫描，时间复杂度为O(n)，对于50万条数据的场景，性能会有显著提升。

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