使用Join和Window函数获取前后值的性能比较 在分析数据时，我们经常需要获取某一列的前一个或后一个值。为了实现这个目标，通常会使用Join操作或Window函数。然而，对于大型数据集来说，选择合适的方法非常重要，以确保查询的性能和效率。 使用Join操作是一种常见的方法，它通过将表与自身连接来获取前后值。这种方法可以提供准确的结果，但在处理大量数据时可能会导致性能问题。因为Join操作需要对整个表进行连接，所以它的执行时间会随着数据量的增加而增加。 另一种方法是使用Window函数，它可以在不进行连接的情况下获取前后值。Window函数是一种强大的工具，它可以在查询中创建一个窗口，并在该窗口内计算聚合函数。相比于Join操作，Window函数更加高效，因为它只需要对特定的窗口进行计算，而不需要对整个表进行连接。 当涉及到性能比较时，我们需要考虑数据集的大小、查询的复杂度以及系统的资源限制。如果数据集较小且查询简单，使用Join操作可能是一个不错的选择。但是，如果数据集很大或查询复杂，使用Window函数可能更加高效。 总之，选择使用Join操作还是Window函数来获取前后值取决于具体的情况。我们应该根据数据集的大小和查询的复杂度来权衡性能和效率，以选择最合适的方法。

Question

我有一张包含2000万行的表格，每一行都有3个列：时间（time）、ID和数值（value）。对于每个ID和时间，都有一个对应的状态数值。我想要知道特定时间和特定ID的前后数值。 我尝试了两种方法来实现这个目标。一种方法是使用连接（join），另一种方法是使用窗口函数（lead/lag）并在时间和ID上创建聚集索引。 我通过执行时间比较了这两种方法的性能。连接方法花费了16.3秒，而窗口函数方法花费了20秒，不包括创建索引的时间。这让我感到惊讶，因为窗口函数似乎更加先进，而连接方法则是蛮力计算。 以下是这两种方法的代码： 创建索引

create clustered index id_time
 on tab1 (id,time)

加入方法

select a1.id,a1.time
   a1.value as value, 
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

使用SET STATISTICS TIME, IO ON生成的IO统计信息：

这是连接方法的执行计划。

窗口函数方法

select id, time, value, 
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

（只通过时间进行排序可以节省0.5秒。）

这是窗口函数方法的执行计划。

IO统计信息

[

---

我检查了sample_orig_month_1999中的数据，看起来原始数据按id和time排序得很好。这是性能差异的原因吗？ 似乎连接方法比窗口函数方法有更多的逻辑读取，而前者的执行时间实际上更短。这是因为前者具有更好的并行性吗？ 我喜欢窗口函数方法，因为代码简洁，有没有办法加快它在这个特定问题上的速度？ 我正在使用Windows 10 64位上的SQL Server 2016。

- Jason

Answer 1

相对于自连接，LEAD和LAG窗口函数的行模式性能相对较低并不是什么新鲜事。例如，Michael Zilberstein在2012年就在SQLblog.com上写过相关内容。在（重复的）Segment、Sequence Project、Window Spool和Stream Aggregate计划操作符中存在相当多的开销。

在SQL Server 2016中，你有一个新选项，即启用窗口聚合的批处理模式。这需要在表上建立某种列存储索引，即使它是空的。目前，优化器要考虑批处理模式计划，需要存在列存储索引。特别是，它可以启用更高效的窗口聚合批处理模式操作符。 为了在你的情况下进行测试，创建一个空的非聚集列存储索引：

 -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON dbo.tab1 (id, [time], [value]) 
WHERE id < 0 AND id > 0;

查询内容：

SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag = 
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

现在应该给出一个执行计划，类似于：

...可能会执行得更快。

当将结果存储在新表中时，您可能需要使用OPTION (MAXDOP 1)或其他提示来获得相同的计划形状。并行版本的计划需要批处理模式排序（或可能是两个），这可能会稍微慢一些。这主要取决于您的硬件。

有关批处理模式窗口聚合运算符的更多信息，请参阅Itzik Ben-Gan的以下文章：

• 关于SQL Server 2016中的批处理模式窗口聚合运算符，你需要知道的事情：第1部分
• 关于SQL Server 2016中的批处理模式窗口聚合运算符，你需要知道的事情：第2部分
• 关于SQL Server 2016中的批处理模式窗口聚合运算符，你需要知道的事情：第3部分

- Paul White