SQL Analytic Functions
What is anAnalytic Function
An Analytic function takes a group of rows as input and it compute an aggregate value as output.
Even though an Analytic function finds an aggregate value but still it is different from an aggregate function.
- Aggregate Function ( windowing functions)
- Analytic Function
Aggregate functions reduce the number of rows returned by the query in the output.But Analytic functions do not reduce the number of rows returned by the query in the output.
Analytical Functions and Aggregate Functions are similar but they are functionally different.
In the computation of Analytic function the number of rows in the output of a query is equivalent to the number of rows in the input.
Analytical functions are also known as windowing functions
Applications of SQL Analytic Functions
Analytic functions are widely used in data warehousing environment for the purpose of analysis. But its main aim is to efficiently utilize the power of relational databse platform for decision support processing.
Analytical function is an efficient replacement for complicated, poor performing native SQL code or Procedural language(PL/SQL) code which perform the same function.
We prefer Analytic Function over a piece similar native SQL code or Procedural Language code which perform the same function under the assumption that Analytic function perform better over the others.
Analytic Functions are highly optimized which results in improved query performance.
Analytic Functions comes with Standardized Syntax makes it very easy to use.
The analytic functions allow us to divide the output of a query into a group of rows called partitions.
Syntax of Analytic Functions
analytic_function_name ([ arguments ]) OVER (analytic_clause)
The analytic_clause is of the following format: [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
Windowing Clause : A windowing clause specifies the set of rows within a specific partition on which the analytic finction operate. The Windowing clause is optional, that is an Analytical function may or may not include a Windowing Clause.
The following Analytic functions include a window clause: AVG, COUNT, FIRST_VALUE, LAST_VALUE,MAX, MIN, SUM
Analytic Function Examples
SELECT empno, deptno, salary, AVG(salary) OVER (PARTITION BY deptno) AS avg_dept_salary FROM emp;
The number of output rows = The number of input rows. Here input has 7 rows, the output also contains 7 rows.It will output employee number, depatment number , Avereage salary of employees belongs to each department
EMPNO DEPTNO SAL AVG_DEPT_SAL
1 1 8000 10000
2 1 15000 10000
3 1 7000 10000
4 2 14000 12000
5 2 10000 12000
6 3 20000 15000
7 3 10000 15000
In a query Analytic functions are executed at the end just before the final ORDER BY clause. The analytic functions can appear only in the select list or ORDER BY clause because All jOINS and all WHERE , GROUP BY , and HAVING clauses are executed before the analytic functions.
Important Analytic Functions
The following analytic functions do not depend on the order of records :SUM,COUNT,AVG,MIN,MAX. These are aggregate functions. When these aggregate functions are used in a query as analytic functions they do not depend on the order of record.
But the following analytic functions depend on the order of records:LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST,FIRST VALUE, LAST, LAST VALUE