这两个SQL高级函数，你用过吗？
SQL数据库开发
共 1910字，需浏览 4分钟
·
2020-11-03 09:07
点击关注上方“SQL数据库开发”，
设为“置顶或星标”，第一时间送达干货
SQL Server从2012版本开始，引入了LEAD和LAG函数，这两个函数可以把之前要关联查询的方法，改为可直接获取当前数据上下相邻多少行数据，可以很方便的对上下相邻两行的数据进行加减乘除。今天我们就给大家介绍一下这两个函数的用法。
LAG函数
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
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
以上就是这两个函数的相关用法。
——End——
后台回复关键字：1024，获取一份精心整理的技术干货 后台回复关键字：进群，带你进入高手如云的交流群。 推荐阅读 这是一个能学到技术的公众号，欢迎关注
点击「阅读原文」了解SQL训练营
评论