Understand window aggregation in continuous queries | BigQuery | Google Cloud Documentation
Skip to main content Console English Deutsch Español – América Latina Français Indonesia Italiano Português – Brasil עברית 中文 – 简体 中文 – 繁體 日本語 한국어 Sign in BigQuery Start free Home Documentation Data analytics BigQuery Guides Stay organized with collections Save and categorize content based on your preferences. Understand window aggregation in continuous queries BigQuery continuous queries support aggregations and windowing as stateful operations, which let you analyze streaming data across multiple rows or time intervals.
This capability lets you calculate metrics over time—such as a 30-minute average—by storing the necessary state in memory while the continuous query runs. About windowing functions Windowing functions assign data into logical components, or windows, based on system time, which indicates the commit time of the transaction that made the change. In BigQuery, these functions are table-valued functions (TVFs) that return a table that includes all original columns and two additional columns: window_start and window_end .
These columns identify the time interval for each window. For more information about stateful operations, see Supported stateful operations . Windowing TVFs are only supported with BigQuery continuous queries . Windowing TVFs are distinct from window function calls . Supported aggregation functions The following aggregation functions are supported: ANY_VALUE APPROX_COUNT_DISTINCT APPROX_QUANTILES APPROX_TOP_COUNT APPROX_TOP_SUM ARRAY_AGG with the following requirements: A LIMIT clause is required, with a maximum value of 10,000.
An ORDER BY clause is optional. ARRAY_CONCAT_AGG AVG BIT_AND BIT_OR BIT_XOR CORR COUNT COUNTIF COVAR_POP COVAR_SAMP LOGICAL_AND LOGICAL_OR MAX MAX_BY MIN MIN_BY STDDEV STDDEV_POP STDDEV_SAMP STRING_AGG with the following requirements: A LIMIT clause is required, with a maximum value of 100. An ORDER BY clause is optional. SUM VAR_POP VAR_SAMP VARIANCE Unsupported aggregation functions The following aggregation functions are unsupported: AVG (Differential Privacy) COUNT (Differential Privacy) Functions containing DISTINCT expressions.
GROUPING PERCENTILE_CONT (Differential Privacy) ST_CENTROID_AGG ST_EXTENT ST_UNION_AGG SUM (Differential Privacy) The TUMBLE function The TUMBLE function assigns data into non-overlapping time intervals (tumbling windows) of specified size. For example, a 5-minute window groups events into discrete intervals such as [2026-01-01 12:00:00, 2026-01-01 12:05:00) and [2026-01-01 12:05:00, 2026-01-01 12:10:00) . A row with a timestamp value 2026-01-01 12:03:18 is assigned to the first window.
Because these windows are disjoint and don't overlap, every element with a timestamp is assigned to exactly one window. The following diagram shows how the TUMBLE function assigns events into non-overlapping time intervals: You can use this function in real-time event processing to group events by time ranges before you perform any aggregations. Syntax TUMBLE ( TABLE table , "timestamp_column" , window_size ) Definitions table : The BigQuery table name.
This must be a standard BigQuery table wrapped within the APPENDS function. The word TABLE must precede this argument. timestamp_column : A STRING literal that specifies the name of the column in the input table that contains the event time. The values in this column assign each row to a window. The _CHANGE_TIMESTAMP column, which defines the BigQuery system time, is the only supported timestamp_column . User-defined columns aren't supported. window_size : An INTERVAL value that defines the duration of each tumbling window.
Window sizes can be a maximum of 24 hours. For example: INTERVAL 30 SECOND . Output The TUMBLE function returns an output with the following columns: All columns of the input table at the time the query runs. window_start : A TIMESTAMP value that indicates the inclusive start time of the window to which the record belongs. window_end : A TIMESTAMP value that indicates the exclusive end time of the window to which the record belongs.
Output materialization In a BigQuery continuous query, a windowed aggregation doesn't produce output for a specific time interval until BigQuery finalizes or closes that window. This behavior ensures that BigQuery emits the aggregated results only after it processes all relevant data for that window. For example, if you perform a 5-minute TUMBLE window aggregation on a user_clickstream table, the results for the interval [10:15; 10:20) are only emitted after the query processes records with a _CHANGE_TIMESTAMP of 10:20 or later.
At that moment, BigQuery considers the window closed. Additionally, a window opens and begins accumulating data the moment the first record belonging to that specific time range appears. While a window remains open, BigQuery must preserve the intermediate aggregation results. This requires storing the state, which means BigQuery must preserve the intermediate aggregation results. Because this state must remain in active memory until the window closes, using longer window durations or processing high-volume streams leads to higher slot utilization to manage the increased amount of stored context.
For more information, see Pricing considerations . Limitations The TUMBLE function is supported only in BigQuery continuous queries. When starting a continuous query with the TUMBLE function, you can use only the APPENDS function. The CHANGES function isn't supported. The BigQuery system time column defined by _CHANGE_TIMESTAMP is the only supported timestamp_column . User-defined columns aren't supported. Window sizes can be a maximum of 24 hours.
When the TUMBLE windowing function runs, it produces two additional output columns: window_start and window_end . You must include at least one of these columns in the GROUP BY statement within the SELECT statement that performs the window aggregation. When you use the TUMBLE function with continuous query joins, you must follow all continuous query join limitations . Pricing considerations BigQuery continuous queries bill you based on the compute capacity (slots) consumed while the job runs.
This compute-based model also applies to stateful operations like windowing. Because windowing requires BigQuery to store "state" while the query is active, it consumes additional slot resources. In general, the more context or data stored within a window—such as when using longer window durations—the more state BigQuery must preserve. This leads to higher slot utilization. Examples The following query shows you how to query a taxi rides table to get a streaming average number of rides, number of passengers, and average fare per taxi every 30 minutes, and export this data into a table in BigQuery: INSERT INTO `real_time_taxi_streaming.driver_stats` WITH ride_completions AS ( SELECT _CHANGE_TIMESTAMP as bq_changed_ts , CAST ( timestamp AS DATE ) AS ride_date , taxi_id , meter_reading , passenger_count FROM APPENDS ( TABLE `real_time_taxi_streaming.taxirides` , CURRENT_TIMESTAMP () - INTERVAL 10 MINUTE ) WHERE ride_status = 'dropoff' ) SELECT ride_date , window_end , taxi_id , COUNT ( taxi_id ) AS total_rides_per_half_hour , ROUND ( AVG ( meter_reading ), 2 ) AS avg_fare_per_half_hour , SUM ( passenger_count ) AS total_passengers_per_half_hour FROM tumble ( TABLE ride_completions , "bq_changed_ts" , INTERVAL 30 MINUTE ) GROUP BY window_end , ride_date , taxi_id What's next Learn how to perform JOINs, aggregations, and windowing .
Learn more about BigQuery continuous queries . Learn how to join data from multiple streams . Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License , and code samples are licensed under the Apache 2.0 License . For details, see the Google Developers Site Policies . Java is a registered trademark of Oracle and/or its affiliates. Last updated 2026-11-10 UTC.