Analytic functions in Oracle

Analytic functions provide powerful ways to view your data.
And they make possible some elegant solutions to problems that previously were difficult to solve in SQL.


"Find the highest salary in each department, and give me a list of employees earning the same amount...
----------------------------------------------------------------------------------------------------

SELECT deptno, ename , sal
FROM (
SELECT deptno, ename ,sal,
MAX(sal) OVER (PARTITION BY deptno)maxsal
FROM emptest1 e
) WHERE sal = maxsal;


The outer query simply compares salary with maxsal, returning only those rows where the two amounts match.

The key to this solution is the analytic function call that computes the maximum salary for each employee's department.


Find the minimum salary in each department, and give me a list of employees earning the same amount...
----------------------------------------------------------------------------------------------------

SELECT deptno, ename , sal
FROM (
SELECT deptno, ename ,sal,
Min(sal) OVER (PARTITION BY deptno)minsal
FROM emptest1 e
) WHERE sal = minsal;

Find the average salary in each department, and give me a list of employees earning less than the average amount
----------------------------------------------------------------------------------------------------------------

SELECT deptno, ename , sal,avgsal
FROM (
SELECT deptno, ename ,sal,
avg(sal) OVER (PARTITION BY deptno)avgsal
FROM emptest1 e
) WHERE sal < avgsal;

Find the count of employees in each department,display count having morethan 5 employees
-----------------------------------------------------------------------------------------

SELECT deptno, ename , sal,countempno
FROM (
SELECT deptno, ename ,sal,
count(empno) OVER (PARTITION BY deptno)countempno
FROM emptest1 e
) WHERE countempno >5;

Find the highest paid salary in each job, and give me a list of employees earning the same amount...
----------------------------------------------------------------------------------------------------

SELECT deptno, ename , sal,job
FROM (
SELECT deptno, ename ,sal,job,
MAX(sal) OVER (PARTITION BY job)maxsal
FROM emptest1 e
) WHERE sal = maxsal;

Find the highest paid employess in each job and display maximum salary department wise.
--------------------------------------------------------------------------------------

SELECT deptno, ename , sal,job,maxsaldeptno
FROM (
SELECT deptno, ename ,sal,job,
MAX(sal) OVER (PARTITION BY job)maxsaljob,
MAX(sal) OVER (PARTITION BY deptno)maxsaldeptno
FROM emptest1 e
) WHERE sal = maxsaljob;

Diaplaying the employees deptno wise using RANK()
-----------------------------------------------
SELECT deptno, job, ename ,
       sal, dept_rank
FROM (
SELECT deptno, job, ename , sal,
       RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC
       ) dept_rank
  FROM emptest1
) WHERE dept_rank <= 5
  ORDER BY deptno, dept_rank DESC;

---> Rows are grouped by department (PARTITION BY deptno).
---> Within each group, rows are sorted in descending order by salary (ORDER BY sal DESC).
---> Each row in each group is given a number to indicate its rank in the group.

The ORDER BY clause is mandatory for use of any of the analytic ranking functions,
because you use it to specify the basis for ranking.

Keep in mind that the ORDER BY for ranking the employees by salary is not the same as the final ORDER BY
that ranks the entire result set

Using DENSE_RANK instead of RANK
-------------------------------
SELECT deptno, job, ename ,
       sal, dept_rank
FROM (
SELECT deptno, job, ename , sal,
       dense_RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC
       ) dept_rank
  FROM emptest1
)  ORDER BY deptno, dept_rank ;

Using LAG and LEAD
-----------------
These functions let you look at the preceding and following rows on column.

SELECT deptno, job, ename ,
       sal,LEAD(sal) OVER(
                          PARTITION BY deptno ORDER BY hiredate)leadsal,
           LAG(sal) OVER(
                          PARTITION BY deptno ORDER BY hiredate)lagsal,
                          dept_rank,
       CASE WHEN sal < LEAD(sal) OVER(
                          PARTITION BY deptno ORDER BY hiredate)
            THEN 'No'
            ELSE 'Yes'
       END lead_salary,
       CASE WHEN sal < lag(sal) OVER(
                          PARTITION BY deptno ORDER BY hiredate)
            THEN 'No'
            ELSE 'Yes'
       END lag_salary
FROM (
SELECT deptno, job, ename , sal,hiredate,
       dense_RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC
       ) dept_rank
  FROM emptest1
)  ORDER BY deptno ;

For the LAG and LEAD functions to work, you must impose order on the rows you are working with. Otherwise there's
 no sense of "preceding" or "following" among the results. Thus, as with RANK and DENSE_RANK,
the ORDER BY clause is mandatory for the LAG and LEAD functions

Using Range between preceding and following (Moving Average Salary)
-------------------------------------------------------------------

SELECT deptno, job, ename, sal,hiredate,moving_averagesal
FROM (
SELECT deptno, job, ename , sal,hiredate ,
round(avg(sal) over (order by hiredate range between hiredate-add_months(hiredate,-1)preceding
  and add_months(hiredate,1)-hiredate following ))moving_averagesal
  FROM emptest1
)  ;

The RANGE BETWEEN clause defines a moving window that is computed a new for each row the query returns.
The ORDER BY hire_date clause specifies that hire_date is to be the basis for the moving window.


Ur's
AmarAlam