Defining window function frame for moving average

user_14177875 0
Viewed 248

I am trying to create a moving average with a window function. I would like it to sum the count from the previous 7 days. I have one column for date, one column for hour, and there is a count for every date - hour row.

For example, for 8/18, I'd like to sum the count of all previous rows (8/11 through 8/17). It's a bit tricky given each date is fanned out by hour. Then all the 8/18 rows will have the same sum value of the previous 7 days.

I tried defining a frame using groups or range in redshift but it seems to only like rows.

What I have so far just sums the count for the group with the same date. How can I adjust this or is there another way?

sum(count) over (partition by date)

date       hour  count last7day
2020-08-11  0      1     
2020-08-11  1      5
2020-08-11  2      8
.
.
.
2020-08-11  23     4 
.
.
.
2020-08-17  23     7
2020-08-18  0.          sum of all rows from 2020-08-11 hour 0 through 2020-08-17 hour 23
2020-08-18  1
2020-08-18  2
amazon-redshift window-functions postgresql sql
1 Answers

You can try this.

If rolling average is required for dates till 1 day prior -

  Sum(count) OVER (order by date RANGE BETWEEN '7 days' PRECEDING AND '1 day' PRECEDING)

If rolling average is required for dates till prior row-

Sum(count) OVER (order by date RANGE BETWEEN '7 days' PRECEDING AND 1 PRECEDING)
user_142366220
Related