如何高效获取每个客户每笔交易的上一笔交易详情？

我有一张包含CustomerID、TransactionDate、Amount字段的交易数据表，需要新增LastTransDate、LastTransAmount列，展示每个客户每笔交易对应的上一笔交易的日期和金额详情。目前用OUTER APPLY的查询能实现需求，但运行速度极慢；尝试转成LEFT JOIN没成功，怀疑GROUP BY影响性能，寻求优化建议。

原表结构 #

目标表结构 #

当前使用的查询语句 #

SELECT TOP 1000 
       t.CustomerID,
       t.TransactionDate,
       t2.LastTransDate,
       t.Amount,
       t2.Amount LastTransAmount
FROM myTable t
OUTER APPLY (
    SELECT TOP 1 
           CustomerID, 
           TransactionDate AS LastTransDate,
           Amount
    FROM myTable
    WHERE t.CustomerID = CustomerID
      AND t.TransactionDate > TransactionDate
    ORDER BY TransactionDate DESC) t2 
GROUP BY t.Customer_ID,
         t.TransactionDate,
         t2.LastTransDate,
         t.Amount,
         t2.Amount,
ORDER BY t.CustomerID, t.TransactionDate

优化建议 #

1. 移除多余的GROUP BY #

当前查询里的GROUP BY完全是冗余操作——OUTER APPLY的子查询已经返回单条结果，GROUP BY不会改变输出，反而会增加额外的排序和聚合开销，直接删除即可。

2. 创建关键复合索引 #

OUTER APPLY的子查询需要按CustomerID过滤，再按TransactionDate倒序取第一条，创建以下复合索引可以让数据库快速定位到目标数据，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_myTable_CustomerID_TransactionDate 
ON myTable (CustomerID, TransactionDate) 
INCLUDE (Amount);

3. 用窗口函数替代OUTER APPLY（最优方案） #

使用LAG()窗口函数是实现这类“上一行关联”需求的最高效方式，数据库原生支持优化，只需要扫描一次表即可完成计算：

SELECT 
    CustomerID,
    TransactionDate,
    LAG(TransactionDate) OVER (PARTITION BY CustomerID ORDER BY TransactionDate) AS LastTransDate,
    Amount,
    LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY TransactionDate) AS LastTransAmount
FROM myTable
ORDER BY CustomerID, TransactionDate

这个方案配合上面的复合索引，性能会比原查询提升几个数量级。

4. 改用自连接实现（替代LEFT JOIN的可行方案） #

如果一定要用JOIN方式，可以先给每个客户的交易按日期排号，再通过行号自连接：

WITH TransactionRanked AS (
    SELECT 
        CustomerID,
        TransactionDate,
        Amount,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TransactionDate) AS RowNum
    FROM myTable
)
SELECT 
    t1.CustomerID,
    t1.TransactionDate,
    t2.TransactionDate AS LastTransDate,
    t1.Amount,
    t2.Amount AS LastTransAmount
FROM TransactionRanked t1
LEFT JOIN TransactionRanked t2 
    ON t1.CustomerID = t2.CustomerID 
    AND t1.RowNum = t2.RowNum + 1
ORDER BY t1.CustomerID, t1.TransactionDate

同样依赖前面提到的复合索引来快速生成行号，效率远高于原OUTER APPLY写法。

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