Oracle Database - SQL - Analytic Functions

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

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap