CODEWITHSUNDEEP
google-bigquery
Bilal Alauddin
Bilal Alauddin

Reputation: 91

How can I compute rolling average on BigQuery

I'm facing an issue and I'm not sure how to compute it on Big Query, I have a table where I have the amount of bananas a user has eaten with a date associated when he ate some:

+------+-------+--------------+
| user | banana|    date      |
+------+-------+--------------+
| a    |     5 |  2019/01/18  |
| .    |     ..|              |
| a    |     2 |  2019/05/18  |
| b    |     2 |  2019/02/18  |
| b    |     3 |  2019/03/18  |
| b    |     1 |  2019/03/18  |
+------+-------+--------------+

I would like to get something like this : 

+------+-------------+--------------------+----------------------+
| user | date  T     |AVG1st week before T|AVG2nd   week before T|
+------+-------------+--------------------+----------------------+
| a    | 2019/01/18  |  2                 |           1          |
| .    |     ..      |  ..                |           .          |
| a    | 2019/01/20  |  3                 |           1          |
| b    |     2       |  2                 |          2.5         |
| b    |     3       |  2                 |           5          |
| b    |     1       |  1                 |           2          |
+------+-------------+--------------------+----------------------+

where " AVG 1st week before T" is the average of daily banana consumed during the 7 days before T for a user.

I thought that for each row I would compute both the "7 days before T" variable and create the "14 days before T" variable. Then calculate in a subquery the amount but I think it's not an optimal way to handle this.

if you have any advice I'll be glad to discuss about it!

Thank you in advance,

Upvotes: 5

Views: 4341

Answers (1)

John Dow
John Dow

Reputation: 806

Something worthwhile to read Analytic Functions Concepts in Standard SQL

Here is a snippet that I came up with, at the current row gives the 'average banana eaten' in the last 7 days.

SELECT
  user,
  banana,
  date,
  AVG(banana) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg
FROM
  `banana.banana`
WHERE
  user = 'a'

enter image description here

Upvotes: 4

Related Questions