Oracle Database - SQL - Analytic Functions

Card Puncher Data Processing

Oracle Database - SQL - Analytic Functions

About

Analytic functions in the Oracle Database Context

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

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





Discover More
Data System Architecture
How to use the SQL ROW_NUMBER function?

ROW_NUMBER is an non-deterministic window function (analytic) that returns a sequence of unique numbers. With the row number, you can retrieve the following rows and create the follwing reports.: ...
Card Puncher Data Processing
Oracle Database - Analytics

The Oracle Advanced Analytics option of Oracle Database 11g Enterprise Edition includes: Oracle Data Mining and a new component called Oracle R Enterprise, which embeds R analytic capability in the...
Data System Architecture
What are the LAG and LEAD SQL functions ?

lag and lead are Sql window functions that provide access to a row at a given offset prior to/after the current row position.



Share this page:
Follow us:
Task Runner