SQL中每第6行之间的差异

Question

如何计算SQL Server 2016中每6行之间的差异？例如：

Col1     Col2   Diff
-----    ----   ----
1       2      NULL
2       4      NULL
3       6      NULL 
4       10     NULL
5       14     NULL 
6       18     NULL 
7       20     18
8       22     18
9       30     24

Answer 1

使用lag()

select t.*, col2 - lag(col2, 6) over (order by col1) as diff6
from t;

lag()和lead()的第二个(不常用)参数是offset。如果值不在那里，它将返回NULL，这似乎正是您想要的。

Answer 2

Lag()是最直接的选项。下面是使用JOIN的另一种方式。

WITH STable
AS
(
    SELECT col1, col2, ROW_NUMBER () OVER (ORDER BY col1) AS rownum FROM table
)

Select x.col1, x.col2, x.col2 - y.col2 AS diff
from STable x
Left Join STable y
ON x.rownum = y.rownum + 6