Window functions in BigQuery let you compute values across a set of rows related to the current row without collapsing them into a single output row, unlike aggregate functions with GROUP BY. They are essential for running totals, moving averages, rankings, and lag/lead comparisons. This article explains how they work, their syntax, and common use cases.
What Are Window Functions?
Window functions (also called analytic functions) perform calculations over a “window” of rows defined by the OVER() clause. The result is appended to each row, preserving all original rows. For example, a cumulative sum of sales per region keeps every transaction row while adding a running total column. This contrasts with a regular SUM() with GROUP BY, which would return one row per region.
BigQuery supports a wide range of window functions, including ranking functions (RANK, DENSE_RANK, ROW_NUMBER), value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE), and aggregate functions used as window functions (SUM, AVG, MIN, MAX, COUNT).
Window Function Syntax
The basic syntax is:
function_name(expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC|DESC]]
[window_frame]
)
- PARTITION BY divides the result set into groups (partitions) where the function is applied independently.
- ORDER BY defines the order of rows within each partition, which is required for ranking and running totals.
- window_frame specifies the range of rows relative to the current row, e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. If omitted, the default is the entire partition when ORDER BY is present, or the whole partition when ORDER BY is absent.
Example: Calculate a running total of sales per region ordered by date.
SELECT
region,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;
This returns each row with a running total that resets for each region.
Common Use Cases and Examples
Window functions shine in real-world reporting scenarios. Here are three typical examples:
- Ranking products by sales within each category – Use RANK() or DENSE_RANK() with PARTITION BY category.
- Comparing a value to the previous row – Use LAG() to get the prior day’s temperature, then compute the difference.
- Moving average of stock prices – Use AVG() with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day average.
Concrete query for ranking:
SELECT
product_id,
category,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rank_in_category
FROM product_sales;
This assigns rank 1 to the top-selling product in each category.
Choosing the Right Window Function
Selecting the correct function depends on the question you’re answering. Use this guide:
- ROW_NUMBER() – Assigns a unique sequential number to each row, regardless of ties. Use for deduplication or pagination.
- RANK() and DENSE_RANK() – For competition-style rankings. RANK leaves gaps after ties (1,2,2,4), DENSE_RANK does not (1,2,2,3).
- LAG() and LEAD() – To access previous or next row values, e.g., day-over-day change.
- SUM/AVG with ORDER BY – For running totals or moving averages.
- FIRST_VALUE/LAST_VALUE – To get the first or last value in a window, e.g., the first purchase date per customer.
Always test with a small dataset to confirm the window frame behaves as expected, especially when ORDER BY is used.
Common Pitfalls and Misconceptions
A frequent mistake is forgetting that ORDER BY in the OVER clause affects the window frame. For example, without a frame clause, SUM(amount) OVER (ORDER BY date) computes a running total, not a global sum. Another pitfall is using DISTINCT inside window functions; BigQuery does not allow DISTINCT in aggregate window functions. Also, window functions cannot be used in the WHERE clause directly; you must wrap them in a subquery or CTE.
Misconception: Window functions are slow. In BigQuery, they are optimized and often faster than self-joins for the same task. However, large partitions with many rows can increase computation time; using appropriate partitioning and ordering helps.
Performance and Cost Considerations
BigQuery charges based on the amount of data scanned, not on computation time. Window functions do not add extra scanning beyond the base query, so they are cost-neutral in that sense. However, they can increase query complexity and memory usage. To optimize:
- Filter data early with WHERE to reduce the number of rows processed.
- Use PARTITION BY on columns that are also used in filters or joins to minimize data shuffle.
- When using ORDER BY, ensure the column is clustered or partitioned in the table to speed up sorting.
For very large datasets, consider pre-aggregating with GROUP BY if you don’t need row-level detail.
Window Functions vs. GROUP BY
The key difference is that GROUP BY collapses rows, while window functions preserve them. Use GROUP BY when you need a single summary per group (e.g., total sales per region). Use window functions when you need to keep the detail rows and also show a computed value alongside, like a running total or a rank. Sometimes you combine both: first aggregate with GROUP BY, then apply a window function on the aggregated result.
Example: Get the top 3 regions by total sales, showing the rank.
WITH region_totals AS (
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
)
SELECT region, total,
RANK() OVER (ORDER BY total DESC) AS rank
FROM region_totals
QUALIFY rank <= 3;
Here, QUALIFY is a BigQuery clause that filters window function results.
Frequently Asked Questions
Can I use window functions in the WHERE clause? No, window functions are evaluated after the WHERE clause. To filter based on a window function result, use a subquery or the QUALIFY clause (BigQuery-specific).
What is the difference between RANK and DENSE_RANK? RANK leaves gaps in the ranking sequence when there are ties (e.g., 1,2,2,4), while DENSE_RANK does not (1,2,2,3). Use DENSE_RANK when you want consecutive ranks.
How do I calculate a moving average in BigQuery? Use AVG() with a window frame: AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for a 7-day average.
Can I use multiple window functions in one query? Yes, you can include multiple window functions in the SELECT list, each with its own OVER clause.
Does BigQuery support window functions in Google Sheets? No, window functions are a BigQuery SQL feature. Google Sheets has different functions like ARRAYFORMULA and QUERY, which are not equivalent.
Are window functions supported in standard SQL only? Yes, window functions are part of BigQuery's standard SQL. Legacy SQL has limited support; it's recommended to use standard SQL.
Related
- F.08 Export to Excel: How to Do It
- Extracting Numbers from Text in Excel
- Export Import Excel Revit: How to Move Data Both Ways
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH in Reverse Order: Find the Last MatchExcel’s MATCH function does not have a setting to search from bottom to top. To find the last…
- How to Filter in Excel Without a Mouse: Keyboard GuideTo filter a list in Excel for Microsoft 365 on Windows without a mouse, select a cell in…
- Excel Formula for Calendar Days in a MonthThe simplest Excel formula for calendar days in a month is =DAY(EOMONTH(A1,0)), where A1 contains any date within…
- How to Copy a Formula in Excel to Other Cells SafelyTo copy a formula in Excel, select the cell that contains it, then drag its fill handle into…
- Excel INDEX and MATCH Lookup: How to Use ItThe short answer: INDEX and MATCH is a pair of Excel functions that together perform a two-way lookup,…
- Excel Custom Number Format: Round Values to ThousandsTo display Excel values rounded to the nearest thousand without changing the stored numbers, apply the custom number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.