CONDITIONAL_TRUE_EVENT [Analytic]
Assigns 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. For example, given a sequence of values for column a, as follows:
( 1, 2, 3, 4, 5, 6 )
CONDITIONAL_TRUE_EVENT(a > 3)
returns 0, 0, 0, 1, 2, 3
.
Behavior Type:
Syntax
CONDITIONAL_TRUE_EVENT ( boolean-expression ) OVER ... ( [ window-partition-clause ] ... window-order-clause )
Parameters
boolean-expression |
SQL scalar expression that is evaluated on an input record, type BOOLEAN. |
OVER()
|
See Analytic Functions. |
Notes
The analytic window-order-clause is required but the window-partition-clause is optional.
Example
> SELECT CONDITIONAL_TRUE_EVENT(bid > 10.6)
OVER(PARTITION BY bid ORDER BY
ts) AS cte
FROM Tickstore;
The system returns an error if the ORDER BY
clause is omitted:
> SELECT CONDITIONAL_TRUE_EVENT(bid > 10.6) OVER(PARTITION BY bid) AS cte FROM Tickstore; ERROR: conditional_true_event must contain an ORDER BY clause within its analytic clause
For more examples, see Event-Based Windows in Analyzing Data.