这两个SQL高级函数,你用过吗?

递归蝉翼
• 阅读 109

这两个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

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

结果如下:

这两个SQL高级函数,你用过吗?

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

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

结果如下:

这两个SQL高级函数,你用过吗?

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

结果是:

这两个SQL高级函数,你用过吗?

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

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

SELECT ID,NUM,

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

FROM T

结果为:

这两个SQL高级函数,你用过吗?

每组最后一行默认是0,所以0-NUM=-NUM,以上就是这两个函数的相关用法。

点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
美凌格栋栋酱 美凌格栋栋酱
7个月前
Oracle 分组与拼接字符串同时使用
SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Easter79 Easter79
3年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
梦
4年前
微信小程序new Date()转换时间异常问题
微信小程序苹果手机页面上显示时间异常,安卓机正常问题image(https://imghelloworld.osscnbeijing.aliyuncs.com/imgs/b691e1230e2f15efbd81fe11ef734d4f.png)错误代码vardate'2021030617:00:00'vardateT
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
VBox 启动虚拟机失败
在Vbox(5.0.8版本)启动Ubuntu的虚拟机时,遇到错误信息:NtCreateFile(\\Device\\VBoxDrvStub)failed:0xc000000034STATUS\_OBJECT\_NAME\_NOT\_FOUND(0retries) (rc101)Makesurethekern
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
初探 Objective
作者:Cyandev,iOS和MacOS开发者,目前就职于字节跳动0x00前言异常处理是许多高级语言都具有的特性,它可以直接中断当前函数并将控制权转交给能够处理异常的函数。不同语言在异常处理的实现上各不相同,本文主要来分析一下ObjectiveC和C这两个语言。为什么要把ObjectiveC和
Stella981 Stella981
3年前
Hibernate纯sql查询结果和该sql在数据库直接查询结果不一致
问题:今天在做一个查询的时候发现一个问题,我先在数据库实现了我需要的sql,然后我在代码中代码:selectdistinctd.id,d.name,COALESCE(c.count_num,0),COALESCE(c.count_fix,0),COALESCE(c
递归蝉翼
递归蝉翼
Lv1
曾经沧海难为水,除却巫山不是云。
文章
1
粉丝
0
获赞
0
热门文章

暂无数据