ORACLE ANALYTICAL FUNCTIONS – 1

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.  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

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment