Oracle Database - SQL - Analytic Functions

About

Analytic functions in the Oracle Database Context

Articles Related

Oracle Database - SQL - First Value Analytic function

Syntax

function() over (partition by mycolumns order by mycolumns)

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

The clause

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.

Functions

List of analytic functions

AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG/LEAD : What are the LAG and LEAD SQL functions ?
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 : Number - Addition (or Sum, Sigma) or Total
VAR_POP
VAR_SAMP
VARIANCE

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 
 WHERE 
  e.job_id = j.job_id
 GROUP BY j.job_title
 ORDER BY rankorder;

Reference