Create Window

The CREATE WINDOW command is used to create a WINDOW in RioDB.

Access Rule

Requires WINDOW, STREAM or ADMIN role.

Syntax

The window definition consists of the window name, running functions, the data source, optional conditions, optional partitioning, and the range.

CREATE WINDOW <window name - required>
RUNNING <aggregate functions, comma-separated - required>
FROM <a value that will populate the window - required>
WHEN <conditions - optional>
PARTITION BY <if grouping by a field value - optional>
   EXPIRE <time to drop stale partitions - optional>
RANGE <the time span or quantity span of the window - required>
;  

WINDOW NAME

Only letters, numbers and underscore allowed. Should not be a reserved word (like RioDB terms or math functions).

RUNNING

List the aggregations that the window should calculate, such as avg, count, count_distinct, sum, first, last, max, median, min, mode, stddev_pop, variance_pop, previous, stddev, variance, and slope.

FROM

The value that will be entering the window. It can only be ONE field from the stream. For example, if your stream messages contain fields motor_rpm, motor_temperature, motor_fuel_flow, you can create a window for the motor_rpm durinig the past minute, and another window for motor_temperature during the last minute. You cannot have a single window handle two fields. It’s one window per field. The value chosen can be a field straight out of the stream message fields, but it can also be the result of an expression.

WHEN

Conditions to filter only messages that are wanted in the window. SQL standards use the word “WHERE”. But RioDB uses the word “WHEN” to emphasize that it’s not looking for data in a file “where” conditions are met. Instead, it’s continually listening on a data stream for “when” conditions are met.

PARTITION BY

Optional. For example, suppose you want to track user website click statistics (like COUNT) for each user session separately. Instead of creating a window for each user session_id, you can create a single window that is partitioned by user session_id. Partitioning allows you to dynamically run n windows in parallel for n keys while defining the window only once.

EXPIRE

Indicates the time when a partitioned key is stale long enough to be removed. For example, you are tracking statistics for each individual session_id on a web server. If a session_id has not posted a single message in a long while, you could drop the window for that session_id key to save RAM. Using EXPIRE 10m will drop a session_id after 10 minutes of inactivity on that key.

RANGE

An integer value will set the window as a window of quantity (same as the integer value). A number followed by s, m, h, or d will set the window as a window of time. RANGE 30s means a window of 30 seconds. 30m means 30 minutes. 30h means 30 hours. And 30d means 30 days. That will be the time span of the window.

Examples

See:
   Fixed-Size Windows
   Time-based Windows
   From Expression
   Conditional Windows
   Partitioned Windows