具有逻辑偏移的窗口 (RANGE)
RANGE 关键字将分析窗口框架定义为当前行的逻辑偏移。
注意
具有逻辑偏移的分析函数返回的值始终具有确定性。对于每个行，分析函数使用窗口顺序子句 (ORDER_BY) 列或表达式来计算窗口框架大小，具体如下所述：
-
在当前分区中，根据连续行的
ORDER_BY值评估当前行的ORDER_BY值。 -
确定这些行中有哪些行符合相对于当前行的指定范围要求。
-
创建仅包含这些行的窗口框架。
-
在当前窗口中执行。
示例
此示例使用 property_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)
AVG 分析函数可以获取每个街区的近似销售价格的平均值。对于每个住宅，下列查询计算了销售价格比该住宅高或低 50000 美元的所有其他街区住宅的平均销售价格：
=> 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 按以下方式处理此查询：
-
AVG评估了第一个分区 (Jamaica Plain) 的第 1 行，但没有发现比此行的sell_price（353000 美元）高或低 50000 美元范围内的销售价格。AVG创建了一个仅包含此行的窗口，并为第 1 行返回了平均值 353000 美元：
-
AVG评估了第 2 行，发现了有三个sell_price值与当前行的相差幅度在 50000 美元范围内。AVG创建了一个包含了这三个行的窗口，并为第 2 行返回了平均值 458000 美元：
-
AVG评估了第 3 行，同样发现了有三个sell_price值与当前行的相差幅度在 50000 美元范围内。AVG创建了一个窗口，它与前面的窗口相同，并为第 3 行返回了相同的平均值 458000 美元： -
AVG评估了第 4 行，发现了有四个sell_price值与当前行的相差幅度在 50000 美元范围内。AVG将窗口扩展到可包含第 2 行至第 5 行，并为第 4 行返回了平均值 472250 美元：
-
同样地，
AVG还评估了此分区的其他行。当函数评估第二个分区 (Roslindale) 的第一行时，它会如下所述重置窗口：
限制
如果 RANGE 指定一个常数值，此值的数据类型与窗口的 ORDER BY 数据类型必须相同。以下情况除外：
-
RANGE如果窗口顺序子句数据类型为以下之一，可以指定INTERVAL Year to Month：TIMESTAMP、TIMESTAMP WITH TIMEZONE或DATE。TIME和TIME WITH TIMEZONE均不受支持。 -
RANGE如果窗口顺序子句数据为以下之一，可以指定INTERVAL Day to Second：TIMESTAMP、TIMESTAMP WITH TIMEZONE、DATE、TIME或TIME WITH TIMEZONE。
窗口顺序子句必须指定以下数据类型之一：NUMERIC、DATE/TIME、FLOAT 或 INTEGER。如果窗口指定了以下框架之一，则会忽视此要求：
-
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING