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 like 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 named window specification defined elsewhere in the query or a parenthesized optional partitioning definition, ordering definition, and/or frame definition:
([PARTITION BY partitioning_list ] [ORDER BY sort_specification_list] [ROWS BETWEEN frame_bound1 AND frame_bound2])
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.
ROWS BETWEEN frame_bound1 AND frame_bound2
Specifies the set of rows in the current partition over which the window function is computed.
Window functions that support frame definitions include the aggregates, FIRST_VALUE, and LAST_VALUE.
Aggregates support the following options:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
FIRST_VALUE and LAST_VALUE support the option:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Note: ORDER BY is required with frame specifications, even for aggregate functions.
Note: DISTINCT is not allowed for COUNT, SUM, and AVG if the window specification includes an ORDER BY or a frame definition.
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 required with frame specifications, but is not supported for windowing aggregates without frame specifications.
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
The multiple coding of the window specification in this query could be avoided as follows:
SELECT OrderID, ProductID, OrderQty
,SUM(OrderQty) OVER win1 AS "Total"
,AVG(OrderQty) OVER win1 AS "Avg"
,COUNT(OrderQty) OVER win1 AS "Count"
,MIN(OrderQty) OVER win1 AS "Min"
,MAX(OrderQty) OVER win1 AS "Max"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553)
WINDOW win1 AS (PARTITION BY OrderID);
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
Example:
This query computes, for each partition, the sum over the rows in that partition up to the current row as ordered by the ProductID column.
SELECT OrderID, ProductID, OrderQty,
SUM(OrderQty) OVER(PARTITION BY OrderID ORDER BY ProductID ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS "Running Total"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553);
Results:
OrderID ProductID OrderQty Running Total
-------------------------------------------------------
32548 709 6 6
32548 711 4 10
32548 712 2 12
32548 714 3 15
32548 716 1 16
32548 771 1 17
32548 772 1 18
32548 773 2 20
32548 774 1 21
32548 776 1 22
32548 777 3 25
32548 778 1 26
32553 714 1 1
32553 716 1 2
32553 771 3 5
32553 772 1 6
32553 773 1 7
32553 775 4 11
32553 777 2 13
32553 778 1 14
Example: Cumulative Column
Assuming a table called user_table with an ANSIDATE column signed_up, calculate the running total of the signed up per day. In this example, the weekly (or seven day) number is shown in the “Cumulative Total” column.
SELECT
signed_up AS "Begin",
COUNT(*) AS "Daily Signups",
SUM(COUNT(*)) OVER(PARTITION BY 1 ORDER BY year(signed_up) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Cumulative Total"
FROM user_table
WHERE signed_up BETWEEN ANSIDATE('2021-05-01')
AND TIMESTAMPADD(DAY, 6, ANSIDATE('2021-05-01'))
GROUP BY 1
ORDER BY 1;
Results-Example row set:
+----------+---------------+-----------------+
|Begin |Daily Signups |Cumulative Total |
+----------+---------------+-----------------+
|2021-05-01| 93| 93|
|2021-05-02| 92| 185|
|2021-05-03| 96| 281|
|2021-05-04| 95| 376|
|2021-05-05| 90| 466|
|2021-05-06| 93| 559|
|2021-05-07| 91| 650|
+----------+---------------+-----------------+