pipelinedb aggregates in where clause

Question

Im am using pipelinedb for testing some analysis of data streams from sensors. I want to be able, as an example, to find events in a stream that are defined by an aggregate. E.g. find events where the difference between max(temperature) and min(temparature) in the last 5 minutes exceeds a certain range.

When trying to put aggregates in the WHERE clause I get an error message saying something like 'aggregates not allowed in continuous views where clasues'

Am I missing something here or is it just not possible?

Otherwise I like pipelinedb very very much!

Well, pipelinedb says: "continuous queries don't support HAVING clauses".

What I'm trying to do is the following:

I have a stream named geo_vital_stream, which sends some sensor data along with a geolocation. At the moment I am interested

insert into geo_vital_stream (device_id, user_id, measured_at, heartrate, energy, eda, lon, lat)  VALUES( 'A005D8-E4 2.0',1,'2015-10-08 15:04:33.134000+02',96.8497201823,351.056269367,0.505791,8.07154018407,52.9531484103 );

My cv looks like this:

CREATE CONTINUOUS VIEW cv_sensor_eda AS 
SELECT user_id::integer, 
       MAX(eda::numeric) -  MIN(eda::numeric) as range_eda

FROM geo_vital_stream
WHERE (measured_at > clock_timestamp() - interval '1 minutes')
GROUP BY user_id

Now, I am interested only in those "events", where the range (range_eda execeeds a certain value in the last minute.)

Answer 1

Using an aggregate in a WHERE clause actually isn't legal SQL. That is accomplished using a HAVING clause, but it doesn't seem like that's what you need here. Since aggregates compute values across multiple rows, it's not clear to me how you'd retrieve individual events based on aggregates (min, max) across multiple events. Could you provide an example of what each event looks like?