Console
    Start free

    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
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: