Analytical functions, also known as Windowing functions, are introduced in Oracle 8i. Analytical functions are designed to address problems in real life such as:
- Getting top ‘N’ results
- Find percentage within a group
- Calculate running total
- Compute moving averages
Analytic Functions Syntax and Features
AnalyticFunction(arguments)
OVER( PARTITION BY clause
ORDER BY clause
Windowing clause
)
- A function is identified as analytic through the
OVERclause. - Analytic functions can appear only in the
SELECTlist orORDER BYclause. - You can apply
OVERclause to aggregate functions like SUM, AVG, MAX, MIN; however the difference from ordinary aggregate functions to analytic functions is, aggregate functions return multiple rows for each group. But analytic function returns for each row. - Analytic function takes 0 to 3 arguments.
- The output of analytic function can be affected by
ORDER BYclause to the end of the query. Since, Analytic functions work by taking theORDER BYclause in theOVERclause, it’s not recommended to useORDER BYclause at the last of the query. - The group of rows defined by the
PARTITION BYinOVERclause is called as Window. This is why Analytic functions are also known as Windowing functions. - The Window defines the range of rows used to perform the calculation for current row.
- Window sizes can be based upon either a physical number of rows or a logical interval such as time.
How Analytic functions work?
- Analytic functions are the last set of operations performed in a query, except for the final ORDER BY clause.
- All JOINs, WHERE clause, GROUP BY and HAVING clauses are completed before the Analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
- The
OVERclause indicates the function is analytic. - The
PARTITION BYclause logically breaks a single result set into “N” groups. Analytic function is applied for each group independently, and they are reset for each group defined by thePARTITION BYclause. - When you do not provide
PARTITION BYto theOVERclause, entire result set is considered as a single group. - Analytic functions computes or aggregates based on the identified group but return for each row.
- The
ORDER BYclause specifies how data has to be processed within each group (partition). For example, when finding first value, last value for a partition, the order of rows becomes crucial. - The Windowing clause gives a definition for how many number of rows are to be considered by the Analytic function while it is operating.
Working with Analytical Functions in Oracle SQL
Analytic functions provides developers flexibility to perform more tasks in SQL. But before we start diving deep with analytical functions in Oracle, let us first quickly run through some examples with GROUP BY and aggregate functions.
Following example shows GROUP BY acting as DISTINCT without any aggregate functions.
SELECT Deptno, Sal
FROM emp
GROUP BY Deptno, Sal;
DEPTNO SAL
---------- ----------
10 5000
20 2975
20 800
10 2450
20 3000
30 1250
30 950
20 1100
10 1300
30 2850
30 1600
30 1500
12 rows selected.
Following example GROUP BY with aggregate functions returns group wise data.
SELECT Deptno, SUM(Sal) InvPerDept
FROM emp
GROUP BY deptno;
DEPTNO INVPERDEPT
---------- ----------
30 9400
20 10875
10 8750
Following example aggregate functions without GROUP BY returns one row.
SELECT SUM(Sal) InvTotal
FROM emp;
INVTOTAL
----------
29025
In all above cases, the aggregate function or GROUP BY clause reduces the number of rows returned by the query. Analytic Functions operate similar to aggregate functions except that aggregate functions squash the output to one row per group whereas Analytic functions return one value per row.
Following sql query makes SUM function as analytic by adding OVER clause to it and returns value for each row. When no arguments are supplied to OVER clause, the entire result set acts as a single group for analytical processing.
SELECT Ename, Deptno, Sal, SUM(Sal) OVER() InvOverAll FROM emp; ENAME DEPTNO SAL INVOVERALL ---------- ---------- ---------- ---------- KING 10 5000 29025 BLAKE 30 2850 29025 CLARK 10 2450 29025 JONES 20 2975 29025 SCOTT 20 3000 29025 FORD 20 3000 29025 SMITH 20 800 29025 ALLEN 30 1600 29025 WARD 30 1250 29025 MARTIN 30 1250 29025 TURNER 30 1500 29025 ADAMS 20 1100 29025 JAMES 30 950 29025 MILLER 10 1300 29025 14 rows selected.
The OVER clause identifies declared aggregate function call as an analytic function (as opposed to an aggregate function). Let us take one more example by supplying PARTITION BY to OVER clause.
Following example SUM function operates for each group specified in PARTITION BY clause.
SELECT Ename, Deptno, Sal, SUM(Sal) OVER(PARTITION BY Deptno) InvPerDept FROM emp; ENAME DEPTNO SAL INVPERDEPT ---------- ---------- ---------- ---------- CLARK 10 2450 8750 MILLER 10 1300 8750 KING 10 5000 8750 FORD 20 3000 10875 SCOTT 20 3000 10875 JONES 20 2975 10875 SMITH 20 800 10875 ADAMS 20 1100 10875 WARD 30 1250 9400 MARTIN 30 1250 9400 TURNER 30 1500 9400 JAMES 30 950 9400 ALLEN 30 1600 9400 BLAKE 30 2850 9400 14 rows selected.
Flexibility with Analytic Functions
Analytic functions add greater performance to the standard query processing and helps reduce code size for many scenarios.
Let us take an example and understand the capability of Analytical functions.
Q : Get the total investment for employees in Emp table?
Ans:
SELECT SUM(Sal) TotalSal FROM Emp;
TOTALSAL
----------
29025
Q : Get the employee name, designation, salary and dept number for all employees in Emp table?
Ans:
SELECT Ename, Job, Deptno, Sal FROM Emp; ENAME JOB DEPTNO SAL ---------- --------- ---------- ---------- KING PRESIDENT 10 5000 BLAKE MANAGER 30 2850 CLARK MANAGER 10 2450 JONES MANAGER 20 2975 SCOTT ANALYST 20 3000 FORD ANALYST 20 3000 SMITH CLERK 20 800 ALLEN SALESMAN 30 1600 WARD SALESMAN 30 1250 MARTIN SALESMAN 30 1250 TURNER SALESMAN 30 1500 ADAMS CLERK 20 1100 JAMES CLERK 30 950 MILLER CLERK 10 1300 14 rows selected.
Our next question tries to combine both of above output and let us see how many approaches you can solve that.
Q : Get the employee name, designation, salary, dept number and total investment made for all employees in Emp table?
Ans:
Approach 1: Using Joins
SELECT Ename, Job, Deptno, Sal, TotalSal FROM Emp e, (SELECT SUM(Sal) TotalSal FROM Emp) ts; ENAME JOB DEPTNO SAL TOTALSAL ---------- --------- ---------- ---------- ---------- KING PRESIDENT 10 5000 29025 BLAKE MANAGER 30 2850 29025 CLARK MANAGER 10 2450 29025 JONES MANAGER 20 2975 29025 SCOTT ANALYST 20 3000 29025 FORD ANALYST 20 3000 29025 SMITH CLERK 20 800 29025 ALLEN SALESMAN 30 1600 29025 WARD SALESMAN 30 1250 29025 MARTIN SALESMAN 30 1250 29025 TURNER SALESMAN 30 1500 29025 ADAMS CLERK 20 1100 29025 JAMES CLERK 30 950 29025 MILLER CLERK 10 1300 29025 14 rows selected.
Approach 2: Using Single row subquery
SELECT Ename, Job, Deptno, Sal, (SELECT SUM(Sal) FROM Emp) TotalSal FROM Emp e; ENAME JOB DEPTNO SAL TOTALSAL ---------- --------- ---------- ---------- ---------- KING PRESIDENT 10 5000 29025 BLAKE MANAGER 30 2850 29025 CLARK MANAGER 10 2450 29025 JONES MANAGER 20 2975 29025 SCOTT ANALYST 20 3000 29025 FORD ANALYST 20 3000 29025 SMITH CLERK 20 800 29025 ALLEN SALESMAN 30 1600 29025 WARD SALESMAN 30 1250 29025 MARTIN SALESMAN 30 1250 29025 TURNER SALESMAN 30 1500 29025 ADAMS CLERK 20 1100 29025 JAMES CLERK 30 950 29025 MILLER CLERK 10 1300 29025 14 rows selected.
Approach 3: Using Analytical function
SELECT Ename, Job, Deptno, Sal, SUM(Sal) OVER() TotalSal FROM Emp e; ENAME JOB DEPTNO SAL TOTALSAL ---------- --------- ---------- ---------- ---------- KING PRESIDENT 10 5000 29025 BLAKE MANAGER 30 2850 29025 CLARK MANAGER 10 2450 29025 JONES MANAGER 20 2975 29025 SCOTT ANALYST 20 3000 29025 FORD ANALYST 20 3000 29025 SMITH CLERK 20 800 29025 ALLEN SALESMAN 30 1600 29025 WARD SALESMAN 30 1250 29025 MARTIN SALESMAN 30 1250 29025 TURNER SALESMAN 30 1500 29025 ADAMS CLERK 20 1100 29025 JAMES CLERK 30 950 29025 MILLER CLERK 10 1300 29025 14 rows selected.
In addition to aggregate functions, Oracle database also have some other inbuilt functions for analytical processing. Following are some Analytical functions available in Oracle.
| Name | Description |
|---|---|
| RANK | RANK calculates the rank of a value in a group of values. The return type is NUMBER. |
| DENSE_RANK | DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. |
| ROW_NUMBER | ROW_NUMBER assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. |
| FIRST_VALUE | FIRST_VALUE returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. |
| LAST_VALUE | LAST_VALUE returns the last value in an ordered set of values. |
| LAG | LAG provides access to a row at a given physical offset prior to the position of the current row. |
| LEAD | LEAD provides access to a row at a given physical offset beyond the position of the current row. |
| NTILE | NTILE divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. |