LAG and LEAD Analytic Functions in Oracle SQL

LAG and LEAD Analytic Functions in Oracle SQL

LAG
LAG is used for getting the access of more row without using the self join in query. It will return previous series of rows returned from query with cursor position.
LAG provides access to a row previous/prior data from current rows.

LAG(col/expression, offset position, value )
Note:
offset: position is row position offset value
value: default value is null otherwise specify your value if don’t find any data.

LEAD
LEAD function is used to getting the access of more row without using the self join in query.
LEAD provides access to a row at a given physical offset beyond that position.

LEAD(col/expression, offset position, value )
Note:
offset: position is row position offset value
value: default value is null otherwise specify your value if don’t find any data.

Example of LAG function:
Return the salary of employee and previous 2 salaries of employees hired before the employee sorted by hire_date and department = 90
Note: If you doesn’t specify 3 parameter value then default is null value, or don’t specify 2nd parameter default is 1.

-Example with all three parameter defines first as column value , second as offset position prior row, third as value of no rows present previously.
col last_name for a10
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0 ) OVER (ORDER BY hire_date) AS prev_sal
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY   PREV_SAL
--------- ---------- ---------- ----------
13-JAN-01 De Haan         17000          0
17-JUN-03 King            24000      17000
21-SEP-05 Kochhar         17000      24000

--Example offset is set to 2, third parameter is default null, it show you difference of values
col last_name for a10
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0 ) OVER (ORDER BY hire_date) AS prev_sal,
LAG(salary, 2) OVER (ORDER BY hire_date) AS Sec_prev_sal
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY   PREV_SAL SEC_PREV_SAL
--------- ---------- ---------- ---------- ------------
13-JAN-01 De Haan         17000          0
17-JUN-03 King            24000      17000
21-SEP-05 Kochhar         17000      24000        17000

--Example as Default used without parameter
col last_name for a10
SELECT hire_date, last_name, salary,
LAG(salary ) OVER (ORDER BY hire_date) AS example_first_par
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY EXAMPLE_FIRST_PAR
--------- ---------- ---------- -----------------
13-JAN-01 De Haan         17000
17-JUN-03 King            24000             17000
21-SEP-05 Kochhar         17000             24000

Example of Lead function:
Return the next row value with lead function

-- Example used all three parameter defines
col last_name for a10
SELECT hire_date, last_name, salary,
LEAD(salary, 1, 0 ) OVER (ORDER BY hire_date) AS next_salary
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY NEXT_SALARY
--------- ---------- ---------- -----------
13-JAN-01 De Haan         17000       24000
17-JUN-03 King            24000       17000
21-SEP-05 Kochhar         17000           0

-- Example with offset value to 2 rows,
col last_name for a10
SELECT hire_date, last_name, salary,
LEAD(salary, 1, 0 ) OVER (ORDER BY hire_date) AS next_sal,
LEAD(salary, 2) OVER (ORDER BY hire_date) AS Sec_next_sal
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY   NEXT_SAL SEC_NEXT_SAL
--------- ---------- ---------- ---------- ------------
13-JAN-01 De Haan         17000      24000        17000
17-JUN-03 King            24000      17000
21-SEP-05 Kochhar         17000          0	 

--Example without parameter
col last_name for a10
SELECT hire_date, last_name, salary,
LEAD(salary ) OVER (ORDER BY hire_date) AS Next_salary
FROM employees where department_id = 90
ORDER BY hire_date;

HIRE_DATE LAST_NAME      SALARY NEXT_SALARY
--------- ---------- ---------- -----------
13-JAN-01 De Haan         17000       24000
17-JUN-03 King            24000       17000
21-SEP-05 Kochhar         17000		 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.