Oracle SQL - Analytic Functions Analytic Functions

Introduction

The analytical functions fall into categories:

  • ranking,
  • aggregate,
  • row comparison, and
  • statistical.

The function has this syntax:

function(<arguments>) OVER(<analytic clause>) 

The <arguments> part may be empty, as it is in the above example: "RANK()."

The <analytic clause> part of the function will contain an ordering, partitioning, or windowing clause.

An example of such a function in a result set would be this:

The ordering clause is illustrated in the above example: "OVER(ORDER BY product)."


SELECT RANK() OVER(ORDER BY product) 
FROM inventory 

Consider the following code. It reports the salary ranking by department for all emp.

SELECT e1.deptno, e1.ename, e1.msal,
       (SELECT COUNT(1)
        FROM emp e2
        WHERE  e2.msal > e1.msal)+1 sal_rank
FROM emp e1
ORDER BY e1.msal DESC;

Here, the query doesn't use an analytic function.

We can generates the same report using the analytic function RANK.

SELECT e1.deptno, e1.ename, e1.msal,
       RANK() OVER (ORDER BY e1.msal DESC) sal_rank
FROM emp e1
ORDER BY e1.msal DESC;

Using the analytic function creates a statement that is simpler and self documenting.

The basic format of the analytic function.

FUNCTION   expr-list   OVER ORDER BY   expr-list

The term OVER indicates an analytic function.

There are analytic functions with the same names as regular functions.

For example, the analytic functions SUM and AVG have the same names as their non-analytic counterparts.

The clause ORDER BY indicates the order in which the functions are applied.

In the preceding example, RANK is applied according to the employee salary.

The default for ORDER BY is ascending, smallest to largest.

Specify the keyword DESC, for descending, to sort from largest to smallest.

The ORDER BY clause must come last in the analytic function.

Demo

SQL>
SQL>-- from   w w w  .  j  av  a2s  .c o  m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> SELECT e1.deptno, e1.ename, e1.msal,
  2         (SELECT COUNT(1)
  3          FROM emp e2
  4          WHERE  e2.msal > e1.msal)+1 sal_rank
  5  FROM emp e1
  6  ORDER BY e1.msal DESC;

   DEPTNO | ENAME    |      MSAL |  SAL_RANK
--------- | -------- | --------- | ---------
    30    | JONES    |  08000.00 |  00001.00
    1     | KING     |  05000.00 |  00002.00
    2     | FORD     |  03000.00 |  00003.00
    2     | SCOTT    |  03000.00 |  00003.00
    2     | JACK     |  02975.00 |  00005.00
    1     | BLAKE    |  02850.00 |  00006.00
    1     | CLARK    |  02450.00 |  00007.00
    2     | SMITH    |  01800.00 |  00008.00
    30    | ALLEN    |  01600.00 |  00009.00
    30    | BREAD    |  01500.00 |  00010.00
    1     | MARY     |  01300.00 |  00011.00

   DEPTNO | ENAME    |      MSAL |  SAL_RANK
--------- | -------- | --------- | ---------
    30    | BROWN    |  01250.00 |  00012.00
    1     | WARD     |  01250.00 |  00012.00
    2     | ADAMS    |  01100.00 |  00014.00

14 rows selected.

SQL>
SQL> SELECT e1.deptno, e1.ename, e1.msal,
  2         RANK() OVER (ORDER BY e1.msal DESC) sal_rank
  3  FROM emp e1
  4  ORDER BY e1.msal DESC;

   DEPTNO | ENAME    |      MSAL |  SAL_RANK
--------- | -------- | --------- | ---------
    30    | JONES    |  08000.00 |  00001.00
    1     | KING     |  05000.00 |  00002.00
    2     | FORD     |  03000.00 |  00003.00
    2     | SCOTT    |  03000.00 |  00003.00
    2     | JACK     |  02975.00 |  00005.00
    1     | BLAKE    |  02850.00 |  00006.00
    1     | CLARK    |  02450.00 |  00007.00
    2     | SMITH    |  01800.00 |  00008.00
    30    | ALLEN    |  01600.00 |  00009.00
    30    | BREAD    |  01500.00 |  00010.00
    1     | MARY     |  01300.00 |  00011.00

   DEPTNO | ENAME    |      MSAL |  SAL_RANK
--------- | -------- | --------- | ---------
    30    | BROWN    |  01250.00 |  00012.00
    1     | WARD     |  01250.00 |  00012.00
    2     | ADAMS    |  01100.00 |  00014.00

14 rows selected.

SQL>

Related Topics