Windows with a Logical Offset (RANGE)
The RANGE
keyword defines an analytic window frame as a logical offset from the current row.
The value returned by an analytic function with a logical offset is always deterministic.
For each row, an analytic function uses the window order clause (ORDER_BY
) column or expression to calculate window frame dimensions as follows:
- Within the current partition, evaluates the
ORDER_BY
value of the current row against theORDER_BY
values of contiguous rows. - Determines which of these rows satisfy the specified range requirements relative to the current row.
- Creates a window frame that includes only those rows.
- Executes on the current window.
Example
This example uses the table property_sales
, which contains data about neighborhood home sales:
=> SELECT property_key, neighborhood, sell_price FROM property_sales ORDER BY neighborhood, sell_price; property_key | neighborhood | sell_price --------------+---------------+------------ 10918 | Jamaica Plain | 353000 10921 | Jamaica Plain | 450000 10927 | Jamaica Plain | 450000 10922 | Jamaica Plain | 474000 10919 | Jamaica Plain | 515000 10917 | Jamaica Plain | 675000 10924 | Jamaica Plain | 675000 10920 | Jamaica Plain | 705000 10923 | Jamaica Plain | 710000 10926 | Jamaica Plain | 875000 10925 | Jamaica Plain | 900000 10930 | Roslindale | 300000 10928 | Roslindale | 422000 10932 | Roslindale | 450000 10929 | Roslindale | 485000 10931 | Roslindale | 519000 10938 | West Roxbury | 479000 10933 | West Roxbury | 550000 10937 | West Roxbury | 550000 10934 | West Roxbury | 574000 10935 | West Roxbury | 598000 10936 | West Roxbury | 615000 10939 | West Roxbury | 720000 (23 rows)
The analytic function AVG can obtain the average of proximate selling prices within each neighborhood. The following query calculates for each home the average sale for all other neighborhood homes whose selling price was $50k higher or lower:
=> SELECT property_key, neighborhood, sell_price, AVG(sell_price) OVER( PARTITION BY neighborhood ORDER BY sell_price RANGE BETWEEN 50000 PRECEDING and 50000 FOLLOWING)::int AS comp_sales FROM property_sales ORDER BY neighborhood; property_key | neighborhood | sell_price | comp_sales --------------+---------------+------------+------------ 10918 | Jamaica Plain | 353000 | 353000 10927 | Jamaica Plain | 450000 | 458000 10921 | Jamaica Plain | 450000 | 458000 10922 | Jamaica Plain | 474000 | 472250 10919 | Jamaica Plain | 515000 | 494500 10917 | Jamaica Plain | 675000 | 691250 10924 | Jamaica Plain | 675000 | 691250 10920 | Jamaica Plain | 705000 | 691250 10923 | Jamaica Plain | 710000 | 691250 10926 | Jamaica Plain | 875000 | 887500 10925 | Jamaica Plain | 900000 | 887500 10930 | Roslindale | 300000 | 300000 10928 | Roslindale | 422000 | 436000 10932 | Roslindale | 450000 | 452333 10929 | Roslindale | 485000 | 484667 10931 | Roslindale | 519000 | 502000 10938 | West Roxbury | 479000 | 479000 10933 | West Roxbury | 550000 | 568000 10937 | West Roxbury | 550000 | 568000 10934 | West Roxbury | 574000 | 577400 10935 | West Roxbury | 598000 | 577400 10936 | West Roxbury | 615000 | 595667 10939 | West Roxbury | 720000 | 720000 (23 rows)
AVG processes this query as follows:
AVG
evaluates row 1 of the first partition (Jamaica Plain), but finds no sales within $50k of this row'ssell_price
, ($353k).AVG
creates a window that includes this row only, and returns an average of 353k for row 1:AVG
evaluates row 2 and finds threesell_price
values within $50k of the current row.AVG
creates a window that includes these three rows, and returns an average of 458k for row 2:AVG
evaluates row 3 and finds the same threesell_price
values within $50k of the current row.AVG
creates a window identical to the one before, and returns the same average of 458k for row 3:AVG
evaluates row 4 and finds foursell_price
values within $50k of the current row.AVG
expands its window to include rows 2 through 5, and returns an average of $472.25k for row 4:- In similar fashion,
AVG
evaluates the remaining rows in this partition. When the function evaluates the first row of the second partition (Roslindale), it resets the window as follows:
Restrictions
If RANGE
specifies a constant value, that value's data type and the window's ORDER BY
data type must be the same. The following exceptions apply:
RANGE
can specifyINTERVAL Year to Month
if the window order clause data type is one of following:TIMESTAMP
,TIMESTAMP WITH TIMEZONE
, orDATE
.TIME
andTIME WITH TIMEZONE
are not supported.RANGE
can specifyINTERVAL Day to Second
if the window order clause data is one of following:TIMESTAMP
,TIMESTAMP WITH TIMEZONE
,DATE
,TIME
, orTIME WITH TIMEZONE
.
The window order clause must specify one of the following data types: NUMERIC
, DATE/TIME
, FLOAT
or INTEGER
. This requirement is ignored if the window specifies one of following frames:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING