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
Adans 1100 3
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
