ANALYTICAL FUNCTION

ANALYTICAL FUNCTION 

RANK()
DENSE RANK()
ROW_NUMBER()
LEAD()
LAG()
ROLLUP()
CUBE()
FIRST()
LAST()
FIRST_VALUE()

SQL> CREATE Table t1(Id Number, Name Varchar2(10), Salary Number);

Table created.

SQL> INSERT into t1 values(1,'Parthi',24000);
SQL> INSERT into t1 values(2,'Karthi',20000);
SQL> INSERT into t1 values(3,'Arthi',20000);
SQL> INSERT into t1 values(4,'Surya',9000);
SQL> INSERT into t1 values(5,'Taj',8000);
SQL> INSERT into t1 values(6,'Priya',19000);
SQL> SELECT * FROM t1;

        ID NAME           SALARY                                                
---------- ---------- ----------                                                
         1 Parthi          24000                                                
         2 Karthi          20000                                                
         3 Arthi           20000                                                
         4 Surya            9000                                                
         5 Taj              8000                                                
         6 Priya           19000                                                

In the ANALYTICAL Function there are two keywords used.

OVER (ORDER BY COLUMN)
OVER (PARTITION BY COLUMN ORDER BY COLUMN)  

RANK() :  Equal rows are ranked the same rank.Next rank will be empty.    
 
SQL> SELECT Id, Name, Salary, RANK() over (order by salary desc) "RANK" from t1;

        ID NAME           SALARY RANK               
---------- ---------- ---------- ------------                  
         1 Parthi          24000        1                  
         2 Karthi          20000        2                  
         3 Arthi           20000        2                  
         6 Priya           19000        4                  
         4 Surya            9000        5                  
         5 Taj              8000        6                  

6 rows selected.

N th MAXIMUM Salary using RANK()

SELECT First_Name,Salary FROM 
( SELECT First_Name,Salary, RANK() OVER (ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 5;

N th MAXIMUM Salary department wise by using RANK() 

SELECT First_Name,department_id,Salary FROM 
( SELECT First_Name,Salary,department_id, RANK() OVER (partition by department_id ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 2;

DENSE_RANK() : Equal rows are ranked the same rank.Next rank will be named as the next position

SQL> SELECT Id, Name, Salary,DENSE_RANK() over (order by salary desc) from t1;

        ID NAME           SALARY DENSE_RANK()OVER(ORDERBYSALARYDESC)            
---------- ---------- ---------- -----------------------------------            
         1 Parthi          24000                                   1            
         2 Karthi          20000                                   2            
         3 Arthi           20000                                   2            
         6 Priya           19000                                   3            
         4 Surya            9000                                   4            
         5 Taj              8000                                   5            

6 rows selected.

Nth MAXIMUM Salary by Using DENSE_RANK 

SELECT First_Name,Salary FROM 
( SELECT First_Name,Salary,DENSE_RANK() OVER (ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 5;

N th MAXIMUM Salary department wise by using RANK() 

SELECT First_Name,department_id,Salary FROM 
( SELECT First_Name,Salary,department_id, DENSE_RANK() OVER (partition by department_id ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 2;


ROWNUM 

SQL> SELECT Id, Name, Salary,ROW_NUMBER() over (order by salary desc) from t1;

        ID NAME           SALARY ROW_NUMBER()OVER(ORDERBYSALARYDESC)            
---------- ---------- ---------- -----------------------------------            
         1 Parthi          24000                                   1            
         2 Karthi          20000                                   2            
         3 Arthi           20000                                   3            
         6 Priya           19000                                   4            
         4 Surya            9000                                   5            
         5 Taj              8000                                   6            

6 rows selected.

N th MAXIMUM Salary By Using ROWNUM

SELECT * FROM
(
SELECT Rownum rn, Salary FROM
(
SELECT DISTINCT Salary FROM Employees Order By Salary DESC)
)
WHERE rn=&n;

SELECT MIN(Salary) FROM 
(SELECT ROWNUM Rn, Salary FROM
(
SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC)
)
WHERE ROWNUM<=5; 

SELECT First_name, department_id, salary from 
(
 SELECT First_name, department_id, salary, row_number() OVER (ORDER BY salary DESC) as row_num from Employees e 
 )
 where row_num = 2;


N th MAXIMUM Salary of department wise By Using ROWNUM 

SELECT First_name, department_id, salary from 
(
 SELECT First_name, department_id, salary, row_number() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num from Employees e 
 )
 where row_num = 2;


LEAD 

SQL> SELECT Id, Name, Salary,LEAD(Salary,1,0) over (order by salary desc) from t1;

SQL> SELECT Id, Name, Salary,LEAD(Salary,2,100) over (order by salary desc) from t1;

LAG 

SQL> SELECT Id, Name, Salary,LAG(Salary,2,100) over (order by salary desc) from t1;

6 rows selected.

SQL> SELECT Id, Name, Salary,LAG(Salary,1,0) over (order by salary desc) from t1;
6 rows selected.

FIRST_VALUE

SELECT employee_id, department_id, hire_date , FIRST_VALUE(hire_date)
OVER (PARTITION BY department_id ORDER BY hire_date) DAY_GAP, hire_date- (FIRST_VALUE(hire_date)
OVER ( partition by department_id ORDER BY hire_date)"Day Count"
FROM employees
WHERE department_id IN (20, 30)
ORDER BY department_id, DAY_GAP;


EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE DAY_GAP    Day Count
----------- ------------- --------- --------- ----------
        201            20 17-FEB-04 17-FEB-04          0
        202            20 17-AUG-05 17-FEB-04        547
        117            30 24-JUL-05 07-DEC-02        960
        116            30 24-DEC-05 07-DEC-02       1113
        118            30 15-NOV-06 07-DEC-02       1439
        119            30 10-AUG-07 07-DEC-02       1707
        114            30 07-DEC-02 07-DEC-02          0
        115            30 18-MAY-03 07-DEC-02        162

FIRST AND LAST

SELECT department_id,
       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;
  
DEPARTMENT_ID      Worst       Best
------------- ---------- ----------
           10       4400       4400
           20       6000      13000
           30       2500      11000
           40       6500       6500
           50       2100       8200
           60       4200       9000
           70      10000      10000
           80       6100      14000
           90      17000      24000
          100       6900      12008
          110       8300      12008
                    7000       7000
  
LISTAGG() 

    DEPT_ID DNAME
---------- ----------
        10 cse
        10 ece
        20 eee
        30 mech
        10 mech
        20 it
        30 it 

SELECT Dept_Id, LISTAGG(DName,',')within group (order by dname)"List" FROM Listagg1 group by dept_id;

   DEPT_ID DNAME
---------- ----------
        10 cse,ece,mech
        20 eee,it
        30 mech,it 

ROLLUP

SQL> SELECT * FROM t3;

    DEP_ID        SID     SALARY
---------- ---------- ----------
         1          1       4000
         1          2       6000
         1          3       6000
         1          4       6500
         1          5       7500
         2          1       7500
         2          2       6500
         2          3       8500
         2          4       9500
         2          5       9000

10 rows selected.

SELECT dep_id,sid,SUM(salary) 
FROM t3 GROUP BY ROLLUP(dep_id,sid) ORDER BY dep_id,sid;

    DEP_ID        SID SUM(SALARY)
---------- ---------- -----------
         1          1        4000
         1          2        6000
         1          3        6000
         1          4        6500
         1          5        7500
         1                  30000
         2          1        7500
         2          2        6500
         2          3        8500
         2          4        9500
         2          5        9000
         2                  41000
                            71000

CUBE

SQL> Select dep_id,sid,sum(salary) from t3 group by CUBE(dep_id,sid) order by dep_id,sid;

    DEP_ID        SID SUM(SALARY)
---------- ---------- -----------
         1          1        4000
         1          2        6000
         1          3        6000
         1          4        6500
         1          5        7500
         1                  30000
         2          1        7500
         2          2        6500
         2          3        8500
         2          4        9500
         2          5        9000
         2                  41000
                    1       11500
                    2       12500
                    3       14500
                    4       16000
                    5       16500

    DEP_ID        SID SUM(SALARY)
---------- ---------- -----------
                            71000