This is a sanity check really.
I’m doing an aggregation insert from a select using avg.max and max_by (downsampling) in the select grouped by a date_bin.
I want to do an on conflict update set to update the data if the source select data has changed.
Is it correct in the update set to do this :-
NSERT INTO <Table>
ON CONFLICT UPDATE SET
ae1 = avg( excluded.ae1 ),
e = MAX_BY( excluded.e,timestamp ),
Is this correct ?
The syntax you are looking for would be something like:
INSERT INTO <Table> ( ae1, e,....)
avg( ae1) ,
ON CONFLICT (<PKcolumns of Table>) DO UPDATE SET
ae1 = excluded.ae1 ,
e = excluded.e,
The fields in
excluded are the values that could not be inserted so they are already aggregated, but please do a few tests before putting this into production.
This approach requires that you still have available all the data to compute the downsampled values for the period.
An alternative approach would be to keep a “weight” in the table with the downsampled data, that way when a new value is added to the period or a value changes you can amend the downsampled value, but aggregations are so fast in CrateDB that if the data is still available it may not be worth the additional complexity.