BigQuery Window Functions
A focused guide to window functions in BigQuery — cumulative sums, rolling averages, ranking functions, named windows, RANGE frames, and more.
- Using LAST_VALUE with STRUCTS
Learn how to correctly use LAST_VALUE with STRUCTs in SQL and handle non-null structures with practical examples and solutions
- Beware of ROW_NUMBER without ORDER BY
Avoid using ROW_NUMBER() without ORDER BY to prevent random changes in reports. Understand its pitfalls and ensure data accuracy
- Transforming cumulative sums into monthly values
Reverse cumulative sums to monthly values using BigQuery, focusing on fiscal year, lag function, and grain considerations
- Choosing the Right Ranking Function: Why Ties in SQL Matter
Discover the importance of choosing the right ranking function in SQL and how handling ties can impact your data analysis
- Controlling ordering of NULL values in the ORDER BY clause
Learn how BigQuery sorts NULL values by default in ORDER BY, and how to override that behavior with NULLS FIRST and NULLS LAST.
- Rolling period calculation in BigQuery
Compute a rolling period sum in BigQuery using RANGE BETWEEN with UNIX_DATE. Correctly handles duplicate dates and gaps where ROWS would produce wrong results.
- Using RANGE in Window Functions in BigQuery
ROWS counts physical rows from the current row; RANGE includes all peers with the same ORDER BY value. Learn when each frame clause changes your window results.
- Computing a cumulative sum in BigQuery
Learn how to compute a running cumulative sum in BigQuery using SUM with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in a window function.
- Filling up missing values with LAST_VALUE
Learn how to forward-fill missing sensor readings in BigQuery using LAST_VALUE with IGNORE NULLS and an unbounded window frame.
- De-duplicating with ROW_NUMBER vs ARRAY_AGG
Compares ARRAY_AGG and ROW_NUMBER for deduplication in BigQuery with real slot time benchmarks. ARRAY_AGG consumed only 40% of the slot time, making it a...
- Comparing ranking functions in BigQuery
Breaks down the difference between ROW_NUMBER, RANK, and DENSE_RANK in BigQuery with a clear side-by-side example.
- Tidying up WINDOW functions in BigQuery with named windows
Learn how to use named window declarations in BigQuery to avoid repeating PARTITION BY and ORDER BY clauses across multiple window functions.
- Combining STRUCTs with Window Functions in BigQuery
Reduce repeated LEAD and LAG window function calls in BigQuery by wrapping multiple attributes into a STRUCT, then applying a single window function to...