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