Analytic functions have been used from the early versions of Oracle. It make it easy to developers in many tasks, especially reporting. In this article, I am going to use EMP table for my examples. If you don’t have EMP table, you can find the script from the following link.
https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html
You can also find more about analytic function syntax in the docs.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
Analytic functions are similar to aggregate functions. However, the output of the query does not reduce the rows. In other words, unlike aggregate functions you can see the whole result set when using the analytic functions.
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
SELECT EMPNO, ENAME, JOB, SAL, AVG(SAL) OVER(PARTITION BY DEPTNO) AVG_DEPT_SAL FROM EMP;
The most used sort functions are followings. So, you can look at the result set below to see the difference between each other. ROW_NUMBER function is the conventional way to sort the data. RANK function is like much more gradation and as the name suggests DENSE_RANK sorts the data without leave a blank.
SELECT
EMPNO,
ENAME,
JOB,
DEPTNO,
SAL,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL) RN,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) RNK,
DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) DRNK
FROM EMP ORDER BY DEPTNO;
Which function would you use if you want to get the EMPNO of the person with the highest salary? Actually, there are several ways to get this data. Assuming that only one person gets the highest salary, the following queries get the same result. However, I would prefer to use the KEEP one.
SELECT
EMPNO
FROM (
SELECT T.*, ROW_NUMBER() OVER(ORDER BY SAL DESC) RN FROM EMP T
) WHERE RN = 1;
SELECT
EMPNO
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
SELECT
MAX(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC)
FROM EMP;
No matter which function you use MAX, MIN or AVG. In KEEP clause, you get the same result as long as you get only one row for the highest salary record.
SELECT
MAX(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC) mx,
MIN(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC) mn,
AVG(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC) ag
FROM EMP;
Windowing Clause
It can only be used when ORDER BY clause is present and after the ORDER BY clause. It has two basic formats with using several combinations like the following.
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN UNBOUNDED PRECEDING AND offset PRECEDING RANGE BETWEEN UNBOUNDED PRECEDING AND offset FOLLOWING RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING RANGE BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW RANGE BETWEEN UNBOUNDED FOLLOWING AND offset PRECEDING RANGE BETWEEN UNBOUNDED FOLLOWING AND offset FOLLOWING RANGE BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING RANGE BETWEEN CURRENT ROW AND CURRENT ROW RANGE BETWEEN CURRENT ROW AND offset PRECEDING RANGE BETWEEN CURRENT ROW AND offset FOLLOWING RANGE BETWEEN offset PRECEDING AND UNBOUNDED PRECEDING RANGE BETWEEN offset PRECEDING AND UNBOUNDED FOLLOWING RANGE BETWEEN offset PRECEDING AND CURRENT ROW RANGE BETWEEN offset PRECEDING AND offset PRECEDING RANGE BETWEEN offset PRECEDING AND offset FOLLOWING RANGE BETWEEN offset FOLLOWING AND UNBOUNDED PRECEDING RANGE BETWEEN offset FOLLOWING AND UNBOUNDED FOLLOWING RANGE BETWEEN offset FOLLOWING AND CURRENT ROW RANGE BETWEEN offset FOLLOWING AND offset PRECEDING RANGE BETWEEN offset FOLLOWING AND offset FOLLOWING ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROWS BETWEEN UNBOUNDED PRECEDING AND offset PRECEDING ROWS BETWEEN UNBOUNDED PRECEDING AND offset FOLLOWING ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW ROWS BETWEEN UNBOUNDED FOLLOWING AND offset PRECEDING ROWS BETWEEN UNBOUNDED FOLLOWING AND offset FOLLOWING ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ROWS BETWEEN CURRENT ROW AND CURRENT ROW ROWS BETWEEN CURRENT ROW AND offset PRECEDING ROWS BETWEEN CURRENT ROW AND offset FOLLOWING ROWS BETWEEN offset PRECEDING AND UNBOUNDED PRECEDING ROWS BETWEEN offset PRECEDING AND UNBOUNDED FOLLOWING ROWS BETWEEN offset PRECEDING AND CURRENT ROW ROWS BETWEEN offset PRECEDING AND offset PRECEDING ROWS BETWEEN offset PRECEDING AND offset FOLLOWING ROWS BETWEEN offset FOLLOWING AND UNBOUNDED PRECEDING ROWS BETWEEN offset FOLLOWING AND UNBOUNDED FOLLOWING ROWS BETWEEN offset FOLLOWING AND CURRENT ROW ROWS BETWEEN offset FOLLOWING AND offset PRECEDING ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING
The default value is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW not ROWS. RANGE and ROWS have the different meanings.
SELECT
EMPNO,
ENAME,
JOB,
DEPTNO,
SAL,
AVG(SAL) OVER(ORDER BY DEPTNO RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) range_sal, --equivalent to AVG(SAL) OVER(ORDER BY DEPTNO)
AVG(SAL) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rows_sal
FROM EMP;
As can be seen in the following example, using the WINDOWING clause, the FIRST_VALUE and LAST_VALUE functions can be used instead of the LAG and LEAD functions.
SELECT
EMPNO,
ENAME,
JOB,
DEPTNO,
SAL,
LAG(SAL, 1, SAL) OVER(ORDER BY SAL) lag_val,
FIRST_VALUE(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) first_val,
LEAD(SAL, 1, SAL) OVER(ORDER BY SAL) lead_val,
LAST_VALUE(SAL) OVER(ORDER BY SAL ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) last_val
FROM EMP;
Lastly, I also would like to mention one of the most useful analytic function that introduced in Oracle 11g Release 2. LISTAGG function. Basically, it is used like a pivot process. It aggregates strings easily. It also order the elements in the concatenated list.
SELECT
JOB, LISTAGG(ENAME, ', ') WITHIN GROUP(ORDER BY ENAME) ENAMES
FROM EMP GROUP BY JOB;
I usually use this function when I have to write column names instead of using * sign in SELECT clause for a table that have many columns.
SELECT
LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP(ORDER BY COLUMN_ID)
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'T_TAB1'
AND COLUMN_NAME != 'ID'; --Column names that not intended to be used in SELECT section
NTILE Function
Basically, NTILE function divides an ordered result set into specified number of equal groups or buckets. In the following example, 10 rows result set split 4 groups. The reason why the first buckets have 3 lines is due to the incomplete ratio of the number of lines to the section. In this case, some buckets may have more lines according to the specified sort priority in the function.
SELECT
EMPNO,
ENAME,
NTILE(4) OVER(ORDER BY ENAME) BUCKET
FROM EMP;
If the number of buckets specified is less than the number of rows in the result set. Then, the number of buckets will be reduced. So there is one row per bucket.
SELECT
EMPNO,
ENAME,
NTILE(20) OVER(ORDER BY ENAME) BUCKET
FROM EMP;
The query below shows the employees who are in the middle of the salary scale.
SELECT * FROM (
SELECT
EMPNO,
DEPTNO,
SAL,
NTILE(3) OVER(ORDER BY SAL) BUCKET
FROM EMP
) WHERE BUCKET = 2;
SELECT
EMPNO,
ENAME,
SAL,
DEPTNO,
NTILE(3) OVER(PARTITION BY DEPTNO ORDER BY SAL) BUCKET
FROM EMP;
So, there are many analytic functions that used for different purposes. I hope this article will help you.