CONDITIONAL_CHANGE_EVENT [analytic]

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

Behavior type

Immutable

Syntax

CONDITIONAL_CHANGE_EVENT ( expression ) OVER (
    [ window-partition-clause ]
    window-order-clause )

Parameters

expression: SQL scalar expression that is evaluated on an input record. The result of expression can be of any data type.
OVER(): See Analytic Functions.

Notes

The analytic window-order-clause is required but the window-partition-clause is optional.

Examples

=> SELECT CONDITIONAL_CHANGE_EVENT(bid)
          OVER (PARTITION BY symbol ORDER BY ts) AS cce
   FROM TickStore;

The system returns an error when no ORDER BY clause is present:

=> SELECT CONDITIONAL_CHANGE_EVENT(bid)
          OVER (PARTITION BY symbol) AS cce
   FROM TickStore;

ERROR:  conditional_change_event must contain an
ORDER BY clause within its analytic clause

For more examples, see Event-based windows.