Oracle SQL Analytical Functions For Beginners
Analytical Function:- Analytical functions provide simple solution to complex querying problem. When we want grouping data with non-grouping column then we use analytical functions. When we want values comparison of a column then only analytical is the way to do the same.Categories of Analytical Function:-
1) Ranking Analytical Function:-
a) row_number() b) rank() c) dense_rank() d) percent_rank() e) cume_dist() f) ntile()
2) Aggregate Analytical Function:-
a) sum() b) avg() c) min() d) max() e) count() f) stddev()
3) Row Comparison Analytical Function:-
a) lag() b) lead()
4) Statistical Analytical Function:-
a) ratio_to_report
General Syntax:-
function(
Type of Analytic clause:-
a) Ordering :-In this clause we use "order by column_name"
b) Partitioning :-In this clause we use "partition by column_name"
c) Windowing :-In this clause we use "rows between x preceding and y following" ,we can use "unbounded" and "current row" clause in place of a and y as well.
Note:-we can use combination of ordering, partitioning and windowing.
1) Ranking Function:-
row_number() vs rownum :- rownum is a pseudo-column, calculating before ordering whereas row_number is a analytical function calculating analytical clause;
row_number() vs rank() vs dense_rank() :- row_number() analytical function generatr numbering on analytical clause but not handling row tie,rank() and dense_rank() generating number with handling problem of row tie in different ways,rank() skip the next rank. if row tie happen,but dense_rank() does not skip the next rank whereas give same rank of tie records.
Examples:- 1)
select empno,ename,job,sal,row_number() over(order by sal desc) row_num,
rank() over(order by sal desc) rank_num,
dense_rank() over(order by sal desc) drank_num
from emp;
Nulls and Analytical Function:-For treating nulls values we used NULLS FIRST and NULLS LAST clause in analytical clause and default value is NULLS FIRST.
Examples:-1)
select empno,ename,job,sal,rank() over(order by sal desc nulls first) rank_num
from emp2 ;
2)
select empno,ename,job,sal,rank() over(order by sal desc nulls last) rank_num
from emp2 ;
Note:- We can also use nvl function for trating nulls values
Examples:-1)
select empno,ename,job,nvl(sal,6000),rank() over(order by nvl(sal,6000) desc ) rank_num
from emp2
order by sal desc nulls last;
2)
select empno,ename,job,nvl(sal,6000),rank() over(order by nvl(sal,6000) desc ) rank_num
from emp2
order by sal desc
y) Partitioning :-partitioning in analytical function allow us to separate grouping of data and then perform a function from within that group.
Examples 1:- ranking according to salary and partition of deptno wise
select empno,ename,job,deptno,sal,rank() over(partition by deptno order by sal desc ) rank_num
from emp;
2) ranking accoring to their salary and partition of job wise
select empno,ename,job,deptno,sal,rank() over(partition by job order by sal desc ) rank_num
from emp;
Ntile():-ntile() roughly works by dividing the number of rows retrieved into the choosen number of segments then the percentile is displayed as the segment
that the rows fall into.
Example:-
select empno,ename,job,deptno,sal,ntile(4) over(order by sal desc ) nt
from emp;
If you wanted to know which salaries where in the top 25%, then next 25%, the next 25% and the bottom 25% then ntile(4) function is used.
Calculation of NTILE:-if you use ntile(4) and max salary is 55000 and min salary is 39000 then
55000-39000=16000
16000/4=4000
55000 to 55000-4000=51000 is in the top 25%
51000 to 51000-4000=47000 is in the 2nd 25%
47000 to 47000-4000=43000 is in the 3rd 25%
and 43000 to 43000-4000=39000 is in the bottom 25%
Percent_rank():- Formula for calculation of percent_rank() is :-
percent_rank()=(rank-1)/(number of rows-1)
example:-select empno,ename,job,deptno,sal,rank() over(order by sal desc) rank,
percent_rank() over(order by sal desc ) p_rank
from emp;
CUME_DIST():-formula for cumulative_distribution is :-
cume_dist()=heighest rank of row/number of rows
example:-select empno,ename,job,deptno,sal,rank() over(order by sal desc) rank,
percent_rank() over(order by sal desc ) p_rank,
cume_dist() over(order by sal desc) cume_rank
from emp;
2) Aggregate functions:- Many of the aggregate function can be used as a analytical function like SUM,AVG,COUNT,STDDEV,VARIANCE,MAX and MIN.Aggregate functions used as analytical functions offer the advantage of partitioning and ordering as well;
Examples:-1) select empno,ename,job,deptno,sal,round(avg(sal) over(),2) avg_sal,
sum(sal) over() sum_sal
from emp;
2) select empno,ename,job,deptno,sal,round(avg(sal) over(partition by deptno),2) dept_avg_sal,
sum(sal) over(partition by deptno) dept_sum_sal
from emp;
3)select empno,ename,job,deptno,sal,round(avg(sal) over(partition by deptno),2) dept_avg_sal,
sum(sal) over(partition by deptno) dept_sum_sal,
max(sal) over(partition by deptno) dept_max_sal,
min(sal) over(partition by deptno) dept_min_sal
from emp;
Ratio_to_report() :- It is used to know fraction value of records in the group
formula for ratio_to_report:-
ratio_to_report()=current_value/sum of values
Example:-select empno,ename,job,deptno,sal,rank() over(order by sal desc) rank,
ratio_to_report(sal) over() fraction_sal
from emp;
Windowing Clause:- syntax of windowing clause
function(attribute1) over(order by attribute2 rows between x preceding and y following)
Example 1:- select empno,ename,job,deptno,sal,
sum(sal) over(order by sal desc rows between 1 preceding and 1 following) window_sum
from emp;
2) For running total of salary
select empno,ename,job,deptno,sal,sum(sal) over(order by sal desc rows between unbounded preceding and current row) running_total
from emp;
Lag() and Lead() :-lag() is for previous value and lead() is for next value.
Example 1:- select empno,ename,job,deptno,sal,lag(sal) over(order by sal desc) previous,
lead(sal) over(order by sal desc) next
from emp;
2) select empno,ename,job,deptno,sal,lag(sal) over(partition by deptno order by sal desc) previous,
lead(sal) over(partition by deptno order by sal desc) next
from emp;
0 comments:
Post a Comment