introduction to 8i analytic functions

Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which by its very nature is usually less efficient than a single SQL statement.

There are three types of SQL extensions that fall under the banner of "analytic functions" though the first could be said to provide "analytic functionality" rather than actually be analytic functions:

Each of these will be dealt with in turn.

grouping extensions (rollup and cube)

Extensions to the GROUP BY clause enable pre-computed resultsets, summaries and aggregations to be supplied from within the Oracle server itself, rather than by a tool such as SQL*Plus. An example follows.

Example One: Sums the salaries by job and then sub-totals each department (similar to a break sum report in SQL*Plus) and then the entire salary column.

SELECT deptno
,      job
,      SUM(sal)
FROM   emp
GROUP  BY ROLLUP(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

Example Two: Sums the salaries by job and then sub-totals each department and each job type (similar to a break sum report in SQL*Plus) and then the entire salary column. This will provide sub-totals for all columns within the GROUP BY clause.

SELECT deptno
,      job
,      SUM(sal)
FROM   emp
GROUP  BY CUBE(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
           ANALYST         6000
           CLERK           4150
           MANAGER         8275
           PRESIDENT       5000
           SALESMAN        5600
                          29025

Example Three: Using the GROUPING function to "label" the sub-total rows (i.e. determine which rows represented rollups). The GROUPING function returns a value of 1 if the current row is a row representing an aggregated rollup group (such as the sub-total rows) or zero if the row is one of the "source" records itself.

SELECT DECODE(GROUPING(deptno),1,'All Departments',deptno) AS deptno
,      DECODE(GROUPING(job),1,'Job Total',job) AS job
,      SUM(sal)
FROM   emp
GROUP  BY CUBE(deptno,job);

DEPTNO                                   JOB         SUM(SAL)
---------------------------------------- --------- ----------
10                                       CLERK           1300
10                                       MANAGER         2450
10                                       PRESIDENT       5000
10                                       Job Total       8750
20                                       ANALYST         6000
20                                       CLERK           1900
20                                       MANAGER         2975
20                                       Job Total      10875
30                                       CLERK            950
30                                       MANAGER         2850
30                                       SALESMAN        5600
30                                       Job Total       9400
All Departments                          ANALYST         6000
All Departments                          CLERK           4150
All Departments                          MANAGER         8275
All Departments                          PRESIDENT       5000
All Departments                          SALESMAN        5600
All Departments                          Job Total      29025

analytic functions

There are 33 new analytic functions, though it is likely that most users will concentrate on a small number of these and very few will use the statistical capabilities. They are:

The way analytic functions work is to manipulate data contained within returning resultsets. This means they can process, merge and compute against data that has already been fetched from a query and partition and order the resultset into groups while at the same time returning the entire resultset without GROUP BY clauses. There follows a range of examples that demonstrate the analytic functions that, in my opinion, will be used most commonly. The first is a simple running total of salaries within each of an organization's departments.

Example Four: Calculate a running salary total for each department as new employees were hired.

SELECT empno
,      deptno
,      sal
,      SUM(sal) OVER
          (PARTITION BY deptno
           ORDER BY ename ASC NULLS LAST) AS department_running_total
,      ROW_NUMBER() OVER
          (PARTITION BY deptno
           ORDER BY ename ASC NULLS LAST) AS employees_in_running_total
FROM   emp
ORDER  BY
       deptno
,      ename;

ENAME          DEPTNO        SAL DEPARTMENT_RUNNING_TOTAL EMPLOYEES_IN_RUNNING_TOTAL
---------- ---------- ---------- ------------------------ --------------------------
CLARK              10       2450                     2450                          1
KING               10       5000                     7450                          2
MILLER             10       1300                     8750                          3

ADAMS              20       1100                     1100                          1
FORD               20       3000                     4100                          2
JONES              20       2975                     7075                          3
SCOTT              20       3000                    10075                          4
SMITH              20        800                    10875                          5

ALLEN              30       1600                     1600                          1
BLAKE              30       2850                     4450                          2
JAMES              30        950                     5400                          3
MARTIN             30       1250                     6650                          4
TURNER             30       1500                     8150                          5
WARD               30       1250                     9400                          6

This simple example demonstrates the power of analytic functions - they can split resultsets into working groups to compute, order and aggregate data. The above example would be considerably more complex with standard SQL, and would require something like three scans of the EMP table instead of the one scan with the above example.

Analytic functions are invoked using the OVER() clause. This also enables Oracle to distinguish between PL/SQL functions and analytic functions that share the same name such as AVG, MIN and MAX.

There are three components to the OVER clause:

The PARTITION and ORDER BY clauses are demonstrated in the first example above. The resultset was partitioned into the individual departments in the organization. Within each department, the data was ordered by ename (using default criteria (ASC and NULLS LAST). No RANGE clause was added which means that we used the default of RANGE UNBOUNDED PRECEDING, which means include all the preceding records in the current partition in the calculation for the current row. The easiest way to understand analytic functions and windowing is by examples which demonstrate the each of the three components to the OVER() clause.

Example Five: Find the average salary by department and compare each employees' salaries to the department average.

SELECT deptno
,      ename
,      sal
,      ROUND(average_sal_dept,0) AS average_sal_dept
,      ROUND(sal - average_sal_dept,0) AS sal_variance
FROM  (SELECT deptno
       ,      ename
       ,      sal
       ,      AVG(sal) OVER
                 (PARTITION BY deptno) AS average_sal_dept
       FROM   emp);

    DEPTNO ENAME             SAL AVERAGE_SAL_DEPT SAL_VARIANCE
---------- ---------- ---------- ---------------- ------------
        10 CLARK            2450             2917         -467
        10 KING             5000             2917         2083
        10 MILLER           1300             2917        -1617
        20 SMITH             800             2175        -1375
        20 ADAMS            1100             2175        -1075
        20 FORD             3000             2175          825
        20 SCOTT            3000             2175          825
        20 JONES            2975             2175          800
        30 ALLEN            1600             1567           33
        30 BLAKE            2850             1567         1283
        30 MARTIN           1250             1567         -317
        30 JAMES             950             1567         -617
        30 TURNER           1500             1567          -67
        30 WARD             1250             1567         -317

Example Six: Determine the order by which employees joined their respective departments. Also include the employees who preceded and succeeded them.

SELECT deptno
,      ename
,      hiredate
,      LAG(ename,1,NULL) OVER
          (PARTITION BY deptno
           ORDER BY hiredate ASC NULLS LAST) AS previous_employee_ename
,      LEAD(ename,1,NULL) OVER
           (PARTITION BY deptno
            ORDER BY hiredate ASC NULLS LAST) AS next_employee_ename
FROM   emp
ORDER  BY
       deptno;

    DEPTNO ENAME      HIREDATE    PREVIOUS_EMPLOYEE_ENAME        NEXT_EMPLOYEE_ENAME
---------- ---------- ----------- ------------------------------ ---------------------
        10 CLARK      09-JUN-1981                                KING
        10 KING       17-NOV-1981 CLARK                          MILLER
        10 MILLER     23-JAN-1982 KING
        20 SMITH      17-DEC-1980                                JONES
        20 JONES      02-APR-1981 SMITH                          FORD
        20 FORD       03-DEC-1981 JONES                          SCOTT
        20 SCOTT      09-DEC-1982 FORD                           ADAMS
        20 ADAMS      12-JAN-1983 SCOTT
        30 ALLEN      20-FEB-1981                                WARD
        30 WARD       22-FEB-1981 ALLEN                          BLAKE
        30 BLAKE      01-MAY-1981 WARD                           TURNER
        30 TURNER     08-SEP-1981 BLAKE                          MARTIN
        30 MARTIN     28-SEP-1981 TURNER                         JAMES
        30 JAMES      03-DEC-1981 MARTIN

Note: LAG() and LEAD() provide access to rows around the current row, something that was very difficult to achieve prior to 8.1.6. The functions take 3 parameters - expression to be returned from the LAG/LEAD row, the LAG/LEAD offset from the current row, and the value to be returned if the offset is beyond the partition window.

Example Seven: Determine the proportion of each department's salary taken up by its individual employees.

SELECT deptno
,      ename
,      sal
,      dept_sal
,      ROUND(employees_dept_ratio*100,2) AS emps_proportion
FROM  (SELECT deptno
       ,      ename
       ,      sal
       ,      SUM(sal) OVER
                 (PARTITION BY deptno) AS dept_sal
       ,      RATIO_TO_REPORT(sal) OVER
                 (PARTITION BY deptno) AS employees_dept_ratio
       FROM   emp)
ORDER  BY
       deptno;
       
    DEPTNO ENAME             SAL   DEPT_SAL EMPS_PROPORTION
---------- ---------- ---------- ---------- ---------------
        10 CLARK            2450       8750              28
        10 KING             5000       8750           57.14
        10 MILLER           1300       8750           14.86
        20 SMITH             800      10875            7.36
        20 ADAMS            1100      10875           10.11
        20 FORD             3000      10875           27.59
        20 SCOTT            3000      10875           27.59
        20 JONES            2975      10875           27.36
        30 ALLEN            1600       9400           17.02
        30 BLAKE            2850       9400           30.32
        30 MARTIN           1250       9400            13.3
        30 JAMES             950       9400           10.11
        30 TURNER           1500       9400           15.96
        30 WARD             1250       9400            13.3

Example Eight: RANGE windowing. Determine the first and last employee to be employed within 50 days of the current employees' hiredate.

SELECT ename
,      hiredate
,      FIRST_VALUE(ename) OVER
          (ORDER BY hiredate ASC NULLS LAST
           RANGE BETWEEN 50 PRECEDING AND 50 FOLLOWING) AS first_employee
,      LAST_VALUE(ename) OVER
          (ORDER BY hiredate ASC NULLS LAST
           RANGE BETWEEN 50 PRECEDING AND 50 FOLLOWING) AS last_employee
FROM   emp
ORDER  BY
       hiredate;
       
ENAME      HIREDATE    FIRST_EMPL LAST_EMPLO
---------- ----------- ---------- ----------
SMITH      17-DEC-1980 SMITH      SMITH
ALLEN      20-FEB-1981 ALLEN      JONES
WARD       22-FEB-1981 ALLEN      JONES
JONES      02-APR-1981 ALLEN      BLAKE
BLAKE      01-MAY-1981 JONES      CLARK
CLARK      09-JUN-1981 BLAKE      CLARK
TURNER     08-SEP-1981 TURNER     MARTIN
MARTIN     28-SEP-1981 TURNER     KING
KING       17-NOV-1981 MARTIN     JAMES
FORD       03-DEC-1981 KING       JAMES
JAMES      03-DEC-1981 KING       JAMES
MILLER     23-JAN-1982 MILLER     MILLER
SCOTT      09-DEC-1982 SCOTT      ADAMS
ADAMS      12-JAN-1983 SCOTT      ADAMS

Example Nine: ROWS windowing. Determine who was recruited two employees before and three after the current employee (note what happens when employees share hiredates).

SELECT ename
,      hiredate
,      FIRST_VALUE(ename) OVER
          (ORDER BY hiredate ASC NULLS LAST
           ROWS 2 PRECEDING) AS two_employees_back
,      FIRST_VALUE(ename) OVER
          (ORDER BY hiredate DESC NULLS LAST
           ROWS 3 PRECEDING) AS three_employees_forward
FROM   emp
ORDER  BY
       hiredate;
       
ENAME      HIREDATE    TWO_EMPLOYEES_BACK        THREE_EMPLOYEES_FORWARD
---------- ----------- ------------------------- -------------------------
SMITH      17-DEC-1980 SMITH                     JONES
ALLEN      20-FEB-1981 SMITH                     BLAKE
WARD       22-FEB-1981 SMITH                     CLARK
JONES      02-APR-1981 ALLEN                     TURNER
BLAKE      01-MAY-1981 WARD                      MARTIN
CLARK      09-JUN-1981 JONES                     KING
TURNER     08-SEP-1981 BLAKE                     FORD
MARTIN     28-SEP-1981 CLARK                     JAMES
KING       17-NOV-1981 TURNER                    MILLER
JAMES      03-DEC-1981 MARTIN                    ADAMS
FORD       03-DEC-1981 KING                      SCOTT
MILLER     23-JAN-1982 JAMES                     ADAMS
SCOTT      09-DEC-1982 FORD                      ADAMS
ADAMS      12-JAN-1983 MILLER                    ADAMS

top-n queries

These refer to ranked sets of data and are quite commonly requested, such as "Who are our top-n spending customers", "who are our top-n earners" and so on. Oracle 8i provides two ways of providing answers to TOP-N queries; either by the introduction of ORDER BY in in-line views or by analytic function. Prior to these methods being available, TOP-N queries could only be achieved using complex SQL, utilizing self-joins and subqueries.

At its simplest, TOP-N queries can be answered using an ORDER BY in an in-line view and then limiting the number of rows selected from the view. An example follows.

Example Ten: Who were the first three recruits to our organization?

SELECT ROWNUM AS rank
,      ename
,      hiredate
FROM  (SELECT ename
       ,      hiredate
       FROM   emp
       ORDER  BY
              hiredate ASC NULLS LAST)
WHERE  ROWNUM <= 3;

      RANK ENAME      HIREDATE
---------- ---------- -----------
         1 SMITH      17-DEC-1980
         2 ALLEN      20-FEB-1981
         3 WARD       22-FEB-1981

There is ambiguity to the above question, especially in using the above methodology. For example, if five people were recruited on the same day, then how would this question be answered? The ORDER BY in-line view method would generate the employees in no particular order and the stopkey would stop returning rows at record three.

To remove this ambiguity, analytic functions can help. For the following example, I've updated five employees to have the earliest date.

Example Eleven: Who were the first three recruits to our organization?

SELECT hire_rank
,      ename
,      hiredate
FROM  (SELECT ename
       ,      hiredate
       ,      RANK() OVER
                  (ORDER BY HIREDATE ASC NULLS LAST) AS hire_rank
       FROM   emp)
WHERE  hire_rank <= 3;

HIRE_RANK  ENAME      HIREDATE
---------- ---------- -----------
         1 SMITH      01-JAN-1951
         1 ALLEN      01-JAN-1951
         1 WARD       01-JAN-1951
         1 JONES      01-JAN-1951
         1 MARTIN     01-JAN-1951

Technically, this has not answered the actual question but has instead expanded it. In using the RANK() analytic function, the query has returned all the people who joined the organization on the same day, even though this is more than the three people asked for. Note the use of RANK() rather than the DENSE_RANK() function. The RANK() function skips ranking numbers, such that the sixth employee to be returned would be given a rank of 6. DENSE_RANK() would assign the sixth person a rank of 2 as this works on distinct values, rather than rows.

Example Twelve: Determine the ranking of each employees' salary within their departments and within the company as a whole.

SELECT deptno
,      ename
,      sal
,      DENSE_RANK() OVER
          (PARTITION BY deptno
           ORDER BY sal DESC NULLS LAST) AS dept_ranking
,      DENSE_RANK() OVER
          (ORDER BY sal DESC NULLS LAST) AS company_ranking
FROM   emp
ORDER  BY
       deptno;
       
    DEPTNO ENAME             SAL DEPT_RANKING COMPANY_RANKING
---------- ---------- ---------- ------------ ---------------
        10 KING             5000            1               1
        10 CLARK            2450            2               5
        10 MILLER           1300            3               8
        20 SCOTT            3000            1               2
        20 FORD             3000            1               2
        20 JONES            2975            2               3
        20 ADAMS            1100            3              10
        20 SMITH             800            4              12
        30 BLAKE            2850            1               4
        30 ALLEN            1600            2               6
        30 TURNER           1500            3               7
        30 WARD             1250            4               9
        30 MARTIN           1250            4               9
        30 JAMES             950            5              11

Note: The use of DENSE_RANK() and opposed to RANK() means that no rank numbers are skipped. For example, in department 20, SCOTT and FORD have the same salary so they share a dense_rank of 1, while JONES (next highest) has the dense_rank of 2. With RANK(), JONES would be ranked 3, as rank is relative to the number of rows, so the RANK() for SCOTT, FORD and JONES would be 1,1,3 respectively.

analytic functions and pl/sql

In 8i, the analytic functions cannot be used in PL/SQL (in 9i, this is overcome as the SQL and PL/SQL parsers are merged). To work around this, compile your cursor SELECT as a view and SELECT from the view instead of the underlying table in the PL/SQL block or alternatively, use Native Dynamic SQL to send your query to the SQL parser instead of compiling it with the PL/SQL parser (though be careful of this approach if the resulting SQL statement is to be executed many times - NDS necessitates one soft-parse of a statement per execution and as a result can be more "expensive" than the static SQL that could be achieved via the view method.

summary

This introduction demonstrates the power and relative simplicity of the analytic functions. They provide an easy mechanism to compute resultsets that, before 8i, were inefficient, impractical and, in some cases, impossible in "straight SQL".

To the uninitiated, the syntax can at first appear cumbersome, but once you have one or two examples under your belt, you will actively seek opportunities to use them. In addition to their flexibility and power, they are also extremely efficient - this can easily be demonstrated using SQL_TRACE and comparing the analytic functions' performance to the SQL statements that would have been required in the days before 8.1.6. Try it for yourself...

further reading

For an in-depth discussion of analytic functions, see Tom Kyte's "Expert One-on-One Oracle" where he gives Analytic Functions an entire chapter. Also see the February 2002 Oracle magazine for details on 9i extensions to the capabilities. And, of course, the various online documentation stored at OTN, tahiti.oracle.com etc.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, February 2002

Back to Top