Vertica Analytics Platform Version 9.2.x Documentation

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.

Example

=> 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 in Analyzing Data.