lag/lead分析函数
来源:互联网 发布:淘宝法克鞋店 编辑:程序博客网 时间:2024/05/07 10:16
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
value_expression
- 可以是一个列名也可以是一个内置函数offset
- 偏移的行数默认为1.default
- 如果是首行(lag)或末行(lead)的返回值. 默认值为 NULL.
SELECT empno,
ename,
job,
sal
FROM emp
ORDER BY sal;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000
14 rows selected.
SQL>
SELECT empno, ename, job, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diffFROM emp; EMPNO ENAME JOB SAL SAL_PREV SAL_DIFF---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 0 800 7900 JAMES CLERK 950 800 150 7876 ADAMS CLERK 1100 950 150 7521 WARD SALESMAN 1250 1100 150 7654 MARTIN SALESMAN 1250 1250 0 7934 MILLER CLERK 1300 1250 50 7844 TURNER SALESMAN 1500 1300 200 7499 ALLEN SALESMAN 1600 1500 100 7782 CLARK MANAGER 2450 1600 850 7698 BLAKE MANAGER 2850 2450 400 7566 JONES MANAGER 2975 2850 125 7788 SCOTT ANALYST 3000 2975 25 7902 FORD ANALYST 3000 3000 0 7839 KING PRESIDENT 5000 3000 200014 rows selected.SQL>
SELECT empno, ename, job, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diffFROM emp; EMPNO ENAME JOB SAL SAL_NEXT SAL_DIFF---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 950 150 7900 JAMES CLERK 950 1100 150 7876 ADAMS CLERK 1100 1250 150 7521 WARD SALESMAN 1250 1250 0 7654 MARTIN SALESMAN 1250 1300 50 7934 MILLER CLERK 1300 1500 200 7844 TURNER SALESMAN 1500 1600 100 7499 ALLEN SALESMAN 1600 2450 850 7782 CLARK MANAGER 2450 2850 400 7698 BLAKE MANAGER 2850 2975 125 7566 JONES MANAGER 2975 3000 25 7788 SCOTT ANALYST 3000 3000 0 7902 FORD ANALYST 3000 5000 2000 7839 KING PRESIDENT 5000 0 -500014 rows selected.SQL>http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
- lag/lead分析函数
- lag和lead 分析函数
- lag和lead 分析函数
- Oracle分析函数Lead(),Lag()
- lag和lead 分析函数
- oracle 分析函数 LAG、LEAD
- ORACLE分析函数 lag lead
- lag和lead 分析函数
- Lead()和Lag() 分析函数
- 分析函数——lead与lag
- Oracle lag()/lead() over()分析函数
- oracle下lag和lead分析函数
- oracle 分析函数lag 和 lead 简介
- 分析函数lag和lead详解
- oracle lag与lead分析函数简介
- Oracle的LAG和LEAD分析函数
- Oracle下lag和lead分析函数
- Oracle的LAG和LEAD分析函数
- 生活本是一场闹剧
- log4j日志配置
- Linux启动过程
- c语言socket编程指南
- webwork入门
- lag/lead分析函数
- 又一个幻灯片代码,复制过来的
- 儿子于靖洋220天照片
- Email电表Q4c协议解析
- 观察者模式
- 给wince驱动/应用初学者推荐的书籍
- 深入浅出多线程(4)对CachedThreadPool OutOfMemoryError问题的一些想法
- domino数据库存取控制列表(acl)基本知识_lotus notes
- 见证Spring Jdbc的强大