Analytical Functions

The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions the result of which does not depend on the order of the records.
Analytical functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.

Analytical functions :-
----------------------
Analytic functions Return the same number of rows as the input. Analytic functions give aggregate result they do not group the result set.
In order to arrange employees in a particular order like ascending or descending order with the help of analytical functional like rank, dense_rank

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6



Types of Analytical Functions
- Rank
- Dense_rank
- Row_number
- Count
- Lag
- Lead
- First_value
- Last_value
- First
- Last


RANK function :-
It is used to rank a record within a group of rows
RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped)

SELECT EMPNO, ENAME, JOB, RANK() OVER (ORDER BY JOB) AS RANK FROM EMP;

(OR) 

 SELECT E.*, RANK() OVER (ORDER BY JOB DESC) AS RANK FROM EMP E;


DENSE_RANK function :-
Dense_rank function acts like the RANK function except that it assigns consecutive ranks.
DENSE_RANK() will return {1,1,2,3,3,4}

SELECT EMPNO, ENAME, JOB, DENSE_RANK() OVER (ORDER BY JOB) AS RANK FROM EMP;

(OR) 

SELECT E.*, DENSE_RANK() OVER (ORDER BY JOB DESC) AS DRANK FROM EMP E;

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+



ROWNUM function :-
Rownum is temporary, It is the sequential number. 

SELECT ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY JOB) AS ROWNUMBER,
EMPNO, ENAME, JOB FROM EMP;

(OR)

SELECT E.*, ROW_NUMBER() OVER (ORDER BY JOB DESC) AS RN FROM EMP E;


ROWID function :-
Rowid is permanent, It is physical address of the rows or permanent unique identifier.

SELECT EMPNO, ENAME, JOB, ROWID FROM EMP;


LEAD function :-
It is used to display Subsequent row data along with current row data
For example, by using the LEAD() function, from the current row, you can access data of the next row, or the second row that follows the current row, or the third row that follows the current row, and so on.

Syntax :
LEAD ( scalar_expression [ ,offset ] , [ default ] )   
OVER ( [ partition_by_clause ] order_by_clause )

SELECT EMPNO, ENAME, JOB, SAL, LEAD(SAL) OVER(PARTITION BY JOB ORDER BY SAL) AS HIGHEST_SAL FROM EMP;

(OR)

SELECT E.*, LEAD(SAL) OVER (ORDER BY SAL DESC) AS LEAD
FROM EMP E;


LAG function :-
It is used to display previous row data along with current row data. Here 1 indicated beside lag column skips 1 row and by default its 1 there is no use of writing it.
2 is indicated to skip two rows in the result.

Syntax :
LAG ( scalar_expression [ ,offset ] , [ default ] )   
OVER ( [ partition_by_clause ] order_by_clause )

SELECT EMPNO, ENAME, SAL, LAG(SAL) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;
(OR)
SELECT EMPNO, ENAME, SAL, LAG(SAL,1) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;


SELECT EMPNO, ENAME, SAL, LAG(SAL,1,0) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;

(OR)

SELECT E.*, LAG(SAL) OVER (ORDER BY SAL DESC) AS LAG
FROM EMP E;






FIRST_VALUE function :-
Its used to display first value from the specified column based on the order by clause.

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(SAL) OVER (ORDER BY SAL) AS LOWEST_SAL FROM EMP;

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(SAL) OVER (PARTITION BY JOB ORDER BY SAL) AS LOWEST_SAL FROM EMP;



LAST_VALUE function :-
Its used to display last value from the specified column based on the order by clause.

SELECT EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (ORDER BY SAL) AS LOWEST_SAL FROM EMP;
As a result of this syntax it does not return expected results but it returns same rows.

SELECT EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_SAL FROM EMP;
This syntax gives you correct results.


PARTITION BY clause :-
This clause is used only with "Over" clause. It is used to breakup or divide table data into many partitions, we can apply aggregate functions and analytical functions.


SELECT DEPTNO, ENAME, SAL, AVG(SAL) OVER (PARTITION BY DEPTNO) AS AVG_SAL FROM EMP;