This Operator can be used to combine sequent elements, e.g. by a set of grouping attributes or with a predicates.
In the attributes case, the elements are merged with also given aggregations functions, as long as the grouping attributes (e.g. a sensorid) are the same. When a new group is opened (e.g. a measurement from a new sensor) the old aggregates values and the grouping attributes are created as a result.
In the predicate case, the elements are merged as long as the predicates evaluates to false, i.e. a new tuple is created when the predicates evaluates to true. If you want to aggregate as long, as the predicate is true, simply use !(predicate) as predicate.
coalesce = COALESCE({ATTR=['sensorid'], AGGREGATIONS=[['AVG','temperature','temperatur']},tempSensor1) coalesce = COALESCE({predicate='temperature>=10', AGGREGATIONS=[['last','temperature','temperature'], ['AVG,'temperature','avgTemp']},tempSensor1) |
A new version of the coalesce operator can combine attr and predicates:
Here is an example to explain this operator:
When using the following file (startstopinput.csv) as input
A;0 B;0 C;0 C;1 D;0 A;1 A;1 D;1 A;1 B;1 A;1 B;1 B;1 C;1 C;1 D;1 B;0 A;0 B;0 D;0 C;0 A;0 B;0 C;0 C;1 D;0 A;1 A;1 D;1 A;1 B;1 A;1 B;1 B;1 C;1 C;1 D;1 B;0 A;0 B;0 D;0 C;0 |
You can define the following query:
#PARSER PQL #ADDQUERY in = CSVFILESOURCE({ filename = '${WORKSPACEPROJECT}/startstopinput.csv', source = 'source', delimiter = ';', schema = [['group', 'String'], ['v', 'Integer']] } ) out = COALESCE({ aggregations = [['COUNT', 'v', 'count']], startpredicate = 'v>0', endpredicate = 'v=0', ATTR = ['group'] }, in ) |
Here for each group (A,B,C and D) the counting starts, when v is larger that 0 and stops when v is zero.
The output for the query can be found here:
Order is from bottom to top.