postgresql 11开始支持sql:2011标准的所有window frame子句
PostgreSQL 在2009年发布的8.4版本中开始支持window语法，直到2017-10-5发布了pg 11才完全支持sql:2011中所有的子句（所以具体厂商实现通常要三五年甚至更长才能完成对标准规范的支持）。
窗口函数的语法定义如下：
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
window_definition定义如下：
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause定义如下：
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
帧定义里面RANGE ROWS GROUPS的含义：
range, groups的边界都是以peer的最外围作为边界，例如字段值 1,1,1,2,3,3，边界是1或者3时，包含所有的1以及所有的3。
如果order by是一个表达式，那么边界以表达式的值来计算，如果多行表达式的值一致，那么这些行就是一组PEER。
- groups，相同表达式或列值作为一个peer簇，边界输入为前后N个簇。current_row表示包含当前行所在簇的最大范围。
- range，当前值的差值作为判断边界的条件。current_row表示包含当前行所在簇的最大范围。
frame_start和frame_end的取值为如下之一：
UNBOUNDED PRECEDING offset PRECEDING CURRENT ROW offset FOLLOWING UNBOUNDED FOLLOWING
frame_exclusion的取值为如下之一：
EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
window的定义如下：
WINDOW window_name AS ( window_definition ) [, ...]
因为一般来说，多个统计需要用到相同的窗口定义，如都是parititon by xxx order by yyy preceding and following，所以把window抽象出来单独子句，也算是为了减少代码重复。
window_definition 的取值如下：
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
window定义在HAVING子句最后，也是就普通SELECT的最后，如下：
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
示例
lightdb@postgres=# select *, avg(score) over w1, sum(score) over w1, count(score) over w1, first_value(score) over w1, last_value(score) over w1 from t window w1 as (partition by sub order by score groups between 1 preceding and 6 following) order by sub,score desc; sid | sub | des | score | avg | sum | count | first_value | last_value -----+-----+------+-------+-------------------+-------+-------+-------------+------------ 4 | 1 | 语文 | 100 | 99.5 | 199 | 2 | 99 | 100 3 | 1 | 语文 | 99 | 96.5 | 289.5 | 3 | 90.5 | 100 7 | 1 | 语文 | 90.5 | 94.625 | 378.5 | 4 | 89 | 100 6 | 1 | 语文 | 89 | 91.9 | 459.5 | 5 | 81 | 100 1 | 1 | 语文 | 81 | 88.41666666666667 | 530.5 | 6 | 71 | 100 2 | 1 | 语文 | 71 | 80.35714285714286 | 562.5 | 7 | 32 | 100 5 | 1 | 语文 | 32 | 80.35714285714286 | 562.5 | 7 | 32 | 100 7 | 2 | 数学 | 99.5 | 98.25 | 196.5 | 2 | 97 | 99.5 6 | 2 | 数学 | 97 | 97.5 | 292.5 | 3 | 96 | 99.5 2 | 2 | 数学 | 96 | 95.625 | 382.5 | 4 | 90 | 99.5 4 | 2 | 数学 | 90 | 92.7 | 463.5 | 5 | 81 | 99.5 1 | 2 | 数学 | 81 | 90.25 | 541.5 | 6 | 78 | 99.5 3 | 2 | 数学 | 78 | 79.07142857142857 | 553.5 | 7 | 12 | 99.5 5 | 2 | 数学 | 12 | 79.07142857142857 | 553.5 | 7 | 12 | 99.5 1 | 3 | 英语 | 100 | 96.25 | 385 | 4 | 95 | 100 3 | 3 | 英语 | 95 | 95.6 | 478 | 5 | 93 | 100 4 | 3 | 英语 | 95 | 95.6 | 478 | 5 | 93 | 100 2 | 3 | 英语 | 95 | 95.6 | 478 | 5 | 93 | 100 7 | 3 | 英语 | 93 | 94.16666666666667 | 565 | 6 | 87 | 100 6 | 3 | 英语 | 87 | 88.71428571428571 | 621 | 7 | 56 | 100 5 | 3 | 英语 | 56 | 88.71428571428571 | 621 | 7 | 56 | 100 6 | 4 | 物理 | 71 | 67.4 | 337 | 5 | 65 | 71 7 | 4 | 物理 | 71 | 67.4 | 337 | 5 | 65 | 71 5 | 4 | 物理 | 65 | 65.28571428571429 | 457 | 7 | 60 | 71 4 | 4 | 物理 | 65 | 65.28571428571429 | 457 | 7 | 60 | 71 3 | 4 | 物理 | 65 | 65.28571428571429 | 457 | 7 | 60 | 71 1 | 4 | 物理 | 60 | 65.28571428571429 | 457 | 7 | 60 | 71 2 | 4 | 物理 | 60 | 65.28571428571429 | 457 | 7 | 60 | 71 (28 rows) lightdb@postgres=# select *, sum(score) over w1, avg(score) over w1, first_value(score) over w1, last_value(score) over w1 from t window w1 as (partition by sub order by score::int range between 1 preceding and 6 following) order by sub, score; sid | sub | des | score | sum | avg | first_value | last_value -----+-----+------+-------+-------+-------+-------------+------------ 5 | 1 | 语文 | 32 | 32 | 32 | 32 | 32 2 | 1 | 语文 | 71 | 71 | 71 | 71 | 71 1 | 1 | 语文 | 81 | 81 | 81 | 81 | 81 6 | 1 | 语文 | 89 | 179.5 | 89.75 | 89 | 90.5 7 | 1 | 语文 | 90.5 | 179.5 | 89.75 | 89 | 90.5 3 | 1 | 语文 | 99 | 199 | 99.5 | 99 | 100 4 | 1 | 语文 | 100 | 199 | 99.5 | 99 | 100 5 | 2 | 数学 | 12 | 12 | 12 | 12 | 12 3 | 2 | 数学 | 78 | 159 | 79.5 | 78 | 81 1 | 2 | 数学 | 81 | 81 | 81 | 81 | 81 4 | 2 | 数学 | 90 | 186 | 93 | 90 | 96 2 | 2 | 数学 | 96 | 292.5 | 97.5 | 96 | 99.5 6 | 2 | 数学 | 97 | 292.5 | 97.5 | 96 | 99.5 7 | 2 | 数学 | 99.5 | 99.5 | 99.5 | 99.5 | 99.5 5 | 3 | 英语 | 56 | 56 | 56 | 56 | 56 6 | 3 | 英语 | 87 | 180 | 90 | 87 | 93 7 | 3 | 英语 | 93 | 378 | 94.5 | 93 | 95 3 | 3 | 英语 | 95 | 385 | 96.25 | 95 | 100 4 | 3 | 英语 | 95 | 385 | 96.25 | 95 | 100 2 | 3 | 英语 | 95 | 385 | 96.25 | 95 | 100 1 | 3 | 英语 | 100 | 100 | 100 | 100 | 100 1 | 4 | 物理 | 60 | 315 | 63 | 60 | 65 2 | 4 | 物理 | 60 | 315 | 63 | 60 | 65 3 | 4 | 物理 | 65 | 337 | 67.4 | 65 | 71 4 | 4 | 物理 | 65 | 337 | 67.4 | 65 | 71 5 | 4 | 物理 | 65 | 337 | 67.4 | 65 | 71 6 | 4 | 物理 | 71 | 142 | 71 | 71 | 71 7 | 4 | 物理 | 71 | 142 | 71 | 71 | 71 (28 rows)
