Window Clause
More than a dozen analytical functions accept a window clause as part of ORDER BY. That capability is so noted in the sections that describe those functions. COUNT is used to illustrate how the window clause works.
The window clause has the following syntax:
{ {ROWS | RANGE}
{ {BETWEEN {UNBOUNDED PRECEDING | CURRENT ROW | value_expr {PRECEDING | FOLLOWING} }
AND {UNBOUNDED FOLLOWING | CURRENT ROW | value_expr {PRECEDING | FOLLOWING} }
|
{UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING} }
}
The following sections describe details of the window clause:
Default Assumptions
RANGE UNBOUNDED PRECEDING is assumed by default when ORDER BY is present but no window clause is supplied. For example, the following three are equivalent:
COUNT(*) OVER (ORDER BY hire_date)
COUNT(*) OVER (ORDER BY hire_date RANGE UNBOUNDED PRECEDING)
COUNT(*) OVER (ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Similarly, the following two COUNT functions involving ROWS are equivalent:
COUNT(*) OVER (ORDER BY hire_date ROWS 1 PRECEDING)
COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
RANGE and the Current Row
In the COUNT example below, the window frame contains the current row, all rows before it, and all ties. If the first three employees were hired on the same date, the count returned would be 3.
COUNT(*) OVER (ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Likewise, when the current row moves to the second and third employees as sorted by hire date, the window frame still contains three rows, and so the result of the function is 3 in both of those cases.
As the current row advances, the resulting counts continue to track the number of employees, but if another hire-date tie occurs—for example, the ninth and tenth employees—the resulting count would be 10 for both of them.
RANGE as a Logical Offset
Because RANGE is a logical offset, the following two functions are equivalent. The frame includes rows that are within three days of the hire date:
COUNT(*) OVER (ORDER BY hire_date RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING)
COUNT(*) OVER (ORDER BY hire_date RANGE BETWEEN INTERVAL ‘3’ days PRECEDING AND INTERVAL ‘3’ days FOLLOWING)
The “interval” syntax allows an expanded range of units (for example, years), and introduces more criteria for the frame size beyond row count.
ROWS and the Current Row
If ROWS is specified instead of RANGE, COUNT behaves the same as ROW_NUMBER; that is, ROWS handles only offsets of the current row. An example of such a COUNT is:
COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ROWS and the Frame’s Maximum Size
An example of a COUNT function that limits the frame size is:
COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
When the current row is the first employee, the frame size is 4 (current plus 3 following). As the current row moves through the table, the frame size can grow to 7. As the current row approaches the end of the table, the frame size goes back down to 4. With ROWS, ties have no effect on the frame size, or the resulting count.
ROWS can point outside of the data set and return results of zero. For example, the following function returns 0 when the current row is the first row of the table, because the frame is empty:
COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
Note: In this example, even when the current row is far enough into the table to return a nonzero count, the current row is not included, because the rows all precede the current row.