Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

Analytic functions differ from aggregate functions in the sense that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row.

Oracle provides many Analytic Functions such as

AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

The Syntax of analytic functions:

Analytic-Function(Column1,Column2,...)
OVER (
[Query-Partition-Clause]
[Order-By-Clause]
[Windowing-Clause]
)

Analytic functions take 0 to 3 arguments.

An Example:

SELECT ename, deptno, sal, 
  SUM( sal ) 
    OVER ( ORDER BY deptno, ename ) AS Running_Total, 
  SUM( sal ) 
    OVER ( PARTITION BY  deptno 
           ORDER BY  ename) AS Dept_Total, 
  ROW_NUMBER() 
    OVER ( PARTITION BY deptno 
          ORDER BY ename ) As Sequence_No 
FROM emp 
ORDER BY deptno, ename;

Oracle analytical function example 1

The partition clause makes the SUM(sal) be computed within each department, independent of the other groups. The SUM(sal) is 'reset' as the department changes. The ORDER BY ENAME clause sorts the data within each department by ENAME;

  1. Query-Partition-Clause

    The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The analytic functions are applied to each group independently, they are reset for each group.

  2. Order-By-Clause

    The ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the output of the analytic function.

  3. Windowing-Clause

    The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group. The default window is an anchored window that simply starts at the first row of a group an continues to the current row.

Let's look an example with a sliding window within a group and compute the sum of the current row's salary column plus the previous 2 rows in that group. i.e ROW Window clause:

SELECT deptno, ename, sal, 
  SUM( sal )  
     OVER  (  PARTITION BY  deptno 
          ORDER BY ename 
           ROWS 2 PRECEDING  ) AS Sliding_Total  
FROM emp 
ORDER BY deptno, ename;  

Oracle analytical function example 2

Now if we look at the Sliding Total value of SMITH it is simply SMITH's salary plus the salary of two preceding rows in the window. [ 800+3000+2975 = 6775 ]

We can set up windows based on two criteria: RANGES of data values or ROWS offset from the current row. It can be said, that the existence of an ORDER BY in an analytic function will add a default window clause of RANGE UNBOUNDED PRECEDING. That says to get all rows in our partition that came before us as specified by the ORDER BY clause.

** Solving Top-N Queries **

Suppose we want to find out the top 3 salaried employee of each department:

SELECT deptno, ename, sal, ROW_NUMBER() 
  OVER ( 
    PARTITION BY deptno ORDER BY sal DESC 
  ) Rnk FROM emp;  

This will give us the employee name and salary with ranks based on descending order of salary for each department or the partition/group . Now to get the top 3 highest paid employees for each dept.

 SELECT * FROM (  
  SELECT deptno, ename, sal, ROW_NUMBER() 
  OVER ( 
    PARTITION BY deptno ORDER BY sal DESC 
  ) Rnk FROM emp 
 ) WHERE Rnk <= 3; 

The use of a WHERE clause is to get just the first three rows in each partition.

Oracle analytical function example 3

** Solving the problem with DENSE_RANK **

If we look carefully the above output we will observe that the salary of SCOTT and FORD of dept 10 are same. So we are indeed missing the 3rd highest salaried employee of dept 20. Here we will use DENSE_RANK function to compute the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The DENSE_RANK function does not skip numbers and will assign the same number to those rows with the same value.

The above query now modified as:

SELECT * FROM ( 
  SELECT deptno, ename, sal,  DENSE_RANK()  
  OVER ( 
    PARTITION BY deptno ORDER BY sal DESC 
  ) Rnk FROM emp 
) 
WHERE Rnk ≤ 3 

and the output is as follows:

Oracle analytical function example 4


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • How to find out Expected Time of Completion for an Oracle Query

    Too often we become impatient when Oracle Query executed by us does not seem to return any result. But Oracle (10g onwards) gives us an option to check how long a query will run, that is, to find out expected time of completion for a query.

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • Oracle Spool

    Oracle Spool for SQLplus command line utility, can be used in conjunction with OS to export data from Oracle to flat file.

  • Oracle UTL_FILE

    The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

  • Oracle External Tables

    The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. This is a very convenient and fast method to retrieve data from flat files outside Oracle database.

  • Understanding Oracle QUERY PLAN - Part2 (Exploring SQL Joins)

    This is the second part of the article Understanding Oracle Query Plan. In this part we will deal with SQL Joins.

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.