I have a measurement which holds data from 3 temperature sensors (under different tags) and would like to create a continuous query to aggregate sensor data. The query works most of the time, but I get frequent spikes for one of the sensors which are not present in the source measurement. This is the query I use:
CREATE CONTINUOUS QUERY "cq_1h_temp" ON "mydb"
RESAMPLE FOR 3h
BEGIN
SELECT mean("value") AS mean_value
INTO "a_year"."downsampled_temp"
FROM "autogen"."temperature"
GROUP BY time(1h), entity_id
FILL(previous)
END
When I use the query with FILL(none), it works fine, but then I can encounter gaps at the beginning or the end of the graph which I’d like to avoid with FILL(previous).
Below is the graph made by two CQs which only differ in FILL(…) clause:
This is the CQ data with the peak example:
select mean_value from a_year.downsampled_temp where time >= '2019-05-23 04:00:00' and time < '2019-05-23 09:00:00' and entity_id = 'temp1' tz('Europe/Moscow')
name: downsampled_temp
time mean_value
---- ----------
2019-05-23T04:00:00 9.5
2019-05-23T05:00:00 8.5
2019-05-23T06:00:00 25.3 <<--- the peak
2019-05-23T07:00:00 6
2019-05-23T08:00:00 5.333333333333333
This is the data from source measurement:
select value from autogen.temperature where time >= '2019-05-23 04:00:00' and time < '2019-05-23 09:00:00' and entity_id = 'temp1' tz('Europe/Moscow')
name: temperature
time value
---- -----
2019-05-23T04:05:46.686852864 10
2019-05-23T04:35:46.826548992 9
2019-05-23T05:05:46.44398592 8
2019-05-23T05:35:46.91831296 9
2019-05-23T07:35:46.76934784 6
2019-05-23T08:20:47.38325504 7
2019-05-23T08:35:46.802694912 5
2019-05-23T08:50:46.528475904 4
There are other temperature points (with different tags) stored to the same measurement which always have the values very close to those spikes so I suspect they are somehow interfering with temp1 data.
select value from autogen.temperature where time >= '2019-05-23 04:00:00' and time < '2019-05-23 09:00:00' and entity_id = 'temp2' tz('Europe/Moscow')
name: temperature
time value
---- -----
2019-05-23T04:10:13.2414912 25.5
2019-05-23T05:01:26.723787008 25.6
2019-05-23T05:04:38.244174848 25.5
2019-05-23T05:32:25.539938816 25.6
2019-05-23T06:05:35.065538048 25.5
2019-05-23T07:08:52.748280064 25.4
2019-05-23T08:25:24.378972928 25.3
Any ideas on how to get rid of those spikes would be greatly appreciated. InfluxDB version is 1.7.6