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.

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

Immutable

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.

Examples

> 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.