The underdog of Oracle SQL – Analytic functions

Oracle analytic functions

it is of the most under-utilized features in Oracle SQL. Most developers are either unaware of it or find normal SQL syntax easier than analytic function. But, the fact is, Oracle Analytic functions are more efficient and a faster way of querying the database than using a chain of joins, sub-queries, group by, order by. The objective of this article is not to teach Analytic functions, but it is an effort to ignite your mind towards using it more often.

Oracle analytic function syntax are part of normal SQL queries and it can be written in combination with normal SQL.

What is an analytic function?

Analytic functions work on a group of rows to return an aggregate value, it differs from the normal group by functions like SUM by virtue of returning multiple rows per group instead of 1 row per group.

Here are some examples of oracle analytic functions

Assume a Subject table that holds a list of subjects for each course.

The following query will return a list of course and the number of subjects in each course by using the normal group by analytic clause.

This analytic clause does not allow us to select any other column like name which is not specified in group by analytic clause.

With the Oracle analytic function, it is possible to select columns other than the group. It also allows us to return multiple rows per group. The below query returns subject name, course and also the number of subjects in each course.

This query allows us to return multiple data sets i.e course & subject set and number of subjects in each course using single query which is not possible with normal SQL queries.

Further explanations and examples will give you a better idea of how Oracle analytic functions can be used to extend normal SQL queries and retrieve complex data more efficiently and make it easier to code.

In-built functions

Common analytic functions are SUM, COUNT, AVG, MIN, MAX. Other more advanced functions are LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE.

LEAD and LAG functions are quite interesting in the fact that LEAD allows us to compute an expression on the data in next immediate row and return the result to the current row. LAG does the opposite of returning result on computation of previous row to the current row.

RANK and DENSE_RANK allows us to return the rank of records based on some column value or expression within a group or overall. We can use this rank to order subset of records. For example, order records based on rank of salary within each department.

Execution

In SQL queries, analytic functions are executed at the end, after execution of WHERE, GROUP BY, HAVING clauses. ORDER BY is executed after analytic functions. Analytic functions are allowed only in SELECT clause or ORDER BY clause. Nested analytic functions are not allowed, but they are allowed in sub-queries.

It is strongly recommended that Oracle analytic functions be used wherever possible as they are optimized for data mining instead of composing a more cumbersome and complicated set of SQL queries. Feel free to go through the references mentioned in this post to understand more about the use of analytic functions.