Nsryjdtyk

I have sensors reporting events. Each event has unique device id, and some measurement value, and reporting sequence id (just for debugging).

I want to get only events that are significant (i.e. their measurement value is twice over maximum observed so far), all this based on time - i.e. I want to get the first event, then block N subsequent events for time period T, unless one of those events has very big payload.
I managed to get it done (more or less) in this way:

create schema Alerts(feeder string, load double, seq int);

create context SegmentedByAsset partition by feeder from Alerts;



create constant variable int WindowTimeSec = 29;



@Name('Out') 

context SegmentedByAsset 

select e2 from Alerts.win:time(WindowTimeSec sec).std:firstevent() as e1, 

               Alerts.std:lastevent() as e2 

where e2.seq = e1.seq or e2.load > 2* (select max(prev(load)) 

from Alerts.win:time(WindowTimeSec sec))

• First - that's ugly.


• Second - I have a "gut feeling" that it won't be very efficient.


• Third - if the condition for special events detection gets more complex, simple subquery won't work (i.e. in addition to load event will have more attributes, like time and type, and I'll need to compare time and type of the event with max load to decide if the last event should be published or blocked).

What would be the recommended pattern to do it ?

10x

You can design a table row to hold the aggregation and use @priority to say when it is updated.

create schema Alert(feeder string, load double, seq int);

create context SegmentedByAsset partition by feeder from Alert;



create constant variable int WindowTimeSec = 29;



context SegmentedByAsset create table LoadAggTable(maxLoad max(double)); 



// select first (priority is 1)

@name('out') @priority(1) context SegmentedByAsset select * from Alert where load > LoadAggTable.maxLoad;



// update table next (priority is 0)

@priority(0) context SegmentedByAsset into table LoadAggTable select max(load) as maxLoad from Alert#time(WindowTimeSec sec);

The table could hold "maxBy" which is the event that contributed the max or other aggregations as well.

I came out with something based on Windows (previous answer, suggesting to use Tables with aggregation columns, may work, but I don't understand why :))

create schema Alerts(feeder string, load double, seq int);

create context SegmentedByAsset partition by feeder from Alerts;



create constant variable int WindowTimeSec = 12;



context SegmentedByAsset 

create window AlertsWindow.win:time(WindowTimeSec sec).ext:sort(1, load desc) as Alerts;



@Name('Select') 

context SegmentedByAsset 

select e2 from  Alerts.std:lastevent() as e2 where (select count(*) from 

   AlertsWindow) = 0 or e2.load > 2* (select load from AlertsWindow);



@Name('Insert') 

context SegmentedByAsset 

insert into AlertsWindow select * from Alerts;

Essentially it just keeps event with maximal load for the past WindowTimeSec period in the named window, if new event has bigger load it gets published, if time period expired and named window is empty - any new event gets published. It is not exactly what I had in mind, but for this specific use case it would work as well, and "bypass condition" can have any complexity, as the complete event data is kept in the window.