CODEWITHSUNDEEP
sqlamazon-redshift
Hawtin
Hawtin

Reputation: 121

Redshift - How to SUM number over last 4 weeks as a window function per row?

is it possible to SUM a number over a special time period in Amazon Redshift with a WINDOW-Function?

As an example I'm counting login numbers for different companies per day. What I now want per row is, that it sums up the logins over the last 4 weeks (referenced by the date of the row): The field which I'm serarching for is marked yellow in the screenshot.

enter image description here

Thanks in advance for your help.

Upvotes: 0

Views: 2152

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If you have data for each day, then you can use rows:

select t.*,
       sum(logs) over (partition by company
                       order by date
                       rows between 27 preceding and current row
                      ) as logins_4_weeks
from t;

Redshift does not yet support range for the window frame, so this is your best bet.

Upvotes: 2

Related Questions