Window Functions
Window function syntax is required with analytical functions and can also be used with aggregate functions.
A window function is defined "over" a group of rows (a window) from a query. A window function produces a result value for each input row, where the value is computed according to the records in the current window.
A window is defined with an optional partitioning definition and an ordering definition.
• The partitioning defines sets of rows over which the function results are computed. Partitioning is similar to the grouping of a grouped query. Without the partitioning definition, the function will operate over the entire set of records returned by the query.
• The ordering defines the order of rows within a partition, which determines the function values.
Note: The ordering does not define the order of result rows from the query. If the results of a query need to be sorted, a separate ORDER BY clause is required on the result set.
Window Function Syntax
A window function has the following format:
window_function OVER window_specification
where:
window_function
Specifies an analytical function or an aggregate function.
A select list can have more than one function, each with a different OVER clause.
OVER window_specification
Specifies a partitioning definition (optional) and an ordering definition:
([PARTITION BY partitioning_list ] ORDER BY sort_specification_list )
PARTITION BY partitioning_list
Specifies a list of column names or expressions.
ORDER BY sort_specification_list
Specifies a list of column names or expressions optionally qualified by the ASC or DESC sort directions.
Note: ORDER BY for an aggregate function is not supported.
An empty window specification, that is, OVER(), causes the function results to be computed across all rows in the table.
Windowing Aggregate Functions
Aggregate functions can be used as window functions; that is, you can use the OVER clause with aggregate functions.
Note: The ORDER BY clause in the window specification is not supported for windowing aggregates.
Example:
This query computes, for each partition, the aggregate over the rows in that partition.
SELECT OrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY OrderID) AS "Total"
,AVG(OrderQty) OVER(PARTITION BY OrderID) AS "Avg"
,COUNT(OrderQty) OVER(PARTITION BY OrderID) AS "Count"
,MIN(OrderQty) OVER(PARTITION BY OrderID) AS "Min"
,MAX(OrderQty) OVER(PARTITION BY OrderID) AS "Max"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553);
Results:
OrderID ProductID OrderQty Total Avg Count Min Max
32548 776 1 26 2 12 1 6
32548 777 3 26 2 12 1 6
32548 778 1 26 2 12 1 6
32548 771 1 26 2 12 1 6
32548 772 1 26 2 12 1 6
32548 773 2 26 2 12 1 6
32548 774 1 26 2 12 1 6
32548 714 3 26 2 12 1 6
32548 716 1 26 2 12 1 6
32548 709 6 26 2 12 1 6
32548 712 2 26 2 12 1 6
32548 711 4 26 2 12 1 6
32553 772 1 14 1 8 1 4
32553 775 4 14 1 8 1 4
32553 714 1 14 1 8 1 4
32553 716 1 14 1 8 1 4
32553 777 2 14 1 8 1 4
32553 771 3 14 1 8 1 4
32553 773 1 14 1 8 1 4
32553 778 1 14 1 8 1 4
When the OVER clause is used without a window specification the function results are computed across all rows in the table. The following example returns the row count of the table for each row:
CREATE TABLE foobar(x INTEGER NOT NULL);
INSERT INTO foobar VALUES (1), (2), (2), (3), (3), (3);
SELECT x
,ROW_NUMBER() OVER(ORDER BY x) AS row_nbr
,COUNT(*) OVER() AS row_cnt
FROM foobar
x row_nbr row_cnt
-------------------------------
1 1 6
2 2 6
2 3 6
3 4 6
3 5 6
3 6 6
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
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