【分析函数】Oracle分析函数之LAG和LEAD

【分析函数】Oracle分析函数之LAG和LEAD

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

lag的语法如下：

lead的语法如下：

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

lag(exp_str,offset,defval) over()

exp_str 是要做对比的字段

offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！

defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

以下是lag和lead的例子

SCOTT@yangdb> set pagesize 10000
SCOTT@yangdb> 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
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.
设置了default 值之后 第一行对应的值 为500

SCOTT@yangdb> 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@yangdb> 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@yangdb> 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！这个结果是否是应用想要的结果？

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


lag和lead之IGNORE NULLS

Oracle 11g增强LEAD和LAG函数的语法，加入了{RESPECT | IGNORE} NULLS，当前行为NULL，想找出该列上一个不为NULL的值，怎么办呢，用IGNORE NULLS吧。


drop table ta;
create table ta as select level id,dummy||level x from dual connect by level<=10;

UPDATE TA SET X='' WHERE ID BETWEEN 3 AND 7;

SELECT T.ID,
       T.X,
       NVL(T.X, LAG(T.X IGNORE NULLS) OVER(ORDER BY T.ID)) A,
       NVL(T.X, LAG(T.X RESPECT NULLS) OVER(ORDER BY T.ID)) B,
       NVL(T.X, LAG(T.X) OVER(ORDER BY T.ID)) C
  FROM TA T
 ORDER BY T.ID;

About Me


...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
时间： 2024-11-08 22:14:00

【分析函数】Oracle分析函数之LAG和LEAD的相关文章

Oracle 11g r2分析函数新特性简介（三）分析函数LAG和LEAD的增强

在11gr2中,Oracle分析函数的功能进一步增强. 这篇介绍分析函数LAG和LEAD的增强. 11gr2对LAG和LEAD函数进行了增强,添加了IGNORE NULLS的功能. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database11gEnterprise Edition Rele

【Oracle】oracle的LAG和LEAD分析函数

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值.这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率. lag的语法如下: lead的语法如下: lead 和lag 的语法类似以下以lag为例进行讲解! lag(exp_str,offset,defval) over() exp_str 是要做对比的字段 offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比,第二行和第四行,依次类推,o

Oracle分析函数、多维函数和Model函数的简要说明

以下代码均经过测试,可直接运行 Oracle分析函数.多维函数和Model函数简要说明,主要针对BI报表统计,不一定很全面,但对BI应用场景做了少许说明 --创建一张销售数量表,数据趋势是递增的 CREATE TABLE ComputerSales AS SELECT 120+TRUNC(rn/12)+ROUND(DBMS_RANDOM.VALUE(1,10)) SalesNumber FROM ( SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWN

ORACLE分析函数(5) 其他

1.除了使用数字来指定窗口范围,我们还可以使用日期类型,如: 2.lead和lag,返回当前窗口中与当前记录距离为n的记录.lag为向前取记录,lead为向后取记录 SELECT prod_id, lag(prod_list_price,1) over(order by prod_id) pre_1, lag(prod_list_price,2) over(order by prod_id) pre_2, prod_list_price, lead(prod_list_price,1) over

常用Oracle分析函数大全_oracle

Oracle的分析函数功能非常强大,工作这些年来经常用到.这次将平时经常使用到的分析函数整理出来,以备日后查看. 我们拿案例来学习,这样理解起来更容易一些. 1.建表 create table earnings -- 打工赚钱表 ( earnmonth varchar2(6), -- 打工月份 area varchar2(20), -- 打工地区 sno varchar2(10), -- 打工者编号 sname varchar2(20), -- 打工者姓名 times int, -- 本月打工次

Oracle分析函数概述

Oracle分析函数概述 Oracle中的分析函数和聚合函数相似,但是对于每一组记录,无论 多少行,聚合函数只返回一行值,而分析函数对其中每一行记录都返回值.这一组记录,称 为分析函数的一个(WINDOW),窗口决定了要处理数据的范围,该范围在物理上可以由指定 的行数来确定,或者在逻辑上由相对偏移量来确定.分析函数总是在除了ORDER BY之外的其 他子句运算后才执行的,所以它不能出现在where.group by等子句中,只能出现在select列 表和order by子句中. 准备测试数据 在

ORACLE分析函数(8) WIDTH-BUCKET

WIDTH-BUCKET会根据参数设置,返回当前记录所属的bucket number.语法格式如下:       WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)        第一个参数,为某数字或者日期表达式:第二个参数为某范围的下限:第三个参数为某范围的上限:第四个参数为对某范围进行等值划分bucket的数量.如 WIDTH_BUCKET(expression, 0, 2000, 4),会

ORACLE分析函数(2)

下面,我们来解析一下分析函数的语法格式(语法格式图请参照:http://blog.csdn.net/yidian815/article/details/12709223). 函数名称 对it人士来时,再简单不过的东西了,不做描述. 函数参数: 分析函数通常会具有0-3个参数. 分区子句: 通过分区子句,可以对记录集进行分区,然后针对每个分区分别进行统计运算.在分析函数中,使用分区子句不必使用()将子句包围起来.在一个查询当中,我们可以使用多个分析函数,每个分析函数可以使用独立的分区规则.如果没有

ORACLE分析函数（1）

分析函数式ORACLE提供的用来进行数据统计的强有力工具,与我们常用的聚合函数具有一些相似性,但又完全不同.聚合函数,首先会将数据进行分组,然后对每一组数据进行运算,如求和sum,求平均AVG等,对于聚合函数,每一个分组返回一条数据结果行.分析函数,同样也会对数据进行求和或者求平均等运算,但是,分析函数计算的基础并不是分组,而是窗口,而且分析函数根据每条记录会返回一个处理结果. 首先,看一下下面的例子: SQL> select prod_subcategory_id,1 from product