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.

S-Man · Accepted Answer · 2019-08-06 11:27:45Z

4

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

edited Aug 6, 2019 at 11:27

answered Aug 6, 2019 at 11:22

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zon Over a year ago

Thanks. Unfortunately the project is on Postgres 9.4.

S-Man Over a year ago

Is the number of records fixed to 12 each year? In this case we can change RANGE BETWEEN interval into ROWS BETWEEN 11 PRECEDING AND CURRENT ROW

Zon Over a year ago

Shift to 12 month is irrelative to year. It is relative the current row. But there's no RANGE BETWEEN in PostreSQL 9.4. The stackoverflow.com/users/9661424/sticky-bit answer works at 9.4.

S-Man Over a year ago

Yes, the RANGE feature does not exist, but the ROWS feature. ;) dbfiddle.uk/… But this only works if there exactly 12 records per year ;) But nevermind, the other solution is better anyway on yout case

sticky bit · Accepted Answer · 2019-08-06 11:46:47Z

2

You can use a correlated subquery.

SELECT t1.*,
       (SELECT avg(t2.value)
               FROM my_table t2
               WHERE t2.region_id = t1.region_id
                     AND t2.my_date <= t1.my_date
                     AND t2.my_date > t1.my_date - '1 year'::interval)
       FROM my_table t1;

answered Aug 6, 2019 at 11:46

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

1 Comment

Zon Over a year ago

Works fine in version 9.4. Thank you.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related