May 19, 2015

# Oracle Analytical Functions

5/19/2015 11:27:00 AM

Introduced in Oracle 8i, analytic functions, also known as windowing functions, allow developers to perform tasks in SQL
that were previously confined to procedural languages.SELECT AVG(sal)
FROM   emp;

AVG(SAL)
----------
2073.21429

SQL>

The GROUP BY clause allows us to apply aggregate functions to subsets of rows. For example, we might want to display the average salary for each department.

SELECT deptno, AVG(sal)
FROM   emp
GROUP BY deptno
ORDER BY deptno;

DEPTNO   AVG(SAL)
---------- ----------
10 2916.66667
20     2175
30 1566.66667

SQL>

In both cases, the aggregate function reduces the number of rows returned by the query.

Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query. For example, the following query reports the salary for each employee, along with the average salary of the employees within the department.

SET PAGESIZE 50
BREAK ON deptno SKIP 1 DUPLICATES

SELECT empno, deptno, sal,
AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM   emp;

EMPNO     DEPTNO        SAL AVG_DEPT_SAL
---------- ---------- ---------- ------------
7782         10       2450   2916.66667
7839         10       5000   2916.66667
7934         10       1300   2916.66667

7566         20       2975         2175
7902         20       3000         2175
7876         20       1100         2175
7369         20        800         2175
7788         20       3000         2175

7521         30       1250   1566.66667
7844         30       1500   1566.66667
7499         30       1600   1566.66667
7900         30        950   1566.66667
7698         30       2850   1566.66667
7654         30       1250   1566.66667

14 rows selected.

The query_partition_clause divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY clause affects the action of an aggregate function. If the query_partition_clause is omitted, the whole result set is treated as a single partition. The following query uses an empty OVER clause, so the average presented is based on all the rows of the result set.

CLEAR BREAKS

SELECT empno, deptno, sal,
AVG(sal) OVER () AS avg_sal
FROM   emp;

EMPNO     DEPTNO        SAL    AVG_SAL
---------- ---------- ---------- ----------
7369         20        800 2073.21429
7499         30       1600 2073.21429
7521         30       1250 2073.21429
7566         20       2975 2073.21429
7654         30       1250 2073.21429
7698         30       2850 2073.21429
7782         10       2450 2073.21429
7788         20       3000 2073.21429
7839         10       5000 2073.21429
7844         30       1500 2073.21429
7876         20       1100 2073.21429
7900         30        950 2073.21429
7902         20       3000 2073.21429
7934         10       1300 2073.21429

order_by_clause

The order_by_clause is used to order rows, or siblings, within a partition. So if an analytic function is sensitive to the order of the siblings in a partition you should include an order_by_clause. The following query uses the FIRST_VALUE function to return the first salary reported in each department. Notice we have partitioned the result set by the department, but there is no order_by_clause.

SELECT empno, deptno, sal,
FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno) AS first_sal_in_dept
FROM   emp;

EMPNO     DEPTNO        SAL FIRST_SAL_IN_DEPT
---------- ---------- ---------- -----------------
7782         10       2450              2450
7839         10       5000              2450
7934         10       1300              2450

7566         20       2975              2975
7902         20       3000              2975
7876         20       1100              2975
7369         20        800              2975
7788         20       3000              2975

7521         30       1250              1250
7844         30       1500              1250
7499         30       1600              1250
7900         30        950              1250
7698         30       2850              1250
7654         30       1250              1250

*Divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row.
The buckets are numbered 1 through expr. The expr value must resolve to a positive constant for each partition.*/

SELECT ename, sal,
NTILE(4) OVER (ORDER BY sal DESC) AS quartile
FROM emp
WHERE deptno = 20

ename  SAL  Quartile(Rank)
Ford   3000  1
scott  3000  1
jones  2975  2

ROLLUP
In addition to the regular aggregation results we expect from the GROUP BY clause, the ROLLUP extension produces group subtotals from right to left and a grand total. If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.

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

JOB        DEPTNO  SUM(SAL)
----
DEPTWISE JOBWISE SUM
CLERK           10      1300
MANAGER    10      2450
PRESIDENT  10      5000
ANALYST      20      6000
CLERK           20      1900
OTHERS        20      501
--
JOBWISE SUM
ANALYST            2000
OTHERS              501