SQL Analytic Functions

SQL Analytic Functions

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

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