CREATE WINDOWwindow_identifier
(window_specification
);
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 ONclose_expr
EMIT ONemit_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.
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 ... FROMstream_identifier
'['{window_identifier
|window_specification
}']' ...
In a tuple join statement, the FROM clause includes two stream_identifier
, window specifications. For example,
SELECT ... FROMstream_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.