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

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.

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.

See Also