ORACLE- ANALYTIC FUNCTIONS LEAD and LAG
LEAD : It is a analytic function which fetches value of a column from next row. If the value in next row goes out of table or partition, default NULL value is populated. The best part of this function is that it gives access to more than one row in a table without using SELF JOIN.
SYNTAX:
LEAD (expression [,offset] [,default]) OVER ([ partition_clause] orderby_clause)
expression: It can be column name or other built in function
offset (optional): Number of rows to lead. Default value is 1.
default (optional): Default value to be populated if next value goes out of table or partition .Default value is NULL
partition_clause (optional): It is used to partition the results into groups.
orderby_clause : It is used to sort the data.
Employee_Details table is used for illustration as below:
 |
| Employee_Details |
EXAMPLE (Without PARTITION BY clause):
SELECT EMPID,EMPNMAE,GENDER,SALARY,
LEAD(SALARY)OVER(ORDER BY SALARY) LEAD_EAXAMPLE
FROM EMPLOYEE_DETAILS;
 |
| Employee_Details |
LEAD_EXAMPLE column is populated with SALARY column from a next now. LEAD_EXAMPLE in last row is populated as NULL as the next row goes out of table.
EXAMPLE (With PARTITION BY clause):
SELECT EMPID,EMPNMAE,GENDER,SALARY,
LEAD(SALARY)OVER(PARTITION BY GENDER ORDER BY SALARY)
AS LEAD_EXAMPLE
FROM EMPLOYEE_DETAILS;
 |
| Employee_Details |
LEAD_EXAMPLE column is populated with SALARY column from a next now. LEAD_EXAMPLE column in 4th row and last last row is populated as NULL as the next row goes out of partition and out of table respectively.
LAG: It is a analytic function which fetches value of a column from next row. If the value in previous row goes out of table or partition, then default NULL value is populated. The best part of this function is that it gives access to more than one row in a table without using SELF JOIN.
SYNTAX:
LAG (expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
expression: It can be column names or other built in function
offset (optional): Number of rows to lag. Default value is 1.
default (optional): Default value to be populated if next value goes out of table or partition. Default value is NULL
partition_clause (optional): It is used to partition the results into groups
orderby_clause : It is used to sort the data.
EXAMPLE (Without PARTITION BY clause):
SELECT EMPID,EMPNMAE,GENDER,SALARY,
LAG(SALARY)OVER(ORDER BY SALARY)LAG_EAXAMPLE
FROM EMPLOYEE_DETAILS;
 |
| Employee_Details |
LAG_EXAMPLE column is populated with SALARY column from a previous row. LAG_EXAMPLE column in first row is populated as NULL as the previous row goes out of table.
EXAMPLE (With PARTITION BY clause):
SELECT EMPID,EMPNMAE,GENDER,SALARY,
LAG(SALARY)OVER(PARTITION BY GENDER ORDER BY SALARY)
AS LAG_EXAMPLE
FROM EMPLOYEE_DETAILS;
 |
| Employee_Details |
LAG_EXAMPLE column is populated with SALARY column from a previous row. LAG_EXAMPLE in 1st row and 5th row is populated as NULL as the previous row goes out of partition and out of table respectively.
If you have like my post. Don't forget to put a comment and share the post.
No comments:
Post a Comment