Get Average for a Date Range Relative to Each Row Using Postgres Window Functions

Question

PROBLEM Need to calculate average value over last 12 months for each row, using PostgreSQL 9.4 window functions (without GROUP BY).

Example:

MY_DATE     VALUE   NEW_VALUE  REGION_ID
2016-09-01  11                 1
2016-10-01  22                 1
2016-11-01  33                 1
2016-12-01  44                 1    
2017-01-01  55                 1    
2017-02-01  66                 1    
2017-03-01  77                 1    
2017-04-01  88                 1    
2017-05-01  99                 1    
2017-06-01  11                 1    
2017-07-01  22                 1    
2017-08-01  33      46.75      1
2017-09-01  44      49.5       1
2017-10-01  55      52.25      1

RESEARCH I get the AVG value, but for exact dates only:

-- Works, but for exact dates:

SELECT *, AVG(value) FILTER (
WHERE my_date > '2016-09-01'
  AND my_date < '2017-10-01') OVER (PARTITION BY region_id)
FROM my_table;

-- Precalculating extra field period_start_date = my_date - INTERVAL '12 month' doesn't work as expected:

SELECT *, AVG(value) FILTER (
WHERE my_date > period_start_date
  AND my_date < period_start_date + INTERVAL '12 MONTH') OVER (PARTITION BY region_id)
FROM my_table;

QUESTION How to get the expected Select-result? Several queries allowed, but without procedures.

Answer 1

demo:db<>fiddle

SELECT
    *,
    CASE WHEN my_date >= '2017-08-01' THEN
        AVG(value) OVER (
            PARTITION BY region_id 
            ORDER BY my_date 
            -- 11 preceding months + current one == 12 months
            RANGE BETWEEN interval '11 months' PRECEDING AND CURRENT ROW)
    ELSE NULL END
FROM mytable

PostgreSQL 11 adds the support for RANGE intervals within window functions. This feature is exactly made for problems like this. Further reading