How to accumulate for previous 7 days value in BigQuery?

user_15379556 0
Viewed 267

In BigQuery SQL, how can I accumulate values for some period?

I have a table like this. And I want to get sum of previous 7 days value for each row.

Please let me know a query for this.

Date Value
2021-05-10 3
2021-05-11 1
2021-05-14 2
2021-05-15 0
2021-05-16 3
2021-05-17 1
2021-05-18 1
2021-05-20 1
2021-05-23 1
2021-05-28 1
... ...

Expected

Date Value acc_7
2021-05-10 3 3 (Sum of 05-04 ~ 05-10)
2021-05-11 1 4 (Sum of 05-05 ~ 05-11)
2021-05-14 2 6 (Sum of 05-08 ~ 05-14)
2021-05-15 0 6 (Sum of 05-09 ~ 05-15)
2021-05-16 3 9 (Sum of 05-10 ~ 05-16)
2021-05-17 1 7 (Sum of 05-11 ~ 05-17)
2021-05-18 1 7 (Sum of 05-12 ~ 05-18)
2021-05-20 1 8 (Sum of 05-14 ~ 05-20)
2021-05-23 1 4 (Sum of 05-17 ~ 05-23)
2021-05-28 1 2 (Sum of 05-22 ~ 05-28)
... ... ...
google-bigquery sql
1 Answers

You can use a range window frame, but you need to convert the date to a number of days.

select t.*,
       sum(value) over (order by unix_date(date)
                        range between 6 preceding and current row
                       ) as value_7
from t;
user_11440350
Related