【Oracle】oracle的LAG和LEAD分析函数
Oracle 函数 分析 lead lag
2023-09-14 08:57:29 时间
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率。
lag的语法如下:![22664653_201203141135381.jpg](http://blog.itpub.net/attachments/2012/03/22664653_201203141135381.jpg)
![22664653_201203141135461.jpg](http://blog.itpub.net/attachments/2012/03/22664653_201203141135461.jpg)
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!这个结果是否是应用想要的结果? 细心很重要!!尤其是在计算和钱有关的情况下!!
相关文章
- 【C/C++学院】(24)Oracle数据库编程--管理oracle
- 【Oracle】并行等待之PX Deq: Signal ACK
- Oracle - 拼接多个字段 - wm_concat()函数
- 《oracle每天一练》Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Oracle cmd乱码
- oracle分析函数与over()(转)
- Oracle计算时间函数(对时间的加减numtodsinterval、numtoyminterval) (转)
- oracle的substr函数的用法
- oracle round 函数,replace()函数
- Oracle使用审计监控用户执行过的SQL语句
- oracle的start with connect by prior如何使用
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之RAC 工作原理和相关组件(三)
- PostgreSQL Oracle兼容性之 - NEW_TIME , SYS_EXTRACT_UTC
- Oracle Profile
- oracle参数文件
- Oracle 11g 在使用时应该启动哪些服务?
- Oracle Greatest()函数
- oracle 统计语句 与常见函数的归纳(未完待续)
- Oracle中添加虚拟列(emp是oracle自带表)
- SpringAccess数据库(oracle)构造
- PostgreSQL的学习心得和知识总结(四十四)|语法级自上而下完美实现Oracle数据库DECODE函数的实现方案(GreenPlum & AntDB)
- oracle之单行函数
- PLSQL专项学习之oracle函数EXTRACT
- Oracle的学习心得和知识总结(三)|Oracle数据库重要函数详解 单行函数(一)(建议收藏 持续更新...)
- 04、Oracle转换函数和条件表达式