SQL Language Guide : 4. Elements of SQL Statements : SQL Functions : Window Functions
 
Share this page                  
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