Specify either a maximum number of records to retain in a window, or a length of time to retain them.
KEEP { count_policy | time_policy } | ALL;
| count_policy | Specify the maximum number of records that will be retained in the window as either a simple maximum nn ROWS or a maximum with some slack nn ROWS SLACK mm. A larger slack value improves performance by reducing the need to delete one row every time a row is inserted. The number of rows, nn, and the slack value, mm, can be either an integer value or an expression. |
| time_policy | Specify the length of time that records will be retained in the window as described in the Intervals topic. |
| ALL | Specifies that all of the rows received will be retained. |
The KEEP clause defines a retention policy for a Named or Unnamed Window. Window retention policies include time-based policies (the time duration for which a window retains rows) and count-based policies (the maximum number of rows that the window can retain). If you omit the KEEP clause from a window definition, the default policy is KEEP ALL.
When specifying a count-based retention policy, you can specify a SLACK value to enhance performance by requiring less frequent cleaning of memory stores. Slack cannot be specified for windows using time-based retention policies.
The location of the KEEP clause in the CREATE WINDOW statement determines whether a named or an unnamed window is created. When the KEEP clause is specified for the window being created, a Named Window is created. If there is a KEEP clause in the query portion of the statement, however, an Unnamed Window is implicitly created. This includes cases where there is a KEEP clause attached to the FROM clause of the query.
In the following example, the first KEEP clause specifies the retention policy for the named window RecentAvgVols, keeping records for ten minutes. The second instance of the KEEP clause creates an unnamed window that stores the average volume over the last five minutes.
CREATE WINDOW RecentAvgVols PRIMARY KEY DEDUCED KEEP 10 MINS AS SELECT Trades.Symbol, Avg(Trades.Volume) as Vol FROM Trades KEEP 5 MINS GROUP BY Trades.Symbol;
This example creates a Sliding Window that retains the most recent 100 records that match the filter condition. The SLACK value means the window may contain as many as 110 records.
CREATE WINDOW Last100Trades PRIMARY KEY DEDUCED KEEP 100 ROWS SLACK 10 AS SELECT * FROM Trades WHERE Trades.Volume > 1000;