Moving trailing week average in PostgreSQL

Question

My source data includes Transaction ID, Date, Amount. I need a one week trailing average which moves on a daily basis and averaging amount per transaction. Problem is, that sometimes there is no transactions in particuliar date, and I need avg per transaction, no per day, and trailing average moves by day, not by week.In this particular case I can't use OVER with rows preceding. I'm stack with it :(

Data looks like this: https://gist.github.com/avitominoz/a252e9f1ab3b1d02aa700252839428dd

Please edit your question and show the query that you have. Also, you should put the data structure directly in the question as text, not through an image. — Gordon Linoff, Apr 05 '16 at 12:08

score 0 · Answer 1 · answered Apr 05 '16 at 12:12

There are two methods to doing this. One uses generate_series() to get all the results. The second uses a lateral join.

with minmax as (
      select min(trade_date) as mintd, max(trade_date) as maxtd
      from sales
     )
select days.dte, s.values,
       avg(values) over (order by days.dte
                         rows between 6 preceding and current row
                        ) as avg_7day
from generate_series(mintd, maxtd, interval '1 day') days(dte) left join
     sales s
     on s.trade_dte = days.dte;

Note: this ignores the values on missing days rather than treating them as 0. If you want 0, then use avg(coalesce(values, 0)).

Moving trailing week average in PostgreSQL

1 Answers1