SQL Server这两个高级函数的用法，你学会了吗？，offset函数的高级用法
SQL Server从2012版本开始，引入了LEAD和LAG函数，这两个函数可以把之前要关联查询的方法，改为可直接获取当前数据上下相邻多少行数据，可以很方便的对上下相邻两行的数据进行加减乘除。今天我们就给大家介绍一下这两个函数的用法。
LAG函数
LAG的作用
LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。
LAG的语法
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
参数解释
scalar_expression
要根据指定偏移量返回的值。 这是一个返回单个（标量）值的任何类型的表达式。scalar_expression 不能为分析函数。
offset
当前行（从中获得取值）后的行数。 如果未指定，则默认值为 1。
OVER
为开窗函数，LAG函数必须与开窗函数一起使用。
LAG示例
- WITH T AS
- (
- SELECT 1 ID,10 NUM
- UNION ALL
- SELECT 1,20
- UNION ALL
- SELECT 1,30
- UNION ALL
- SELECT 2,40
- UNION ALL
- SELECT 2,50
- UNION ALL
- SELECT 2,60
- )
- SELECT ID,NUM,
- LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
- LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
- LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
- FROM T
(提示：可以左右移动代码)
结果如下：
从上面的示例中我们可以看到
1、针对列OneArgs，组内的NUM列的值默认向后偏移了一行，每组的第一行用默认的NULL来代替
2、针对TowArgs，使用了2个参数显示的偏移行，NUM的值也是向后偏移一行。
3、针对ThreeArgs，不仅使用了显示的偏移2行，而且第三个参数将偏移后默认值NULL改成了0
实战例子：如何求解组内上下两行的和？
- SELECT ID,NUM,
- NUM+LAG(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM) AS Result
- FROM T
结果如下：
注意：第一行因为默认是0，所以每组第一行的结果是NUM+0=NUM
LEAD函数
LEAD函数与LAG函数刚刚相反，它是向前偏移指定的行数，默认是1行。
语法哈参数与LAG类似，这里就不重复介绍了。我们直接看示例：
- SELECT ID,NUM,
- LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
- LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
- LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
- FROM T
结果：
使用情况与LAG函数类似，只是组内数据分别向前偏移了指定行数。
实战示例：求解同组内上下两行的差？
- SELECT ID,NUM,
- LEAD(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM)-NUM AS Result
- FROM T
结果：
每组最后一行默认是0，所以0-NUM=-NUM
以上就是这两个函数的相关用法。