Oracle Database - SQL - Analytic Functions

Analytic functions in the Oracle Database Context

• Oracle Database - SQL - First Value Analytic function

Query processing using analytic functions takes place in three stages :

1. all joins, WHERE, GROUP BY and HAVING clauses are performed.
2. the result set is made available to the analytic functions, and all their calculations take place.
3. if the query has an ORDER BY clause at its end, the ORDER BY is processed to allow for precise output ordering.

FUNCTION() over (partition BY mycolumns ORDER BY mycolumns)

An analytic function takes place after that the original data set is retrieved.

The clause <partition by> defines how the function is calculated (aggregated for an aggregate function). For a sum for instance, the following statement:

SUM(MyColumn) over (partition BY Year)

will do a sum by year (partition = group by).

If the PARTITION BY clause is absent, then the function is computed over the whole query result set.

List of analytic functions

• AVG
• CORR
• COVAR_POP
• COVAR_SAMP
• COUNT
• CUME_DIST
• DENSE_RANK
• FIRST
• FIRST_VALUE
• LAG/LEAD : Oracle Database - SQL - Analytic Function LAG/LEAD
• LAST
• LAST_VALUE
• MAX/MIN
• NTILE
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC
• RANK
• RATIO_TO_REPORT
• REGR_ (Linear Regression) Functions
• ROW_NUMBER
• STDDEV
• STDDEV_POP
• STDDEV_SAMP
• SUM : Analytic Function Sum
• VAR_POP
• VAR_SAMP
• VARIANCE

On the HR sample schema.

SELECT j.job_title, COUNT(*),
 MAX(salary) maxsalary,
 MIN(salary) minsalary,
 RANK() OVER(ORDER BY MAX(salary)) rankorder
FROM employees e, jobs j 
 WHERE 
  e.job_id = j.job_id
 GROUP BY j.job_title
 ORDER BY rankorder;

• Performance Improvement in Peoplesoft Using Oracle Analytic Functions
• Sql Oracle Reference
• SQL for Analysis and Reporting - Oracle Database Data Warehousing Guide