Understand window aggregation in continuous queries

To request support or provide feedback for this feature, send an email to bq-continuous-queries-feedback@google.com.

BigQuery continuous queries support aggregations and windowing as stateful operations. Stateful operations let continuous queries perform complex analysis that requires retaining information across multiple rows or time intervals. This capability lets you calculate metrics over time—such as a 30-minute average—by storing necessary data in memory while the query runs.

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:

Unsupported aggregation functions

The following aggregation functions are unsupported:

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:

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