PostgreSQL 10 - 窗口函数_postgre min over order by rows between

聚合遵循一个简单的原理：接受很多行，转换成较少的行。窗口函数不一样，它比较当前行和同组内的其他行。返回的行的数量不变。
先看一个例子：

postgres=# SELECT avg(production) FROM t_oil;
          avg          
-----------------------
 2607.5139860139860140
(1 行记录)

postgres=# SELECT country, year, production,
postgres-# consumption, avg(production) OVER ()
postgres-# FROM t_oil LIMIT 4;
 country | year | production | consumption |          avg          
---------+------+------------+-------------+-----------------------
 USA     | 1965 |       9014 |       11522 | 2607.5139860139860140
 USA     | 1966 |       9579 |       12100 | 2607.5139860139860140
 USA     | 1967 |      10219 |       12567 | 2607.5139860139860140
 USA     | 1968 |      10600 |       13405 | 2607.5139860139860140
(4 行记录)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

我们数据集的平均产量大约是每天2600万桶。查询目标是把他们加成一列。这样，可以很容易地比较当前行的值和平均值。
必须有OVER，否则PostgreSQL不能处理该查询：

postgres=# SELECT country, year, production,
consumption, avg(production) FROM t_oil;
错误:  字段 "t_oil.country" 必须出现在 GROUP BY 子句中或者在聚合函数中使用
第1行SELECT country, year, production,
            ^
1
2
3
4
5

分区数据

也可以使用子查询得到上面的结果。但是，如果你想要的不只是整体平均值，子查询会让你的查询变成噩梦。
假设，你还想要国家的平均值，就需要PARTITION BY：

postgres=# SELECT country, year, production, consumption,
postgres-# avg(production) OVER (PARTITION BY country)
postgres-# FROM t_oil;
 country | year | production | consumption |          avg          
---------+------+------------+-------------+-----------------------
 Canada  | 1965 |        920 |        1108 | 2123.2173913043478261
 Canada  | 1966 |       1012 |        1167 | 2123.2173913043478261
 Canada  | 1967 |       1106 |        1246 | 2123.2173913043478261
 ...
 Iran    | 1965 |       1908 |         134 | 3631.6956521739130435
 Iran    | 1966 |       2132 |         148 | 3631.6956521739130435
 Iran    | 1967 |       2603 |         165 | 3631.6956521739130435
 ...
1
2
3
4
5
6
7
8
9
10
11
12
13

这样，每个国家都有了平均值。OVER定义要查看的窗口。本例的窗口是行所属的国家。本查询返回某行和所属国家的全部行的计算结果。
基本上，PARTITION BY接受任何表达式。通常，使用一列做分区：

postgres=# SELECT year, production,
avg(production) OVER (PARTITION BY year < 1990)
FROM t_oil
WHERE country = 'Canada' ORDER BY year;
 year | production |          avg          
------+------------+-----------------------
 1965 |        920 | 1631.6000000000000000
 1966 |       1012 | 1631.6000000000000000
 1967 |       1106 | 1631.6000000000000000
 ...
1
2
3
4
5
6
7
8
9
10

上面的例子，数据被分割。表达式year < 1990返回两个值：true或者false。所以，根据年，分为1990年前的平均值和之后的平均值。

窗口内排序

OVER中不是只能有PARTITION BY，还可以在窗口内排序-可以使用ORDER BY向聚合函数提供数据：

postgres=# SELECT country, year, production,
postgres-# min(production) OVER (PARTITION BY country ORDER BY year)
postgres-# FROM t_oil
postgres-# WHERE year BETWEEN 1979 AND 1981 AND country IN ('Iran', '
1
2
3