The analytical functions fall into categories:
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.
SQL> SQL>-- w ww. jav a 2s . co 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>