Streaming Windowing Table Functions

SQL streaming windowing functions divide rows from a table or view into time-based windows. You use these table functions in the FROM clause of a query.

Each function returns the input columns and two additional columns: WINDOW_START and WINDOW_END. These columns identify the window assigned to each returned row. You can use the returned rows for filtering, grouping, aggregation, and other query operations.

The four functions use different windowing rules:

TUMBLE, HOP, and CUMULATE evaluate each input row independently. SESSIONS evaluates the rows together, in datetime order, to form sessions.

The individual function topics describe the syntax, arguments, examples, and restrictions for each function.

Common Syntax

Specify a SQL streaming windowing table function where you can specify a row source in the FROM clause. The common form is:

function_name (
  table,
  COLUMNS ( datetime_column ),
  interval_argument [, interval_argument ]...
)

The arguments define the windows. For example, TUMBLE takes a window size, while HOP takes a slide and a window size. The function returns the input columns plus WINDOW_START and WINDOW_END. These are result columns, so you do not specify them in the function call.

For example, the following query applies a one-hour TUMBLE window to the event_time column:

SELECT *
FROM TUMBLE (sales, COLUMNS (event_time), INTERVAL '1' HOUR);

Function Arguments

table

The table argument specifies the table, view or query_name (WITH clause) that provides the rows to process. The current user must have the required privileges on the table or view.

If the datetime value requires conversion or other processing, perform the processing in a view or a WITH clause and pass the result to the function.

COLUMNS

The COLUMNS argument specifies a date/time column. The function uses the value in that column to assign each row to a time window.

datetime_column

The datetime_column parameter accepts the name of exactly one datetime column. You cannot specify an expression.

The column can have any of the following data types:

The function does not implicitly convert a character value to a datetime value. Convert the value before you pass the table or view to the function.

interval_argument

The interval_argument specifies the interval values used to define the windows. These values follow COLUMNS(datetime_column) in the function call. The required values depend on the function:

Here, size is the window duration, slide is the time between new HOP windows, step is the amount by which a CUMULATE window grows, offset shifts the window alignment, and gap is the inactivity interval that starts a new session.

Interval Parameters

Each interval parameter must be a non-null runtime constant of one of the following data types:

A runtime constant can be an interval literal, a bind variable, a scalar subquery that returns one row, a correlation variable, or an expression composed of these values.

Values that specify a window size, slide, step, or session gap must be positive. See the description of each function for additional requirements.

Windowing Semantics and Restrictions

Window Boundaries

Each returned row has a WINDOW_START and a WINDOW_END value. Together, they identify the time window assigned to that row. Both columns have the same data type as the datetime column.

A window includes its start boundary and excludes its end boundary:

WINDOW_START <= datetime value < WINDOW_END

For example, assume that one-hour TUMBLE windows begin on the hour:

Datetime value WINDOW_START WINDOW_END
10:15 10:00 11:00
11:00 11:00 12:00

The row at 11:00 belongs to the window that starts at 11:00 because the preceding window ends at, but does not include, 11:00. With HOP or CUMULATE, an input row can appear in more than one window. Each returned row has the boundary values for its window.

If the datetime value is null, the function returns the row with null values for WINDOW_START and WINDOW_END.

Datetime and Calendar Considerations

Restrictions

See Also: