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.

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.

See also