注：测试数据在 postgres —— 分组集与部分聚集 中
聚集将多行转变成较少、聚集的行。而窗口则不同，它把当前行与分组中的所有行对比，并且返回的行数没有变化。
组合当前行与 production 的均值
SELECT country, year, production,comsumption,
avg(production) over()
from t_oil;
1.分组
组合当前行与 按年份分组后，当前行所在分组的 production 的均值
SELECT country, year, production,comsumption,
avg(production) over(partition by year)
from t_oil;
组合当前行与 按年份>2000 分组后，当前行所在分组的 production 的均值
SELECT country, year, production,comsumption,
avg(production) over(partition by year > 2000)
from t_oil;
2.排序
组合当前行与 按年份排序后, 在当前 country 分组内, 到当前行为止 的 最小 production
SELECT country, year, production,
min(production) over (partition by country order by year)
from t_oil
where year between 1978 and 1983
and country in ('Iran', 'Oman')
注：可以看到，随着行数增加，最小值一直在改变。比如，到第5行为止时，最小值为 1321。
3.滑动窗口
① 移动窗口应与 order by 一起使用。此窗口的范围是当前行，当前行的前一行，当前行的下一行
SELECT country, year, production,
min(production) over
(PARTITION by country ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
from t_oil
WHERE year BETWEEN 1978 AND 1983
AND country IN ('Iran', 'Oman');
② 窗口的范围与上面一样。为上一行，当前行与下一行。注：array_agg 的作用是将范围内的该字段值转变成一个 postgresql 数组
SELECT *, array_agg(id) OVER
(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM generate_series(1, 5) as id;
③ 此窗口的范围是：当前行与当前行之前的行
SELECT *, array_agg(id) OVER
(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM generate_series(1, 5) as id;
④ 此窗口的范围是：当前行的前两行，当前行以及当前行之后的行
SELECT *, array_agg(id) OVER
(ORDER BY id ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING)
FROM generate_series(1, 5) as id;
4.提取窗口子句
组合当前行，按年份排序后 到当前行为止 最小与最大的产量
SELECT country, year, production, min(production) OVER (w), max(production) OVER (w)
FROM t_oil
WHERE country = 'Canada' AND year BETWEEN 1980 AND 1985
WINDOW w AS (ORDER BY year);
5.使用内建窗口函数
四大排名函数
-- rank
SELECT year, production, rank() over (order by production)
from t_oil
where country = 'Other Middle East'
order by rank
limit 7; -- dense_rank
SELECT year, production, dense_rank() over (order by production)
from t_oil
where country = 'Other Middle East'
order by dense_rank
limit 7; -- ntile
SELECT year, production, ntile(4) OVER (order by production)
from t_oil
where country = 'Iraq'
and year between 2000 and 2006; -- row_number
select country, production, row_number() over (order by production)
from t_oil
limit 3;
233