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_diff FROM 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 2000 14 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_diff FROM 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 -5000 14 rows selected. SQL>http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php