备案控制台
开发者社区 数据库 文章 正文

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

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

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

lag的语法如下：
22664653_201203141135381.jpg
lead的语法如下：
22664653_201203141135461.jpg
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！这个结果是否是应用想要的结果？
细心很重要！！尤其是在计算和钱有关的情况下！！
文章标签：
关系型数据库
Oracle
关键词：
Oracle数据库一键上云函数
Oracle数据库一键上云分析
Oracle数据库一键上云分析函数
Oracle数据库一键上云lag
Oracle数据库一键上云lag函数
北在南方
目录
相关文章
游客pnral34kl5xba
|
1天前
|
存储 Oracle NoSQL
Oracle中decode函数详解
Oracle中decode函数详解
游客pnral34kl5xba
8 0
老板这功能得加钱
|
1月前
|
SQL Oracle 前端开发
Oracle效率分析，Github标星25K+超火的前端实战项目
Oracle效率分析，Github标星25K+超火的前端实战项目
老板这功能得加钱
14 1
wljslmz
|
1月前
|
NoSQL Oracle 关系型数据库
MongoDB与Oracle：管道函数兼容之道
【4月更文挑战第20天】
wljslmz
14 2
1941623231718325
|
1月前
|
Oracle 关系型数据库 数据管理
Oracle常用系统函数之日期和时间类函数：时空穿梭者的魔法棒
【4月更文挑战第19天】Oracle数据库中的日期和时间函数是强大的工具，如同时空穿梭者的魔法棒。`SYSDATE`和`CURRENT_TIMESTAMP`能显示当前时间，助你在数据中记录关键时刻；`ADD_MONTHS`函数让你轻易跃过月份，实现时间穿越；而`TO_DATE`和`TO_CHAR`则负责日期和时间的格式转换，如同时间的化妆师。除此之外，还有`EXTRACT`、`LAST_DAY`等更多魔法道具，帮助数据管理专家们自如操控时间，解决各种挑战。掌握这些函数，你也能在数据世界中自由穿梭！
1941623231718325
31 2
1941623231718325
|
1月前
|
Oracle 算法 关系型数据库
Oracle常用系统函数之数字类函数：数字的魔术师
【4月更文挑战第19天】Oracle数据库中的数字类函数是数字处理的魔术师，包括`ROUND`（四舍五入），`CEIL`和`FLOOR`（向上/下取整），以及`ABS`（计算绝对值）。还有`MOD`、`TRUNC`和`POWER`等函数，提供求余数、截断和计算幂的功能。熟练运用这些函数能提升数据管理效率，让处理数字变得更简单、有趣。
1941623231718325
17 1
1941623231718325
|
1月前
|
存储 SQL Oracle
Oracle创建函数：数据王国的“魔法秘籍”
【4月更文挑战第19天】Oracle函数是数据处理的利器，专注于计算与返回值。通过`CREATE OR REPLACE FUNCTION`定义函数名、参数及返回类型，如示例中的加法函数`add_numbers`。调用函数时，可将其结果赋值给变量。函数可包含复杂逻辑和SQL，与其他数据库对象交互，用于触发器或存储过程。注意函数设计的唯一性、参数类型和性能优化。掌握函数使用能提升代码可读性和数据库管理效率。
1941623231718325
23 0
1941623231718325
|
1月前
|
Oracle 关系型数据库 数据挖掘
Oracle常用系统函数之聚集函数：数据的统计大师
【4月更文挑战第19天】Oracle的聚集函数是数据统计的强大工具，包括`SUM`（求和）、`COUNT`（计数）、`AVG`（平均值）、`MAX`和`MIN`（最大值和最小值）、`STDDEV`（标准差）等。这些函数帮助我们汇总、分析数据，提供丰富的统计信息。熟练掌握这些函数，能提升数据分析效率，助你成为数据大师。
1941623231718325
31 0
1941623231718325
|
1月前
|
Oracle 关系型数据库 数据管理
Oracle常用系统函数之字符类函数：文字的魔法师
【4月更文挑战第19天】Oracle数据库中的字符类函数是字符串处理的魔法师：`INITCAP`首字母大写，`TRIM`去除多余字符，`CONCAT`拼接字符串。还有`SUBSTR`、`LENGTH`、`UPPER`和`LOWER`等，助你轻松应对字符串问题，成为数据管理专家！
1941623231718325
13 0
穆雄雄.
|
1月前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析2
oracle基本笔记整理及案例分析2
穆雄雄.
22 0
穆雄雄.
|
1月前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析1
oracle基本笔记整理及案例分析1
穆雄雄.
29 0

热门文章

最新文章

  • 1
    Oracle创建表空间及用户
  • 2
    [20160222]Oracle 11G Data Guard Failover
  • 3
    ORACLE 动态注册和静态注册的区别(转)
  • 4
    DBA_Oracle Audit基本概念（概念）
  • 5
    Oracle跨库链接操作
  • 6
    【原】Oracle回收站功能开启与关闭
  • 7
    详解ASP.NET的最新安全漏洞，Padding Oracle攻击原理及其他
  • 8
    [Oracle]跨越 DBLINK 访问表时，数据缓存在何处的Data Buffer 中？
  • 9
    Oracle动态创建序列和触发器生成键
  • 10
    不装oracle进行远程连接解决办法 .
  • 1
    DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本，并遇到了与RAW字段相关的语法错误，该怎么处理
    43
  • 2
    DataWorks产品使用合集之在 DataWorks 中，使用Oracle作为数据源进行数据映射和查询，如何更改数据源为MaxCompute或其他类型
    39
  • 3
    MongoDB与Oracle：管道函数兼容之道
    14
  • 4
    Oracle索引知识看这一篇就足够
    31
  • 5
    Oracle 性能优化之AWR、ASH和ADDM(含报告生成和参数解读)
    128
  • 6
    一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（下）
    154
  • 7
    一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（上）
    163
  • 8
    Oracle 数据泵导出导入(映射表空间、Schema)
    29
  • 9
    不小心删除表或数据后，如何利用Oracle的闪回进行恢复
    37
  • 10
    Oracle PLM Agile936 单点登录配置方式
    30

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考