ORACLE ANALYTICAL FUNCTIONS – 1
In this article, we will see how Analytical functions works in Oracle.
Analytical Function :
Analytical function is used to compute the aggregate values and perform aggregate operations it returns more than value in each group In sql we have different types of analytical functions are there .
They are
1)LAG
2)LEAD
3)FIRST_VALUE
4)LAST_VALUE
5)KEEP FIRST
6)KEEP LAST
1.LAG()
Both functions are used to compare value of current row with previous of following rows that means compare current row value with previous or next row value .
LAG():
It is used to compare previous row value with current row value.This functions accept three parameter along with over clause. In the syntax offset and default values are optional if you are not using by default offset value it will take 1 and default_value will take null and offset value represent which row value will be lag first(it represent
position) and default_value returns value in case of row value is null and partition by clause is optional and order by clause is mandatory .
Syntax :
lag(column_name,[offset],[default_value]) over (partition by column_name order by column_name[asc/desc])
For example here we take emp table
- 1)select * from emp;
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
| 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 17-NOV-81 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | 10 | |
| 7002 | lakshmi | clerk | 7902 | 1500 | 1500 | 20 |
2)select ename,sal ,lag(sal) over(order by sal desc) previous_row1 from emp ;
| ENAME | SAL | PREVIOUS_ROW1 |
| KING | 5000 | |
| FORD | 3000 | 5000 |
| SCOTT | 3000 | 3000 |
| JONES | 2975 | 3000 |
| BLAKE | 2850 | 2975 |
| SMITH | 2500 | 2850 |
| CLARK | 2450 | 2500 |
| ALLEN | 1600 | 2450 |
| TURNER | 1500 | 1600 |
| lakshmi | 1500 | 1500 |
| MILLER | 1300 | 1500 |
| WARD | 1250 | 1300 |
| MARTIN | 1250 | 1250 |
| ADAMS | 1100 | 1250 |
| JAMES | 950 | 1100 |
In the above example we are lagging the first previous row value .That means we are getting null value in the first previous_row value why because there is no previous row value against king employee. Here we have used lag(sal) by default offset value will take one and default value is null that’s why we are getting the result from the first previous row value .
3)select ename,sal,lag(sal,2) over(order by sal desc) previous_row2 from emp
| ENAME | SAL | PREVIOUS_ROW2 |
| KING | 5000 | |
| FORD | 3000 | |
| SCOTT | 3000 | 5000 |
| JONES | 2975 | 3000 |
| BLAKE | 2850 | 3000 |
| SMITH | 2500 | 2975 |
| CLARK | 2450 | 2850 |
| ALLEN | 1600 | 2500 |
| TURNER | 1500 | 2450 |
| lakshmi | 1500 | 1600 |
| MILLER | 1300 | 1500 |
| WARD | 1250 | 1500 |
| MARTIN | 1250 | 1300 |
| ADAMS | 1100 | 1250 |
| JAMES | 950 | 1250 |
In the above example we are lagging the second previous row value .That means we are getting null value in the first two rows why because there is no previous tow row values against king employee and ford and we are getting previous_row2 value for the scott employee why because the scott employee having previous two row value 5000 that’s why Scott employee onwards we are getting lag value. In the above example we have used lag(sal,2) by default offset value 2 it will lag the previous second row for the each current row if in case mention default_value like 1 if you get 1 in place null values .
4)select deptno,ename,sal,lag(sal,2) over(partition by deptno order by sal desc) previous_value from emp
| DEPTNO | ENAME | SAL | PREVIOUS_VALUE |
| 10 | KING | 5000 | |
| 10 | CLARK | 2450 | |
| 10 | MILLER | 1300 | 5000 |
| 20 | SCOTT | 3000 | |
| 20 | FORD | 3000 | |
| 20 | JONES | 2975 | 3000 |
| 20 | SMITH | 2500 | 3000 |
| 20 | Lakshmi | 1500 | 2975 |
| 20 | ADAMS | 1100 | 2500 |
| 30 | BLAKE | 2850 | |
| 30 | ALLEN | 1600 | |
| 30 | TURNER | 1500 | 2850 |
| 30 | WARD | 1250 | 1600 |
| 30 | MARTIN | 1250 | 1500 |
| 30 | JAMES | 950 | 1250 |
In the above example we are using lag function with partition clause .Partition clause can use for the spiting into number of segments .
LEAD():
It is used to compare next row value with current row value. It is similar to LAG() major difference is lag function is used to compare previous row value but lead() is used compare next row value .
Syntax :
lead(column_name,[offset],[default_value]) over (partition by column_name order by column_name[asc/desc])
1)select ename,sal ,lead(sal) over(order by sal desc) next_row1 from emp ;
| ENAME | SAL | NEXT_ROW1 |
| KING | 5000 | 3000 |
| FORD | 3000 | 3000 |
| SCOTT | 3000 | 2975 |
| JONES | 2975 | 2850 |
| BLAKE | 2850 | 2500 |
| SMITH | 2500 | 2450 |
| CLARK | 2450 | 1600 |
| ALLEN | 1600 | 1500 |
| TURNER | 1500 | 1500 |
| lakshmi | 1500 | 1300 |
| MILLER | 1300 | 1250 |
| WARD | 1250 | 1250 |
| MARTIN | 1250 | 1100 |
| ADAMS | 1100 | 950 |
| JAMES | 950 |
In the above example we are leading the first next row value .That means we are getting null value in the first previous_row value why because there is no previous row value against james employee.Here we have used lead(sal) by default offset value will take one and default value is null that’s why we are getting the result from the first previous row value .
2) select ename,sal,lead(sal,2) over(order by sal desc) next_row2 from emp .
| ENAME | SAL | NEXT_ROW2 |
| KING | 5000 | 3000 |
| FORD | 3000 | 2975 |
| SCOTT | 3000 | 2850 |
| JONES | 2975 | 2500 |
| BLAKE | 2850 | 2450 |
| SMITH | 2500 | 1600 |
| CLARK | 2450 | 1500 |
| ALLEN | 1600 | 1500 |
| TURNER | 1500 | 1300 |
| lakshmi | 1500 | 1250 |
| MILLER | 1300 | 1250 |
| WARD | 1250 | 1100 |
| MARTIN | 1250 | 950 |
| ADAMS | 1100 | |
| JAMES | 950 |
In the above example we are leading the second next row value .That means we are getting null value in the last two rows why because there is no second next row values against admas and james employee.We are not getting NEXT_ROW2 value for adams and james employee why because adams and james employee not having then next two row value .In the above we are leading second next value
3)select deptno,ename,sal,lead(sal,2) over(partition by deptno order by sal desc) next_value_parition from emp
lead() with partition clause .
| DEPTNO | ENAME | SAL | NEXT_VALUE_PARITION |
| 10 | KING | 5000 | 1300 |
| 10 | CLARK | 2450 | |
| 10 | MILLER | 1300 | |
| 20 | SCOTT | 3000 | 2975 |
| 20 | FORD | 3000 | 2500 |
| 20 | JONES | 2975 | 1500 |
| 20 | SMITH | 2500 | 1100 |
| 20 | lakshmi | 1500 | |
| 20 | ADAMS | 1100 | |
| 30 | BLAKE | 2850 | 1500 |
| 30 | ALLEN | 1600 | 1250 |
| 30 | TURNER | 1500 | 1250 |
| 30 | WARD | 1250 | 950 |
| 30 | MARTIN | 1250 | |
| 30 | JAMES | 950 |
Loading...
