LAG and LEAD Analytic Functions
Simple examples of how to use the LAG and LEAD analytic functions.
oracle miscconfigurationintermediate
by OracleDba
19 views
📋Steps in this guide1/5
1
Setup
The examples in this article require the following table.
Code/Command (click line numbers to comment):
12345678910111213141516171819202122232425262728
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;2
Introduction
Both and functions have the same usage, as shown below. The analytic clause elements are described in more detail here .
- - Can be a column or a built-in function, except for other analytic functions.
- - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
- - The value returned if the offset is outside the scope of the window. The default value is NULL.
Looking at the table, we query the data in salary ( ) order.
Code/Command (click line numbers to comment):
12345678910111213141516171819202122232425262728293031323334353637
LAG
{ ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
LEAD
{ ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
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
SQL>3
LAG
The function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the of the function is used to order the data by salary.
If the would span a partition boundary, the default value is returned. In the following example we partition by department, so the column has a default value of "0" for the first row in each department.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
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
SQL>
SELECT deptno,
empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev
FROM emp;
DEPTNO EMPNO ENAME JOB SAL SAL_PREV
---------- ---------- ---------- --------- ---------- ----------
10 7934 MILLER CLERK 1300 0
10 7782 CLARK MANAGER 2450 1300
10 7839 KING PRESIDENT 5000 2450
20 7369 SMITH CLERK 800 0
20 7876 ADAMS CLERK 1100 800
20 7566 JONES MANAGER 2975 1100
20 7788 SCOTT ANALYST 3000 2975
20 7902 FORD ANALYST 3000 3000
30 7900 JAMES CLERK 950 0
30 7654 MARTIN SALESMAN 1250 950
30 7521 WARD SALESMAN 1250 1250
30 7844 TURNER SALESMAN 1500 1250
30 7499 ALLEN SALESMAN 1600 1500
30 7698 BLAKE MANAGER 2850 1600
SQL>4
LEAD
The function is used to return data from rows further down the result set. The following query returns the salary from the next row to calculate the difference between the salary of the current row and the following row.
If the would span a partition boundary, the default value is returned. In the following example we partition by department, so the column has a default value of "0" for the last row in each department.
Code/Command (click line numbers to comment):
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
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
SQL>
SELECT deptno,
empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_next
FROM emp;
DEPTNO EMPNO ENAME JOB SAL SAL_NEXT
---------- ---------- ---------- --------- ---------- ----------
10 7934 MILLER CLERK 1300 2450
10 7782 CLARK MANAGER 2450 5000
10 7839 KING PRESIDENT 5000 0
20 7369 SMITH CLERK 800 1100
20 7876 ADAMS CLERK 1100 2975
20 7566 JONES MANAGER 2975 3000
20 7788 SCOTT ANALYST 3000 3000
20 7902 FORD ANALYST 3000 0
30 7900 JAMES CLERK 950 1250
30 7654 MARTIN SALESMAN 1250 1250
30 7521 WARD SALESMAN 1250 1500
30 7844 TURNER SALESMAN 1500 1600
30 7499 ALLEN SALESMAN 1600 2850
30 7698 BLAKE MANAGER 2850 0
SQL>5
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
- LAG and LEAD Analytic Functions
- Analytic Functions : All Articles
- Analytic Functions
- Top-N Queries
- Analytic Functions (Docs)
- LAG
- LEAD
Hope this helps. Regards Tim...
Comments (0)
Please to add comments
No comments yet. Be the first to comment!