mysql lag和lead_Oracle的LAG和LEAD分析函数

最新推荐文章于 2023-11-22 08:45:00 发布
最新推荐文章于 2023-11-22 08:45:00 发布
阅读量470 收藏
点赞数
文章标签： mysql lag和lead
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31524201/article/details/113430865
版权
本文详细介绍了Oracle数据库中的LAG和LEAD分析函数，用于获取当前行前后指定行的数据，提高查询效率。通过实例展示了如何使用这两个函数，并对比了它们的语法。同时，文章还提到了在处理包含NULL值的数据时需要注意的问题。
摘要由CSDN通过智能技术生成

Oracle的LAG和LEAD分析函数 Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。 Lead 函数可以在一次查询中取出当前行的同一字段的后 面第 N行的。 这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。 lag的语法如下：

Oracle的LAG和LEAD分析函数

Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。

这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

lag的语法如下：

test.jsp?url=http%3A%2F%2Fwww.linuxidc.com%2Fupload%2F2012_03%2F120316055029401.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaiross%2Farticle%2Fdetails%2F43733955

lead的语法如下：

test.jsp?url=http%3A%2F%2Fwww.linuxidc.com%2Fupload%2F2012_03%2F120316055029402.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaiross%2Farticle%2Fdetails%2F43733955

lead 和lag 的语法类似以下以lag为例进行讲解！

lag(exp_str,offset,defval) over()

exp_str 指的是要做对比的字段。

offset 是exp_str字段的偏移量，即 offset 为N ，指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。

比如说，

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为3时表示的是我们所要找的数据行就是表中的第一行(即4-3=1)。

offset的默认值为1！

lag()函数的返回值为在表中从当前行位置向前数N行的那一行上exp_str字段的值。

当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值。

比如说，

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为6时表示的是我们所要找的数据行就是表中的第-2行(即4-6=-2)，这就表示我们所要找的数据行不在表中已经超出表的范围了，所以lag()函数将defval这个参数值作为函数的返回值。

default 参数的默认值为空值null，即如果在lag()函数中没有显式设置default

参数值时lag()函数的返回值为空值null。

Lead函数的用法类似。

以下是lag例子：

SCOTT@ www.linuxidc.com> set pagesize 10000

SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 --此时没有设置default 值 则为空值

JAMES CLERK 950 800 --这里的800来自第一行字段sal里的值800

ADAMS CLERK 1100 950

WARD SALESMAN 1250 1100

MARTIN SALESMAN 1250 1250

MILLER CLERK 1300 1250

TURNER SALESMAN 1500 1300

ALLEN SALESMAN 1600 1500

CLARK MANAGER 2450 1600

BLAKE MANAGER 2850 2450

JONES MANAGER 2975 2850

SCOTT ANALYST 3000 2975

FORD ANALYST 3000 3000

KING PRESIDENT 5000 3000

14 rows selected.

注释：

lag(sal) over(order by sal) 解释

over(order by salary)表示意义如下：

首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，

然后，在当前组(此时为整个表的数据行)这个范围里执行排序(即order by salary)，

最后，我们知道分析函数lag(sal)在当前组(此时为整个表的数据行)这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。

参见：

oracle分析函数技术详解(配上开窗函数over())

Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解

设置了default 值之后 第一行对应的值 为500：

SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,1,500) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 500

JAMES CLERK 950 800

ADAMS CLERK 1100 950

WARD SALESMAN 1250 1100

MARTIN SALESMAN 1250 1250

MILLER CLERK 1300 1250

TURNER SALESMAN 1500 1300

ALLEN SALESMAN 1600 1500

CLARK MANAGER 2450 1600

BLAKE MANAGER 2850 2450

JONES MANAGER 2975 2850

SCOTT ANALYST 3000 2975

FORD ANALYST 3000 3000

KING PRESIDENT 5000 3000

14 rows selected.

指定offset的值为2时

SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,2) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800

JAMES CLERK 950

ADAMS CLERK 1100 800

WARD SALESMAN 1250 950

MARTIN SALESMAN 1250 1100

MILLER CLERK 1300 1250

TURNER SALESMAN 1500 1250

ALLEN SALESMAN 1600 1300

CLARK MANAGER 2450 1500

BLAKE MANAGER 2850 1600

JONES MANAGER 2975 2450

SCOTT ANALYST 3000 2850

FORD ANALYST 3000 2975

KING PRESIDENT 5000 3000

14 rows selected.

offset的值为3

SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,3) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800

JAMES CLERK 950

ADAMS CLERK 1100

WARD SALESMAN 1250 800

MARTIN SALESMAN 1250 950

MILLER CLERK 1300 1100

TURNER SALESMAN 1500 1250

ALLEN SALESMAN 1600 1250

CLARK MANAGER 2450 1300

BLAKE MANAGER 2850 1500

JONES MANAGER 2975 1600

SCOTT ANALYST 3000 2450

FORD ANALYST 3000 2850

KING PRESIDENT 5000 2975

14 rows selected.

使用lead分析函数

SCOTT@yangdb> select ename,job,sal ,lead(sal) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 950

JAMES CLERK 950 1100

ADAMS CLERK 1100 1250

WARD SALESMAN 1250 1250

MARTIN SALESMAN 1250 1300

MILLER CLERK 1300 1500

TURNER SALESMAN 1500 1600

ALLEN SALESMAN 1600 2450

CLARK MANAGER 2450 2850

BLAKE MANAGER 2850 2975

JONES MANAGER 2975 3000

SCOTT ANALYST 3000 3000

FORD ANALYST 3000 5000

KING PRESIDENT 5000

14 rows selected.

SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 950

JAMES CLERK 950 1100

ADAMS CLERK 1100 1250

WARD SALESMAN 1250 1250

MARTIN SALESMAN 1250 1300

MILLER CLERK 1300 1500

TURNER SALESMAN 1500 1600

ALLEN SALESMAN 1600 2450

CLARK MANAGER 2450 2850

BLAKE MANAGER 2850 2975

JONES MANAGER 2975 3000

SCOTT ANALYST 3000 3000

FORD ANALYST 3000 5000

KING PRESIDENT 5000

14 rows selected.

SCOTT@yangdb> select ename,job,sal ,lead(sal,2) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 1100

JAMES CLERK 950 1250

ADAMS CLERK 1100 1250

WARD SALESMAN 1250 1300

MARTIN SALESMAN 1250 1500

MILLER CLERK 1300 1600

TURNER SALESMAN 1500 2450

ALLEN SALESMAN 1600 2850

CLARK MANAGER 2450 2975

BLAKE MANAGER 2850 3000

JONES MANAGER 2975 3000

SCOTT ANALYST 3000 5000

FORD ANALYST 3000

KING PRESIDENT 5000

SCOTT@yangdb> select ename,job,sal ,lead(sal,3) over(order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

SMITH CLERK 800 1250

JAMES CLERK 950 1250

ADAMS CLERK 1100 1300

WARD SALESMAN 1250 1500

MARTIN SALESMAN 1250 1600

MILLER CLERK 1300 2450

TURNER SALESMAN 1500 2850

ALLEN SALESMAN 1600 2975

CLARK MANAGER 2450 3000

BLAKE MANAGER 2850 3000

JONES MANAGER 2975 5000

SCOTT ANALYST 3000

FORD ANALYST 3000

KING PRESIDENT 5000

14 rows selected.

lead 的offset N 是以记录的第N行和第一做对比注意末尾的 null 值！

Lead和Lag函数也可以使用分组，以下是使用job 分组的例子：

SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(partition by job order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

FORD ANALYST 3000 3000

SCOTT ANALYST 3000

SMITH CLERK 800 950

JAMES CLERK 950 1100

ADAMS CLERK 1100 1300

MILLER CLERK 1300

CLARK MANAGER 2450 2850

BLAKE MANAGER 2850 2975

JONES MANAGER 2975

KING PRESIDENT 5000

MARTIN SALESMAN 1250 1250

WARD SALESMAN 1250 1500

TURNER SALESMAN 1500 1600

ALLEN SALESMAN 1600

14 rows selected.

SCOTT@yangdb> select ename,job,sal ,lag(sal,1) over(partition by job order by sal) last_sal from emp;

ENAME JOB SAL LAST_SAL

---------- --------- ---------- ----------

FORD ANALYST 3000

SCOTT ANALYST 3000 3000

SMITH CLERK 800

JAMES CLERK 950 800

ADAMS CLERK 1100 950

MILLER CLERK 1300 1100

CLARK MANAGER 2450

BLAKE MANAGER 2850 2450

JONES MANAGER 2975 2850

KING PRESIDENT 5000

MARTIN SALESMAN 1250

WARD SALESMAN 1250 1250

TURNER SALESMAN 1500 1250

ALLEN SALESMAN 1600 1500

14 rows selected.

SCOTT@yangdb>

使用分析函数的时候注意空值 或者null 给数据带来的影响，数据是否允许为空或者null计算的时候会导致一定的差错 比如 800-null 肯定为null！这个结果是否是应用想要的结果？

细心很重要！！尤其是在计算和钱有关的情况下！！

f68f2add0b68e4f9810432fce46917b7.png

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

程小新同学
关注
LAG函数和LEAD函数（案例详解）
清平乐的技术专栏
04-29 6245
在SQL的开窗函数使用中，LAG函数和LEAD函数出现频率还是很高的。这里博主将这两个函数的用法结合案例做一下介绍。 一、函数介绍 LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名， 第二个参数为往上第n行（可选，默认为1）， 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL） LEAD函数与LAG相反 二、案例演示 注：这里用HiveSQL演示（Oracle、MySQL同理） 示例1：基础含义 数据准备 DROP TABLE IF EXIS
查询连续三个值相同的值SQL函数之lag() over()、lead() over()函数
wqr111的博客
04-23 1358
题目：在数据库中查询连续出现三次的id 表：Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id 是这个表的主键。 编写一个 SQL 查询，查找所有至少连续出现三次的数字。 返回的结果表中的数据可以按 任意顺序 排列。 查询...
【mysql】使用变量实现类似oracle中lag函数功能
debimeng
07-07 2318
mysql使用变量实现类似oracle中的lag函数功能 说明： 有一个订单表，每次下单都会记录是否使用了券，现模拟一个需求，将订单表插入日志表达到记录用户上一次是否用券以及此订单是否用券的情况 #--------实现： #创建表t_order create table t_order(id int,name varchar(50),time date); #插入数据 inser...
【Mysql系列】LAG和LEAD函数
热门推荐
檀越的博客
12-07 2万+
💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨。
mysql lag和lead_oracle 统计分析函数之lead和lag
weixin_39833270的博客
01-25 163
无需添加内联结构便可以是实现访问当前行附近行的字段的lead :向后 lag: 向前. 语法结构： lag(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause) lead(value_expr [,offset][,default]) over([query_partition...
mysql中lead_MySQL LEAD() and LAG()用法及代码示例
weixin_39563823的博客
01-26 1403
MySQL中的LEAD()和LAG()函数用于获取其分区内任何行的前后值。这些功能称为非聚合窗口功能。窗口函数是对分区或窗口的每一行执行操作的那些函数。这些函数为每个查询行生成结果，这与将它们分组并在单个行中生成结果的聚合函数不太可能。发生操作的行称为当前行。表现为当前行或使用哪个功能在当前行上不透明的行的集合称为“窗口”。LAG()函数用于从当前行之前的行中获取值。LEAD()函数用于从成功当前...
mysql 获取上一条记录的值 类似 oracle lag函数
liuyongli_0618的博客
08-10 882
mysql 类似 oracle lag函数 mysql 类似 oracle lag函数 分享一下 set @lagfield:=null; select @lagfield, (select @lagfield:=t.id) r, t.* from table t @lagfield 为上一条记录要拿的字段的值 t.id 为表table的一个字段 注意：1.set要加上 没有set 会出现 第一...
Mysql 与 Oracle 基本语法的对照
yuyan64的博客
12-15 700
Mysql 与 Oracle 基本语法的对照 函数类型 oracle（11g） mysql（8.0.20） 语法 例子 窗口函数 over() over() 一致 Over()窗口函数最常见的搭配有以下几种： 1. rank(),dense_rank(),row_number() + over(partition by … order by …) 排名 2. sum(),avg(),count()聚合函数+over(partition .
OceanBase兼容mysql_OceanBase 2.1 的ORACLE兼容性能力探秘
weixin_32969997的博客
01-28 1589
OceanBase是一款通用的分布式关系型数据库，目前内部业务使用比较多有两个版本：1.4和2.1。OceanBase每个版本变化总能带给人很多惊喜，其中2.1版本实现了ORACLE很多特性的兼容。本文主要简单浏览一下这些新功能。运维账户管理在ORACLE Mode的租户里，创建用户依然是create user命令，密码不用单引号。授权方式还是还是ORACLE和MySQL的结合。查看权限方式是My...
MySQL使用之二_WHERE子句、JOIN、UNION、DISTINCT、ORDER、LIMIT、窗口函数
IMchg的博客
09-05 530
一 WHERE子句 where子句中对字段值进行条件判断，条件的来源可以是：基本条件、子查询结果、函数结果、正则表达式。 1.1 基本条件：NOT、LIKE、IN、Between 1.1.1 NOT：对条件值取反 1.1.2 LIKE：字符串模糊匹配 LIKE ‘模式串’，精简版的正则表达式，价值在于与通配符"%"、"_“合用；”%“相当于正则表达式中的 “.*” 、”_“相当于正则表达式中的”."； where name LIKE 'abc'：表示name的值等于abc where name LIKE '
mysql实现LAG函数.pdf
12-20
最近工作中用到 Mysql 要实现 oracle 的 lag 函数，网络上搜了不少文章，大都格式不清晰，或说明不清楚，所以自己做出例子来留个记录同时也分享一下。注解清晰，代码可直接用，建表样例数据sql都完整，直接可以跑。
mysql实现oracle的分析函数lag和rank
lihengfang的专栏
01-04 2803
利用mysql的变量来实现： set @lag:=0,@currs:=0,@rank:=0; SELECT name,@lag:=@currs as lag,(@currs:=acct_no) as current_value,@rank:=if(@currs from a_acct ORDER BY acct_no;
MySQL函数模拟Oracle序列
qq_36278106的博客
12-20 317
MySQL函数模拟Oracle序列MySQL模拟Oracle序列建表语句 MySQL模拟Oracle序列 Oracle数据库迁移到MySQL的过程中，需要解决MySQL中没有Oracle的序列功能，本人的解决方案是通过一张表用来记录Oracle中序列的名称、当前序列的值及每次步进长度，通过俩个函数来模拟序列自增，具体代码如下: 建表语句 建表语句. CREATE TABLE `sys_sequen...
mysql lag和lead_MS SQL Server2012中的LEAD和LAG函数
weixin_36368932的博客
01-27 176
为了理解这两个函数，可以先参考下面这两篇，http://www.cnblogs.com/insus/archive/2012/03/18/2404288.html和 http://www.cnblogs.com/insus/archive/2012/03/23/2415041.html可以获取在某一种排序下的某一记录的前一个值或后一个值。现sQL Server 2012带来这两个函数，可以方便...
mysql lag和lead_lag和lead函数的用法
weixin_42501298的博客
01-18 807
今天刚开通了博客，随便学了点，网上写写。当然刚开始肯定不是写的肯定不是很好的，但是总比不写强点，慢慢的进步么！！！！主要是lag和lead函数的用法，举两个例子来说明这两个函数是偏移量函数，可以查出一个字段的上一个值或者下一个值，配合over来使用。如全表查询select * from tmp_cmu t 结果如下ITEM_NO PERIOD CMU--...
SQL开窗函数之前后函数（LEAD、LAG）
weixin_46599926的博客
12-11 1万+
本文主要关于SQL开窗函数之LEAD和LAG的用法
【Mysql系列】LAG与LEAD开窗函数
最新发布
檀越的博客
11-22 1万+
在数据库查询中，开窗函数（Window Functions）是一种强大的工具，用于在结果集的子集上执行计算。LAG和LEAD是两个常用的开窗函数，它们允许你在当前行的前面或后面的行中访问数据。在数据库中，LAG和LEADLAG函数的全称是"LAST VALUE"。LEAD函数的全称是"LEAD VALUE"。这两个函数是 SQL 标准中的开窗函数，它们允许在查询结果的子集上执行类似于聚合函数的计算，但不会导致结果集的单一行。它们的目的是在当前行的前面（LAG）或后面（LEAD。
MySQL中LAG()函数和LEAD()函数的使用
shyの个人笔记
08-12 1万+
MySQL中LAG()函数和LEAD()函数的使用
MySQL 8.0窗口函数实战教程：入门与实例分析
在MySQL 8.0以前，进行数据排名和统计分析在MySQL中是一项相对复杂的工作，因为缺乏与Oracle、SQL Server和PostgreSQL等数据库类似的窗口函数功能。然而，随着MySQL 8.0版本的更新，引入了窗口函数，极大地提高了这...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值