CREATE WINDOW Statement

Syntax 

CREATE WINDOW window_identifier (
  window_specification
);

Substitutable Fields

window_identifier

A unique name for the window declaration.

window_specification

A description of the window in the format: 

  SIZE size ADVANCE increment
  {TIME | TUPLES | ON field_identifier_w
   PREDICATE OPEN ON open_expr CLOSE ON close_expr EMIT ON emit_expr}
  [PARTIAL {TRUE | FALSE}]
  [PARTITION BY field_identifier_p[,...]]
  [VALID ALWAYS]
  [OFFSET offset]
  [TIMEOUT timeout]
size

The size of the window, expressed as either the number of tuples, an interval of time, or a range of values within a tuple field.

increment

The amount each subsequent window is offset from the first window that opens on a stream, or the literal null.

field_identifier_w

The tuple field used to set window size and advance.

open_xpr

Predicate governing opening a window.

close_expr

Predicate governing closing a window.

emit_expr

Predicate governing partial window emissions.

field_identifier_p

The tuple field to use to create windows for separate groupings of tuples.

offset

Indicates when to open the first window on a stream.

timeout

Indicates the amount of time (in seconds) after which a window should close regardless of whether a tuple has arrived.

Discussion

Since a stream is a potentially unlimited source of input data, some mechanism must exist to subdivide a stream into analyzable segments. A window specification describes how a stream of tuples will be subdivided prior to analysis through an aggregate stream query or used within a tuple join statement.

In a StreamSQL application, windows are used within an aggregate stream or tuple join statement. The window specification may be entered as a separate statement or included within the aggregate stream or tuple join statement. The advantage of writing the specification as a separate statement is that the definition may be reused in multiple aggregate stream or tuple join statements. A window specification embedded within an aggregate stream or tuple join statement is only available to that statement.

Note that the square braces are a required part of the window usage syntax (indicated by the single quotation marks). A window specification immediately follows the identifier for the stream on which it is applied. For example, in an aggregate query statement 

SELECT ... FROM stream_identifier '['{window_identifier|window_specification}']' ...

In a tuple join statement, the FROM clause includes two stream_identifier, window specifications. For example, 

SELECT ... FROM stream_identifier_1 '['{window_identifier_1|window_specification_1}']', 
                stream_identifier_2 '['{window_identifier_2|window_specification_2}']' ...

The CREATE WINDOW statement, and the FROM clause modification, are StreamSQL extensions to SQL.

The only required entries are SIZE, ADVANCE, and one of the choices TIME | TUPLES | ON field_identifier.

SIZE sets the size of the window to either a fixed interval of time, a fixed number of tuples, or a specified range of values within one of the input tuple's fields. When the window size specification has been met, the window closes.

ADVANCE is the amount each subsequent window is offset from the first window that opens on a stream. ADVANCE is generally a value that is less than, or equal to, SIZE. ADVANCE takes null for its increment argument, which specifies the equivalent of not specifying and Advance setting in the EventFlow Aggregate operator.

TIME | TUPLES | ON field_identifier determines whether the window is based on time, a fixed number of tuples, or a field value.

Using the TIME specification means that window size and advance will be based on the system time on the computer running the StreamBase application. The values following the SIZE and ADVANCE entries are interpreted as seconds. Using this setting is the equivalent of a time-based Aggregate operator.</>

Using the TUPLE specification means that window size and advance will be based on the number of tuples. The values following the SIZE and ADVANCE entries are interpreted as number of tuples. Using this setting is the equivalent of a tuple-based Aggregate operator.

Using the ON field_identifier specification means that window size and advance will be based on the value of a field in the input tuple. The values following the SIZE and ADVANCE entries are interpreted as field values. Using this setting is the equivalent of a field-based Aggregate operator.

PARTITION BY identifies a field within the input tuple that will be used to group the incoming tuples. For a window defined on tuples, this entry is required. At the present time, this parameter is not used by windows based on time or field values. A field included in the PARTITION BY clause must also be included in the GROUP BY clause.

VALID ALWAYS indicates that a window is valid even when its SIZE restriction has not been reached. If included in a window definition, the window will emit an output tuple as each input tuple arrives. The window will also emit an output tuple when the window closes. The VALID ALWAYS clause may only be used when the values of SIZE and ADVANCE are equivalent.

OFFSET indicates when the first window on a stream is opened.

TIMEOUT indicates when a window closes early. It represents the amount of time after which the window will close regardless of whether a tuple has arrived.

Related Topics

Back to Top ^