Hi
The idea of continuous aggregates seems really interesting for our use case, but unfortunately having regularly spaced time intervals is too restrictive. Consider the following illustration:
The store data in a table of format:
time, device=D1, step=1, sensor_value=v1
time, device=D1, step=NULL, sensor_value=v2
time, device=D1, step=NULL, sensor_value=v3
time, device=D1, step=2, sensor_value=v4
Essentially aggregation would need to take into account two additional requirements:
- time windows are dictated by a different column (production step) and might not have an equal size
- sensor values during which production is down should be ignored
Is there some way to achieve aggregation of this kind of data in Timescale? Even better, is it possible to achieve this with continuous aggregates?
Hi Giokara! welcome to our community!
Probably state_agg can help you to split your aggregation as step seems to be your state/grouping criteria.
sensor values during which production is down should be ignored
can it be just a where clause?
1 Like
I have a use-case related to this type of data but with a different query need:
Given two tables:
- a hyper table of sensor measurements (columns:
time, sensor_value)
- a table of event intervals (columns:
event_id, started_at, ended_at)
I’d like to calculate summary statistics during each interval.
e.g. for each event, calculate the min, max, average sensors values, producing a table with columns event_id, sensor_value_min, sensor_value_max, sensor_value_avg
I can’t see a way to leverage any of the timescale operators to help with this. It’s a different use case than what state_agg supports since i’m not just calculating aggregates for a small number of states.
Any suggestions?
