## 示例表数据如下：

test=# select * from tcost ;
path | cost
------+-------
111 |  23.3
111 |  33.4
111 |   3.4
222 |   3.4
222 |  33.4
222 | 333.4
32 |   3.4
32 |   0.4
32 |  0.04
(9 rows)

test=#


## ROWS

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc rows between current row and 2 following ) as row from tcost;
path | cost  | sum_cost |  row
------+-------+----------+-------
222 | 333.4 |    333.4 | 400.2
111 |  33.4 |    400.2 |  90.1
222 |  33.4 |    400.2 |  60.1
111 |  23.3 |    423.5 |  30.1
111 |   3.4 |    433.7 |  10.2
32 |   3.4 |    433.7 |   7.2
222 |   3.4 |    433.7 |  3.84
32 |   0.4 |    434.1 |  0.44
32 |  0.04 |   434.14 |  0.04
(9 rows)

test=#


test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (partition by path order by cost desc rows between current row and 2 following ) as row from tcost;
path | cost  | sum_cost |  row
------+-------+----------+-------
32 |   3.4 |    433.7 |  3.84
32 |   0.4 |    434.1 |  0.44
32 |  0.04 |   434.14 |  0.04
111 |  33.4 |    400.2 |  60.1
111 |  23.3 |    423.5 |  26.7
111 |   3.4 |    433.7 |   3.4
222 | 333.4 |    333.4 | 370.2
222 |  33.4 |    400.2 |  36.8
222 |   3.4 |    433.7 |   3.4
(9 rows)

test=#


## RANGE

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc range  between current row and  UNBOUNDED  following ) as range from tcost;
path | cost  | sum_cost | range
------+-------+----------+--------
222 | 333.4 |    333.4 | 434.14
111 |  33.4 |    400.2 | 100.74
222 |  33.4 |    400.2 | 100.74
111 |  23.3 |    423.5 |  33.94
111 |   3.4 |    433.7 |  10.64
32 |   3.4 |    433.7 |  10.64
222 |   3.4 |    433.7 |  10.64
32 |   0.4 |    434.1 |   0.44
32 |  0.04 |   434.14 |   0.04
(9 rows)

test=#


test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc range  between current row and  UNBOUNDED  following ) as range,sum(cost) over (order by cost desc rows between current row and UNBOUNDED  following ) as row from tcost;
path | cost  | sum_cost | range  |  row
------+-------+----------+--------+--------
222 | 333.4 |    333.4 | 434.14 | 434.14
111 |  33.4 |    400.2 | 100.74 | 100.74
222 |  33.4 |    400.2 | 100.74 |  67.34
111 |  23.3 |    423.5 |  33.94 |  33.94
111 |   3.4 |    433.7 |  10.64 |  10.64
32 |   3.4 |    433.7 |  10.64 |   7.24
222 |   3.4 |    433.7 |  10.64 |   3.84
32 |   0.4 |    434.1 |   0.44 |   0.44
32 |  0.04 |   434.14 |   0.04 |   0.04
(9 rows)

test=#


## RANGE 和 ROWS 在PostgreSQL中的语法

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end


frame_start 和 frame_end可以是：

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING


## RANGE时，请注意有没有 order by 的区别

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (range  between current row and  UNBOUNDED  following ) as no_order_by_range,sum(cost) over (order by cost desc range between current row and UNBOUNDED  following ) as has_order_by_range from tcost;
path | cost  | sum_cost | no_order_by_range | has_order_by_range
------+-------+----------+-------------------+--------------------
222 | 333.4 |    333.4 |            434.14 |             434.14
111 |  33.4 |    400.2 |            434.14 |             100.74
222 |  33.4 |    400.2 |            434.14 |             100.74
111 |  23.3 |    423.5 |            434.14 |              33.94
111 |   3.4 |    433.7 |            434.14 |              10.64
32 |   3.4 |    433.7 |            434.14 |              10.64
222 |   3.4 |    433.7 |            434.14 |              10.64
32 |   0.4 |    434.1 |            434.14 |               0.44
32 |  0.04 |   434.14 |            434.14 |               0.04
(9 rows)

test=#


## ROWS时，请注意有没有 order by 的区别

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (rows  between current row and  UNBOUNDED  following ) as no_order_by_rows,sum(cost) over (order by cost desc rows between current row and UNBOUNDED  following ) as has_order_by_rows from tcost;
path | cost  | sum_cost | no_order_by_rows | has_order_by_rows
------+-------+----------+------------------+-------------------
222 | 333.4 |    333.4 |           434.14 |            434.14
111 |  33.4 |    400.2 |           100.74 |            100.74
222 |  33.4 |    400.2 |            67.34 |             67.34
111 |  23.3 |    423.5 |            33.94 |             33.94
111 |   3.4 |    433.7 |            10.64 |             10.64
32 |   3.4 |    433.7 |             7.24 |              7.24
222 |   3.4 |    433.7 |             3.84 |              3.84
32 |   0.4 |    434.1 |             0.44 |              0.44
32 |  0.04 |   434.14 |             0.04 |              0.04
(9 rows)

test=#


## 总结

ROWS：是按物理行来进行窗口级别里再次进行范围选择的。
RANGE：是按逻辑行来进行窗口级别里再次进行范围选择的。RANGE时，相同行会被合并成同一条数据再进行计算，相同行窗口计算时的结果也是相同的。

不同行是说ORDER BY排序时具有不同的数值的行。