如何使用SQL Server中LEAD和LAG高级函数

时间:2020-02-28 11:09:52 来源: 数据库开发


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示例

WITHTAS

(

SELECT1ID,10NUM

UNIONALL

SELECT1,20

UNIONALL

SELECT1,30

UNIONALL

SELECT2,40

UNIONALL

SELECT2,50

UNIONALL

SELECT2,60

)

SELECTID,NUM,

LAG(NUM)OVER(PARTITIONBYIDORDERBYNUM)ASOneArgs,

LAG(NUM,1)OVER(PARTITIONBYIDORDERBYNUM)ASTowArgs,

LAG(NUM,2,0)OVER(PARTITIONBYIDORDERBYNUM)ASThressArgs

FROMT

(提示:可以左右移动代码)

结果如下:

从上面的示例中我们可以看到

1、针对列OneArgs,组内的NUM列的值默认向后偏移了一行,每组的第一行用默认的NULL来代替

2、针对TowArgs,使用了2个参数显示的偏移行,NUM的值也是向后偏移一行。

3、针对ThreeArgs,不仅使用了显示的偏移2行,而且第三个参数将偏移后默认值NULL改成了0

实战例子:如何求解组内上下两行的和?

SELECTID,NUM,

NUM+LAG(NUM,1,0)OVER(PARTITIONBYIDORDERBYNUM)ASResult

FROMT

结果如下:

注意:第一行因为默认是0,所以每组第一行的结果是NUM+0=NUM

LEAD函数

LEAD函数与LAG函数刚刚相反,它是向前偏移指定的行数,默认是1行。

语法哈参数与LAG类似,这里就不重复介绍了。我们直接看示例:

SELECTID,NUM,

LEAD(NUM)OVER(PARTITIONBYIDORDERBYNUM)ASOneArgs,

LEAD(NUM,1)OVER(PARTITIONBYIDORDERBYNUM)ASTowArgs,

LEAD(NUM,2,0)OVER(PARTITIONBYIDORDERBYNUM)ASThressArgs

FROMT

结果:

使用情况与LAG函数类似,只是组内数据分别向前偏移了指定行数。

实战示例:求解同组内上下两行的差?

SELECTID,NUM,

LEAD(NUM,1,0)OVER(PARTITIONBYIDORDERBYNUM)-NUMASResult

FROMT

结果:

每组最后一行默认是0,所以0-NUM=-NUM

以上就是这两个函数的相关用法。

关键词:

关于我们 加入我们 广告服务 网站地图

All Rights Reserved, Copyright 2004-2020 www.ctocio.com.cn

如有意见请与我们联系 邮箱:5 53 13 8 [email protected]

豫ICP备20005723号    IT专家网 版权所有