Oracle Database - SQL - Analytic Functions

> Database > Oracle Database

1 - About

Analytic functions in the Oracle Database Context


3 - Syntax

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.

4 - Functions

List of analytic functions


5 - Analytics and Aggregate function

On the HR sample schema.

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

6 - Reference