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, 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
FIRST_VALUE() OVER() and LAST_VALUE() OVER()
FIRST_VALUE (scalar_value_expression) [null_spec] OVER([partition_by_clause] order_by_clause [frame_spec])
LAST_VALUE (scalar_value_expression) [null_spec] OVER([partition_by_clause] order_by_clause frame_spec)
Produces one row per input row. The same value is returned for every row in the partition, or if there is no partition_by_clause, every row in the result.
where:
scalar_value_expr
Specifies any scalar expression that can appear in the select list of a query. Can contain constants, row values, operators, scalar functions, and scalar subqueries.
null_spec
Indicates whether to include null values in the selection of a first_value or last_value. Valid values are:
RESPECT NULLS
(Default) Uses the expression value in the first or last row.
IGNORE NULLS
Uses the first or last non-null expression value.
OVER ([partition_by_clause] order_by_clause frame_spec)
For FIRST_VALUE a frame specification is required if using IGNORE NULLS and must be ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
For LAST_VALUE a frame specification is required and must be ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The following query ranks each employee in a department based on his salary:
SELECT empno, deptno, sal,
FIRST_VALUE(empno) OVER (PARTITION BY deptno ORDER BY sal DESC)
FROM emp;
EMPNO DEPTNO SAL FIRST_VALUE
---------- ---------- ---------- ----------
839 100 4900 839
782 100 2350 839
934 100 1200 839
788 200 2900 788
902 200 2900 788
566 200 2875 788
876 200 1000 788
369 200 700 788
900 300 2750 900
654 300 1500 900
521 300 1400 900
844 300 1150 900
499 300 1150 900
698 300 850 900
LAG and LEAD
LAG | LEAD (scalar_expression [,offset] [,default]) [RESPECT NULLS | IGNORE NULLS]
Returns a scalar column value from a row before (LAG) or after (LEAD) the current row. Use LAG to compare values in the current row with values in a previous row. Use LEAD to compare values in the current row with values in a following row.
LAG and LEAD are useful for comparing values across multiple rows—for example, across time periods.
where:
scalar_expression
Is an expression that returns a single value. The expression can be a constant, row value, operator, scalar function, and scalar subquery.
offset
Specifies the number of rows of lag or lead from the current row. Must be an expression that resolves to a positive integer. Default is 1.
default
Returns the specified value if no lagging or leading row is present (for example, lag of the first value in an ordered set). Default is NULL.
RESPECT NULLS | IGNORE NULLS
Indicates whether to include null values in the selection of a lag or lead value. Default is RESPECT NULLS.
IGNORE NULLS and offset N returns the N-th previous/following not-NULL value.
RESPECT NULLS and offset N returns the N-th previous/following value, counting NULL values.
The following query shows the date, the weather, and the previous day's weather:
SELECT wdate, forecast, LAG(forecast) OVER(ORDER BY wdate) AS lag
FROM weather ORDER BY wdate;
wdate forecast lag
---------------------------------
2012-06-12 overcast NULL
2012-06-13 sunny overcast
2012-06-14 rain sunny
The following query finds the salary of each employee in department 300 and lists the next higher salary:
SELECT deptno, empno, sal,
LEAD(sal) OVER(ORDER BY sal) AS next_sal
FROM salary WHERE deptno=300;
deptno empno sal next_sal
----------------------------------
300 698 1150 1150
300 844 1150 1450
300 499 1450 1500
300 521 1500 2750
300 654 2750
300 900
The following example demonstrates the use of IGNORE NULLS:
CREATE TABLE lead_table(c1 INT , c2 INT , c3 INT);
INSERT INTO lead_table VALUES (1,10,1), (2, 20,2), (NULL, 30,1), (NULL,40,2), (5,50,1);
SELECT
c1, c2, c3,
LEAD(c1 , 1,100) OVER (ORDER BY c2) AS lead1,
LEAD(c1 IGNORE NULLS, 1,200) OVER (ORDER BY c2) AS lead1ign,
LAG (c1 , 2,300) OVER (ORDER BY c2) AS lag2,
LAG (c1 IGNORE NULLS, 2,400) OVER (ORDER BY c2) AS lag2ign
FROM lead_table;
c1 c2 c3 lead1 lead1ign lag2 lag2ign
------ ---- -- ----- -------- ----- --------
1 10 1 2 2 300 400
2 20 2 NULL 5 300 400
NULL 30 1 NULL 5 1 1
NULL 40 2 5 5 2 1
5 50 1 100 200 NULL 1
Note the difference in results between the lag2 and lag2ign columns.
For the fourth row, for RESPECT NULLS the value two rows above is 2.
However, for IGNORE NULLS the second non-null value above is 1.
SELECT
c1, c2, c3,
LEAD(c1 , 1,100) OVER (PARTITION BY c3 ORDER BY c2) AS lead1,
LEAD(c1 IGNORE NULLS, 1,200) OVER (PARTITION BY c3 ORDER BY c2) AS lead1ign,
LAG (c1 , 1,300) OVER (PARTITION BY c3 ORDER BY c2) AS lag1,
LAG (c1 IGNORE NULLS, 1,400) OVER (PARTITION BY c3 ORDER BY c2) AS lag1ign
FROM lead_table;
c1 c2 c3 lead1 lead1ign lag1 lag1ign
---- --- --- ----- --------- ---- -------
1 10 1 NULL 5 300 400
NULL 30 1 5 5 1 1
5 50 1 100 200 NULL 1
2 20 2 NULL 200 300 400
NULL 40 2 100 200 2 2
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