Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 3 Oracle Functions
  5. 3.1.4 Analytical Functions

3.1.4 Analytical Functions

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 OVER clause.
  • Analytic functions can appear only in the SELECT list or ORDER BY clause.
  • You can apply OVER clause 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 BY clause to the end of the query. Since, Analytic functions work by taking the ORDER BY clause in the OVER clause, it’s not recommended to use ORDER BY clause at the last of the query.
  • The group of rows defined by the PARTITION BY in OVER clause 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 OVER clause indicates the function is analytic.
  • The PARTITION BY clause 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 the PARTITION BY clause.
  • When you do not provide PARTITION BY to the OVER clause, 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 BY clause 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.

NameDescription
RANKRANK calculates the rank of a value in a group of values. The return type is NUMBER.
DENSE_RANKDENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER.
ROW_NUMBERROW_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_VALUEFIRST_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_VALUELAST_VALUE returns the last value in an ordered set of values.
LAGLAG provides access to a row at a given physical offset prior to the position of the current row.
LEADLEAD provides access to a row at a given physical offset beyond the position of the current row.
NTILENTILE divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row.
Was this article helpful to you? Yes No

How can we help?