Skip to content
Advertisement

BigQuery SQL for 28-day sliding window aggregate (without writing 28 lines of SQL)

I’m trying to compute a 28 day moving sum in BigQuery using the LAG function.

The top answer to this question

Bigquery SQL for sliding window aggregate

from Felipe Hoffa indicates that that you can use the LAG function. An example of this would be:

Is there a way to do this without having to write out 28 lines of SQL!

Advertisement

Answer

The BigQuery documentation doesn’t do a good job of explaining the complexity of window functions that the tool supports because it doesn’t specify what expressions can appear after ROWS or RANGE. It actually supports the SQL 2003 standard for window functions, which you can find documented other places on the web, such as here.

That means you can get the effect you want with a single window function. The range is 27 because it’s how many rows before the current one to include in the sum.

A RANGE bound can also be extremely useful. If your table was missing dates for some user, then 27 PRECEDING rows would go back more than 27 days, but RANGE will produce a window based on the date values themselves. In the following query, the date field is a BigQuery TIMESTAMP and the range is specified in microseconds. I’d advise that whenever you do date math like this in BigQuery, you test it thoroughly to make sure it’s giving you the expected answer.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement