Mastering PostgreSQL 9.6

Using sliding windows

So far the window we have used inside our query has been static. However, for calculations such as a moving average, this is not enough. A moving average needs a sliding window, which moves along as data is processed.

Here is an example of how a moving average can be achieved:

test=# 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');  country | year | production | min   ---------+------+------------+------  Iran    | 1978 |       5302 | 3218  Iran    | 1979 |       3218 | 1479  Iran    | 1980 |       1479 | 1321  Iran    | 1981 |       1321 | 1321  Iran    | 1982 |       2397 | 1321  Iran | 1983 | 2454 ...