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
(提示:可以左右移动代码)
结果如下:
从上面的示例中我们可以看到
针对列OneArgs,组内的NUM列的值默认向后偏移了一行,每组的第一行用默认的NULL来代替
针对TowArgs,使用了2个参数显示的偏移行,NUM的值也是向后偏移一行。
针对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
以上就是这两个函数的相关用法。