ANALYTICAL FUNCTIONS SQL

This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.

It is true that whatever an analytic function does can be done by native SQL, with join and sub-queries. But the same routine done by analytic function is always faster, or at least as fast, when compared to native SQL. Moreover, I am not considering here the amount of time that is spent in coding the native SQLs, testing, debugging and tuning them.

The general syntax of analytic function is:

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

<window_clause> is like "ROW <?>" or "RANK <?>"
All the keywords will be dealt in details as we walk through the examples. The script for creating the schema (SCOTT) on which the example queries of this article are run can be obtained in ORACLE_HOME/sqlplus/demo/demobld.sql of any standard Oracle installation.

How are analytic functions different from group or aggregate functions?

QUERY - 1

SELECT deptno,

COUNT(*) DEPT_COUNT

FROM emp

WHERE deptno IN (20, 30)

GROUP BY deptno;

DEPTNO                 DEPT_COUNT             

---------------------- ---------------------- 

20                     5                      

30                     6                      

2 rows selected

Consider the Query-1 and its result. Query-1 returns departments and their employee count. Most importantly it groups the records into departments in accordance with the GROUP BY clause. As such any non-"group by" column is not allowed in the select clause.

QUERY - 2

SELECT empno, deptno, 

COUNT(*) OVER (PARTITION BY 

deptno) DEPT_COUNT

FROM emp

WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT

---------- ---------- ----------

      7369         20          5

      7566         20          5

      7788         20          5

      7902         20          5

      7876         20          5

      7499         30          6

      7900         30          6

      7844         30          6

      7698         30          6

      7654         30          6

      7521         30          6

11 rows selected.

Now consider the analytic function query (Query-2) and its result. Note the repeating values of DEPT_COUNT column.

This brings out the main difference between aggregate and analytic functions. Though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record. As such any other non-"group by" column or expression can be present in the select clause, for example, the column EMPNO in Query-2.

Analytic functions are computed after all joins, WHERE clause, GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query operates after the analytic functions. So analytic functions can only appear in the select list and in the main ORDER BY clause of the query.

In absence of any PARTITION or <window_clause> inside the OVER( ) portion, the function acts on entire record set returned by the where clause. Note the results of Query-3 and compare it with the result of aggregate function query Query-4.

QUERY - 3

SELECT empno, deptno, 

COUNT(*) OVER ( ) CNT 

FROM emp 

WHERE deptno IN (10, 20)

ORDER BY 2, 1;

   EMPNO     DEPTNO        CNT

---------- ---------- ----------

      7782         10          8

      7839         10          8

      7934         10          8

      7369         20          8

      7566         20          8

      7788         20          8

      7876         20          8

      7902         20          8

QUERY - 4

SELECT COUNT(*) FROM emp WHERE deptno IN (10, 20);

  COUNT(*)

----------

         8