Range-based window frames—The SQL/OLAP syntax supports another kind of window frame whose limits are defined in terms of a value-based—or range-based—set of rows, rather than a specific sequence of rows.
Value-based window frames define rows within a window partition that contain a specific range of numeric values. The OLAP function’s ORDER BY clause defines the numeric column to which the range specification is applied, relative to the current row’s value for that column. The range specification uses the same syntax as the rows specification, but the syntax is interpreted in a different way.
The window frame unit, RANGE, defines a window frame whose contents are determined by finding rows in which the ordering column has values within the specified range of value relative to the current row. This is called a logical offset of a window frame, which you can specify with constants, such as “3 preceding,” or any expression that can be evaluated to a numeric constant. When using a window defined with RANGE, there can be only a single numeric expression in the ORDER BY clause.
For example, a frame can be defined as the set of rows with year values some number of years preceding or following the current row’s year:
ORDER BY year ASC range BETWEEN 1 PRECEDING AND CURRENT ROW
The phrase 1 PRECEDING means the current row’s year value minus 1.
This kind of range specification is inclusive. If the current row’s year value is 2000, all rows in the window partition with year values 2000 and 1999 qualify for the frame, regardless of the physical position of those rows in the partition. The rules for including and excluding value-based rows are quite different from the rules applied to row-based frames, which depend entirely on the physical sequence of rows.
Put in the context of an OLAP AVG() calculation, the following partial result set further demonstrates the concept of a value-based window frame. Again, the frame consists of rows that:
Have the same year as the current row
Have the same year as the current row minus 1
The following query demonstrates a range-based window definition:
SELECT dimension, year, measure,
AVG(measure) OVER(PARTITION BY dimension
ORDER BY year ASC
range BETWEEN CURRENT ROW and 1 PRECEDING)
as olap_avg
FROM ...
The averages are computed as follows:
Row [1] = 1999; rows [2] through [5] are excluded; AVG = 10,000/1
Row [2] = 2001; rows [1], [4], and [5] are excluded; AVG = 6,000/2
Row [3] = 2001; rows [1], [4], and [5] are excluded; AVG = 6,000/2
Row [4] = 2002; row [1] is excluded; AVG = 21,000/4
Row [5] = 2002; row [1] is excluded; AVG = 21,000/4
Ascending and descending order for value -based frames—The ORDER BY clause for an OLAP function with a value-based window frame not only identifies the numeric column on which the range specification is based; it also declares the sort order for the ORDER BY values. The following specification is subject to the sort order that precedes it (ASC or DESC):
RANGE BETWEEN CURRENT ROW AND n FOLLOWING
The specification n FOLLOWING means:
Plus n if the partition is sorted in default ascending order (ASC)
Minus n if the partition is sorted in descending order (DESC)
For example, assume that the year column contains four distinct values, from 1999 to 2002. The following table shows the default ascending order of these values on the left and the descending order on the right:
If the current row is 1999 and the frame is specified as follows, rows that contain the values 1999 and 1998 (which does not exist in the table) are included in the frame:
ORDER BY year DESC range BETWEEN CURRENT ROW and 1 FOLLOWING
Using an unbounded window—The following query produces a result set consisting of all of the products accompanied by the total quantity of all products:
SELECT id, description, quantity, SUM(quantity) OVER () AS total FROM products;
Computing deltas between adjacent rows—Using two windows—one over the current row and the other over the previous row—provides a direct way of computing deltas, or changes, between adjacent rows.
SELECT EmployeeID, Surname, SUM(salary)
OVER(ORDER BY BirthDate rows between current row and current row)
AS curr, SUM(Salary)
OVER(ORDER BY BirthDate rows between 1 preceding and 1 preceding)
AS prev, (curr-prev) as delta
FROM Employees
WHERE State IN ('MA', 'AZ', 'CA', 'CO') AND DepartmentID>10
ORDER BY EmployeeID, Surname;
The results from the query:
EmployeeID Surname curr prev delta --------- --------- -------- --------- ----- 148 Jordan 51432.000191 209 Bertrand 29800.000 39300.000 -9500.000278 225 Melkisetian 48500.000 42300.000 6200.000299 657 Overbey 39300.000 41700.750 -2400.750318 902 Crow 41700.750 45000.000 -3299.250586 949 Coleman 42300.000 46200.000 -3900.000690 1053 Poitras 46200.000 29800.000 16400.000703 1090 Martinez 55500.800 51432.000 4068.800949 1154 Savarino 72300.000 55500.800 16799.2001101 1420 Preston 37803.000 48500.000 -10697.0001142 1507 Clark 45000.000 72300.000 -27300.000
Although the window function SUM() is used, the sum contains only the salary value of either the current or previous row because of the way the window is specified. Also, the prev value of the first row in the result is NULL because it has no predecessor; therefore, the delta is NULL as well.
In each of the examples above, the function used with the OVER() clause is the SUM() aggregate function.