CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsamazon-redshift
martina.physics
martina.physics

Reputation: 9804

SQL - Rolling avg over truncated date

I want to do a rolling mean of a calculated field on a week basis out of data whose precision is at the second. This is why I first truncate the date to the week.

So my provisional query is 

SELECT week, AVG(my_value) OVER(ORDER BY week ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg_my_value 
FROM
    (SELECT id, 
           DATE_TRUNC('week', created_at) AS week,
           my_value
    FROM my_table
    ORDER BY week ASC
    ) 
GROUP BY week

The problem I have is that the AVG works but it's done separately for all rows which have got the same week! I think this is because there must be some sort of inner grouping added but the problem I have is to conceive it for the case of an average.

If that counts, I am looking for a solution working for Redshift, or PostgreSQL.

Upvotes: 0

Views: 376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

If you want a cumulative average, then:

SELECT week,
       AVG(AVG(my_value)) OVER (ORDER BY week ASC) AS avg_my_value 
FROM (SELECT id,  DATE_TRUNC('week', created_at) AS week, my_value
      FROM my_table
     ) t
GROUP BY week;

Notes:

  • The ORDER BY in the subquery is superfluous.
  • Note the nesting of the aggregation functions.

Upvotes: 1

Related Questions