1

I have a timeseries dataset that looks like this:

uid | geom     | date       | count |
-------------------------------------
1   | FeatureA | 2016-02-01 | 1     | 
2   | FeatureA | 2016-02-02 | 2     | 
3   | FeatureA | 2016-02-03 | 3     | 
4   | FeatureA | 2016-02-04 | 4     | 
5   | FeatureA | 2016-02-05 | 5     | 
6   | FeatureA | 2016-02-06 | 9     | 
7   | FeatureA | 2016-02-07 | 11    | 
8   | FeatureA | 2016-02-08 | 15    | 
9   | FeatureA | 2016-02-09 | 17    | 
10  | FeatureA | 2016-02-10 | 20    | 
11  | FeatureB | 2016-02-01 | 2     | 
12  | FeatureB | 2016-02-02 | 2     | 
13  | FeatureB | 2016-02-03 | 8     | 
14  | FeatureB | 2016-02-04 | 4     | 
15  | FeatureB | 2016-02-05 | 5     | 
16  | FeatureB | 2016-02-06 | 15    | 
17  | FeatureB | 2016-02-07 | 11    | 
18  | FeatureB | 2016-02-08 | 15    | 
19  | FeatureB | 2016-02-09 | 19    | 
20  | FeatureB | 2016-02-10 | 25    | 

I would like to calculate the 7 day rolling average for each feature in the dataset (~2000 features). To calculate a 7 day rolling average in Postgres windows could be used as described here.

The following code almost works:

SELECT geom,date,count,  
       AVG(count)
            OVER(PARTITION BY geom ORDER BY geom, date ROWS BETWEEN CURRENT ROW AND 7 Following) AS rolling_avg_count
FROM features;

This gives the following output:

 geom    | date       | count | rolling_avg_count
--------------------------------------------------
FeatureA | 2016-02-01 | 1     | 6.25
FeatureA | 2016-02-02 | 2     | 8.25
FeatureA | 2016-02-03 | 3     | 10.5
FeatureA | 2016-02-04 | 4     | 11.57
FeatureA | 2016-02-05 | 5     | 12.83
FeatureA | 2016-02-06 | 9     | 14.4
FeatureA | 2016-02-07 | 11    | 15.75
FeatureA | 2016-02-08 | 15    | 17.33
FeatureA | 2016-02-09 | 17    | 18.5
FeatureA | 2016-02-10 | 20    | 20
FeatureB | 2016-02-01 | 2     | 7.75
FeatureB | 2016-02-02 | 2     | 9.875
FeatureB | 2016-02-03 | 8     | 12.75
FeatureB | 2016-02-04 | 4     | 13.43
FeatureB | 2016-02-05 | 5     | 15
FeatureB | 2016-02-06 | 15    | 17
FeatureB | 2016-02-07 | 11    | 17.5
FeatureB | 2016-02-08 | 15    | 19.67
FeatureB | 2016-02-10 | 25    | 25

However, the output continues calculating averages up to the end of the partition. For example, uid 10 would have a rolling average of 20 (calculated using only one record). I'd like the calculation to stop when there a fewer than 7 following rows.

Ideally the output would look something like this:

 geom    | date       | count | rolling_avg_count
--------------------------------------------------
FeatureA | 2016-02-01 | 1     | 6.25
FeatureA | 2016-02-02 | 2     | 8.25
FeatureA | 2016-02-03 | 3     | 10.5
FeatureA | 2016-02-04 | 4     | 11.57
FeatureA | 2016-02-05 | 5     | 
FeatureA | 2016-02-06 | 9     | 
FeatureA | 2016-02-07 | 11    | 
FeatureA | 2016-02-08 | 15    | 
FeatureA | 2016-02-09 | 17    | 
FeatureA | 2016-02-10 | 20    | 
FeatureB | 2016-02-01 | 2     | 7.75
FeatureB | 2016-02-02 | 2     | 9.875
FeatureB | 2016-02-03 | 8     | 12.75
FeatureB | 2016-02-04 | 4     | 13.43
FeatureB | 2016-02-05 | 5     | 
FeatureB | 2016-02-06 | 15    | 
FeatureB | 2016-02-07 | 11    | 
FeatureB | 2016-02-08 | 15    | 
FeatureB | 2016-02-10 | 25    | 
0
1

This produces the desired result - after adjusting off-by-1 errors ②:

SELECT geom, date, count
     , CASE WHEN rn < 7 THEN NULL  -- ②
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , AVG(count) OVER (PARTITION BY geom
                           ORDER BY date -- ①
                           ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING -- ②
                          ) AS rolling_avg_count
        , row_number() OVER (PARTITION BY geom ORDER BY date DESC) AS rn
   FROM   features
   ) sub;

db<>fiddle here

① With PARTITION BY geom, you don't need geom in ORDER BY.

② You had off-by-one error(s):

I would like to calculate the 7 day rolling average

But you show a calculation for the 8 day rolling average (1 + 7).

I'd like the calculation to stop when there a fewer than 7 following rows.

But you show a result for only 6 following rows (displayed values 11.57 and 13.43).

I made it an actual 7 day average, stopping when there are fewer than 6 following rows.

Performance optimization

The above adds an additional sort to the query plan, due to the descending sort order in the second window function.

This alternative query avoids that with an additional count():

SELECT geom, date, count
     , CASE WHEN rn < 6 THEN NULL -- ③
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , avg(count)   OVER (PARTITION BY geom
                             ORDER BY date
                             ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING
                            ) AS rolling_avg_count
        , count(*)     OVER (PARTITION BY geom)
        - row_number() OVER (PARTITION BY geom ORDER BY date) AS rn
   FROM   features
   ) sub;

db<>fiddle here

A bit more verbose, adding an additional window function. But I expect it to perform better.

③ Adjusted to 0-based row number. (Cheaper than adding + 1.)

2
  • @Oliver: Can you confirm that the 2nd variant is, in fact, faster? May 22 '20 at 0:12
  • 1
    Thanks Erwin. The first one took 1148.282 ms and the second 999.499 ms. May 22 '20 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.