Event-based windows
Event-based windows let you break time series data into windows that border on significant events within the data. This is especially relevant in financial data where analysis often focuses on specific events as triggers to other activity.
Vertica provides two event-based window functions that are not part of the SQL-99 standard:
-
CONDITIONAL_CHANGE_EVENTassigns an event window number to each row, starting from 0, and increments by 1 when the result of evaluating the argument expression on the current row differs from that on the previous row. This function is similar to the analytic functionROW_NUMBER, which assigns a unique number, sequentially, starting from 1, to each row within a partition. -
CONDITIONAL_TRUE_EVENTassigns an event window number to each row, starting from 0, and increments the number by 1 when the result of the boolean argument expression evaluates true.
Both functions are described in greater detail below.
Example schema
The examples on this page use the following schema:
CREATE TABLE TickStore3 (ts TIMESTAMP, symbol VARCHAR(8), bid FLOAT);
CREATE PROJECTION TickStore3_p (ts, symbol, bid) AS SELECT * FROM TickStore3 ORDER BY ts, symbol, bid UNSEGMENTED ALL NODES;
INSERT INTO TickStore3 VALUES ('2009-01-01 03:00:00', 'XYZ', 10.0);
INSERT INTO TickStore3 VALUES ('2009-01-01 03:00:03', 'XYZ', 11.0);
INSERT INTO TickStore3 VALUES ('2009-01-01 03:00:06', 'XYZ', 10.5);
INSERT INTO TickStore3 VALUES ('2009-01-01 03:00:09', 'XYZ', 11.0);
COMMIT;
Using CONDITIONAL_CHANGE_EVENT
The analytical function CONDITIONAL_CHANGE_EVENT returns a sequence of integers indicating event window numbers, starting from 0. The function increments the event window number when the result of evaluating the function expression on the current row differs from the previous value.
In the following example, the first query returns all records from the TickStore3 table. The second query uses the CONDITIONAL_CHANGE_EVENT function on the bid column. Since each bid row value is different from the previous value, the function increments the window ID from 0 to 3:
SELECT ts, symbol, bidFROM Tickstore3 ORDER BY ts; |
| |
|
==> |
ts | symbol | bid | cce ---------------------+--------+------+----- 2009-01-01 03:00:00 | XYZ | 10 | 0 2009-01-01 03:00:03 | XYZ | 11 | 1 2009-01-01 03:00:06 | XYZ | 10.5 | 2 2009-01-01 03:00:09 | XYZ | 11 | 3 (4 rows) |
The following figure is a graphical illustration of the change in the bid price. Each value is different from its previous one, so the window ID increments for each time slice:
So the window ID starts at 0 and increments at every change in from the previous value.
In this example, the bid price changes from $10 to $11 in the second row, but then stays the same. CONDITIONAL_CHANGE_EVENT increments the event window ID in row 2, but not subsequently:
SELECT ts, symbol, bidFROM Tickstore3 ORDER BY ts; |
| |
|
==> |
ts | symbol | bid | cce ---------------------+--------+------+----- 2009-01-01 03:00:00 | XYZ | 10 | 0 2009-01-01 03:00:03 | XYZ | 11 | 1 2009-01-01 03:00:06 | XYZ | 11 | 1 2009-01-01 03:00:09 | XYZ | 11 | 1 |
The following figure is a graphical illustration of the change in the bid price at 3:00:03 only. The price stays the same at 3:00:06 and 3:00:09, so the window ID remains at 1 for each time slice after the change:
Using CONDITIONAL_TRUE_EVENT
Like CONDITIONAL_CHANGE_EVENT, the analytic function CONDITIONAL_TRUE_EVENT also returns a sequence of integers indicating event window numbers, starting from 0. The two functions differ as follows:
-
CONDITIONAL_TRUE_EVENTincrements the window ID each time its expression evaluates to true. -
CONDITIONAL_CHANGE_EVENTincrements on a comparison expression with the previous value.
In the following example, the first query returns all records from the TickStore3 table. The second query uses CONDITIONAL_TRUE_EVENT to test whether the current bid is greater than a given value (10.6). Each time the expression tests true, the function increments the window ID. The first time the function increments the window ID is on row 2, when the value is 11. The expression tests false for the next row (value is not greater than 10.6), so the function does not increment the event window ID. In the final row, the expression is true for the given condition, and the function increments the window:
SELECT ts, symbol, bidFROM Tickstore3 ORDER BY ts; |
| |
|
==> |
ts | symbol | bid | cte---------------------+--------+------+----- 2009-01-01 03:00:00 | XYZ | 10 | 0 2009-01-01 03:00:03 | XYZ | 11 | 1 2009-01-01 03:00:06 | XYZ | 10.5 | 1 2009-01-01 03:00:09 | XYZ | 11 | 2 |
The following figure is a graphical illustration that shows the bid values and window ID changes. Because the bid value is greater than $10.6 on only the second and fourth time slices (3:00:03 and 3:00:09), the window ID returns <0,1,1,2>:
In the following example, the first query returns all records from the TickStore3 table, ordered by the tickstore values (ts). The second query uses CONDITIONAL_TRUE_EVENT to increment the window ID each time the bid value is greater than 10.6. The first time the function increments the event window ID is on row 2, where the value is 11. The window ID then increments each time after that, because the expression (bid > 10.6) tests true for each time slice:
SELECT ts, symbol, bidFROM Tickstore3 ORDER BY ts; |
| |
|
==> |
ts | symbol | bid | cte ---------------------+--------+------+----- 2009-01-01 03:00:00 | XYZ | 10 | 0 2009-01-01 03:00:03 | XYZ | 11 | 1 2009-01-01 03:00:06 | XYZ | 11 | 2 2009-01-01 03:00:09 | XYZ | 11 | 3 |
The following figure is a graphical illustration that shows the bid values and window ID changes. The bid value is greater than 10.6 on the second time slice (3:00:03) and remains for the remaining two time slices. The function increments the event window ID each time because the expression tests true:
Advanced use of event-based windows
In event-based window functions, the condition expression accesses values from the current row only. To access a previous value, you can use a more powerful event-based window that allows the window event condition to include previous data points. For example, analytic function LAG(x, n) retrieves the value of column x in the nth to last input record. In this case, LAG shares the OVER specifications of the CONDITIONAL_CHANGE_EVENT or CONDITIONAL_TRUE_EVENT function expression.
In the following example, the first query returns all records from the TickStore3 table. The second query uses CONDITIONAL_TRUE_EVENT with the LAG function in its boolean expression. In this case, CONDITIONAL_TRUE_EVENT increments the event window ID each time the bid value on the current row is less than the previous value. The first time CONDITIONAL_TRUE_EVENT increments the window ID starts on the third time slice, when the expression tests true. The current value (10.5) is less than the previous value. The window ID is not incremented in the last row because the final value is greater than the previous row:
SELECT ts, symbol, bidFROM Tickstore3 ORDER BY ts; |
|
|
ts | symbol | bid | cte ---------------------+--------+------+----- 2009-01-01 03:00:00 | XYZ | 10 | 0 2009-01-01 03:00:03 | XYZ | 11 | 0 2009-01-01 03:00:06 | XYZ | 10.5 | 1 2009-01-01 03:00:09 | XYZ | 11 | 1 |
The following figure illustrates the second query above. When the bid price is less than the previous value, the window ID gets incremented, which occurs only in the third time slice (3:00:06):
