开发者社区> 游客gsgxy6yg6ydlm> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

两个实用的SQL高级函数

简介: 两个实用的SQL高级函数
+关注继续查看

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

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

结果如下:

image.png

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

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

结果如下:

image.png

注意:第一行因为默认是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

结果:

image.png

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


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

SELECT ID,NUM,
LEAD(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM)-NUM AS Result
FROM T

结果:

image.png

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


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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ES 高级实战(三)同步 ES 数据
ES 高级实战(三)同步 ES 数据
204 0
这两个SQL高级函数的用法,你学会了吗?
SQL Server从2012版本开始,引入了LEAD和LAG函数,这两个函数可以把之前要关联查询的方法,改为可直接获取当前数据上下相邻多少行数据,可以很方便的对上下相邻两行的数据进行加减乘除。今天我们就给大家介绍一下这两个函数的用法。
42 0
SQL中的高级日期函数(下)
我们在工作中时常需要处理某个时间段的数据,例如: 如何求解上周的销量? 如何求解上月第一天的销售金额? 如何求解去年同期的在线人数? ...... 这些都是涉及到具体或者以当前为参照的时间段的数据。 我们该如何从海量数据中找出准确的时间段呢?
62 0
for循环写入100条数据到文件中 1.获取文件对象 2.for循环 100次 3.每次都要想文件中写入内容
for循环写入100条数据到文件中 1.获取文件对象 2.for循环 100次 3.每次都要想文件中写入内容
36 0
健壮且灵活的混合云网络——Series2:运维、并网及升级
健壮且灵活的混合云网络——Series2:运维、并网及升级
2691 0
golang使用ssl自签证书通信
版权声明:本文可能为博主原创文章,若标明出处可随便转载。 https://blog.
1028 0
61
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载