ORACLE ANALYTICAL FUNCTIONS (WINDOWING FUNCTIONS)

Introduction :

Analytic functions(SUM() OVER, RANK,ROW_NUMBER etc) will be executed in a SQL query after all JOINS,WHERE,GROUP BY and HAVING clause except ORDER BY. These functions are the last set of operations performed on group of rows or window of rows. And most important point is that these wont need GROUP BY clause unlike normal aggregate functions.

• Analytical functions are also known as windowing functions.
• Analytical functions compute values based on group of rows and display value for each row.  
• Analytic functions are used to compute cumulative, moving, centered, and reporting aggregates.
  
  

  	Analytical Functions	Aggregate Functions
  Definition	Analytical functions compute aggregate value based on group of rows and return multiple rows for each group.	Aggregate Functions return single value for a set of rows.
  Syntax	Analytical_function([arguments]) over ({query partition clause}) [order by clause] ([windowing clause] )	Aggregate_function(column)
  Example	SELECT DEPT_ID,EMPNO,SAL,MAX(SAL) OVER () AS MAX_SALARY FROM EMPLOYEES; SELECT DEPT_ID,EMPNO,SAL,MAX(SAL) OVER (PARTITION BY DEPT_ID) AS MAX_SALARY FROM EMPLOYEES;	SELECT MAX(SAL) FROM EMPLOYEES; SELECT DEPT_ID,EMPNO,SAL,MAX(SAL)  AS MAX_SALARY FROM EMPLOYEES GROUP BY DEPT_ID,EMPNO,SAL;

  
  
  

  Analytical Function Syntax :

  1.       Arguments: Analytical Functions can take 0 to 3 arguments.

  2.       Over and Partition Clause:  These clauses are used to indicate that the list of columns on which the query gets computed. If we do not specify the partition clause then the query treats all the rows as single set and calculates average for all the employees irrespective of departments.

  3.       Example: Query to calculate average salary for department and display against each employee.                                                                                                                                                       SELECT EMPLOYEE, DEPT_ID, AVG (SAL) OVER PARTITION BY AS AVG_DEPT FROM EMPLOYEES.

  4.       Windowing Clause:  Some analytical functions allow window function. Ex: max, min etc.

  
  
  

  
  

  Analytical functions examples:

  -- Query to display department wise max salary using analytical function
  select empno,deptno,max(sal) over (partition by deptno order by empno) max_sal from emp ;
  

  

  
  Aggregate function examples:

  
  

  

      From the above 2 examples we can infer that 

• Analytical functions does not reduce the row count and will display the values against all the rows.
• Aggregate functions reduce the row count.

      Similarly we can  use the other Analytical functions.

      For more information please visit the oracle website : http://docs.oracle.com/cloud/latest/db112/SQLRF/functions004.htm#SQLRF06174