Analytical Functions
Analytical functions compute an aggregate value based on a group of rows, and can return multiple rows for each group. Analytical functions can be used to calculate percentages or top-N results in a group.
Analytical functions can appear only in the select list of a query or in the ORDER BY clause. They cannot appear in WHERE, ON, HAVING, or GROUP BY clauses. Analytical functions can appear in the select lists of views and derived tables.
Analytical functions are
window functions (see
Window Function Syntax), and thus require an OVER clause.
Note: The ORDER BY clause within the OVER clause supports the NULLS FIRST / NULLS LAST syntax. The default is NULLS LAST.
DENSE_RANK
DENSE_RANK()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in no gaps in the list of ranks. For example: 1, 2, 2, 3, 4, 4, 4, 5.
The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is consecutive.
SELECT empno, deptno, sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank
FROM emp;
EMPNO DEPTNO SAL RANK
---------- ---------- ---------- ----------
839 100 4900 1
782 100 2350 2
934 100 1200 3
788 200 2900 1
902 200 2900 1
566 200 2875 2
876 200 1000 3
369 200 700 4
900 300 2750 1
654 300 1500 2
521 300 1400 3
844 300 1150 4
499 300 1150 4
698 300 850 5
The following query uses the DENSE_RANK function and then gets only the rows ranked 1 from each group. You must provide a correlation name (in this case, x) for the subquery:
SELECT fname, address FROM (SELECT fname, address,
DENSE_RANK() OVER (PARTITION BY fname ORDER BY timestamp DESC) AS rank
FROM tab1) x WHERE rank = 1;
fname address
---------- ------------‑‑‑‑‑‑‑‑‑-
Mary 1600 Pennsylvania Av
NTILE
NTILE(n)
Divides the rows in an ordered partition into n groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
NTILE can be used, for example, to see what quartile, decile, or percentile a row is in.
The following query divides the employees in Department 400 into 4 groups by salary:
SELECT Department_ID, Employee_ID, Salary, NTILE(4)
OVER (PARTITION BY Department_ID ORDER BY Salary DESC) AS Quartile
FROM employees WHERE Department_ID = 400;
Department_ID Employee_ID Salary Quartile
------------- -------------- --------- -----------
400 50 9000 1
400 48 8000 1
400 51 7500 1
400 54 7000 1
400 53 6500 2
400 47 6000 2
400 44 5000 2
400 46 4500 2
400 52 4300 3
400 45 4000 3
400 43 3500 3
400 42 3000 4
400 41 3000 4
400 49 2800 4
When the number of rows is not divisible by n, the later rows will have the smaller number of rows, so the first and second quartiles have 4 rows whereas the third and fourth have only 3.
PERCENT_RANK
PERCENT_RANK()
Calculates the relative rank of a row within a group of rows. PERCENT_RANK returns a number between 0 and 1, which represents the percentage of rows in the group that are less than the current row. If a partition has exactly one row, its percent_rank() is 0. Percent_rank() for the highest value in the group will always be 1.
Use this function to determine the relative standing of a value within a result set.
The following query calculates, for each employee, the percent rank of the employee's salary in the department:
SELECT deptno, empno, sal,
PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr
FROM emp
ORDER BY deptno, pr;
DEPTNO EMPNO SAL PR
------------- ------------- ------------ ----------
100 840 4400 0.000
300 900 2750 0.000
300 654 1500 0.200
300 521 1400 0.400
300 844 1150 0.600
300 499 1150 0.600
300 698 850 1.000
400 789 6500 0.000
500 299 3900 0.000
500 473 2200 0.333
500 371 2200 0.333
500 900 2100 1.000
800 5 10500 0.000
800 854 6200 1.000
RANK
RANK()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in gaps in the list of ranks. For example: 1, 2, 2, 4, 5, 5, 5, 8.
The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is not consecutive.
SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank
FROM emp;
EMPNO DEPTNO SAL RANK
---------- ---------- ---------- ----------
839 100 4900 1
782 100 2350 2
934 100 1200 3
788 200 2900 1
902 200 2900 1
566 200 2875 3
876 200 1000 4
369 200 700 5
698 300 2750 1
499 300 1500 2
844 300 1400 3
521 300 1150 4
654 300 1150 4
900 300 850 6
ROW_NUMBER
ROW_NUMBER()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values in their sort specification are ordered arbitrarily. For example: 1, 2, 3, 4, 5.
The following query assigns a consecutive number to each row. Rows with matching numbers are ordered arbitrarily.
SELECT empno, deptno, sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) AS rownum
FROM emp;
EMPNO DEPTNO SAL ROWNUM
---------- ---------- ---------- ----------
934 100 1200 1
782 100 2350 2
839 100 4900 3
369 200 700 1
876 200 1000 2
566 200 2875 3
788 200 2900 4
902 200 2900 5
900 300 850 1
654 300 1150 2
521 300 1150 3
844 300 1400 4
499 300 1500 5
698 300 2750 6