I am having a Influxdb 1.8 with temperature measurements “temperature” tagged with a location “name”.
I need help to create a continuous query that collects daily temperature sum. The temperature sum is defined as a daily average of temperature exceeding +5 centigrade. i.e if the daily average is 2 degree, the temperature sum is 0 day degree and if the daily average is +8 degree the temperature sum is 3 day degree for that day.
I have made a CQ Query_ave that calculates the daily average temperature and another CQ Query_summa to calculate the temperature sum:
CREATE CONTINUOUS QUERY Query_ave ON saa BEGIN SELECT mean(temperature) INTO historia.ave FROM ruuvi GROUP BY time(1d), “name” END
CREATE CONTINUOUS QUERY Query_summa ON saa BEGIN SELECT mean - 5 INTO historia.lamposumma FROM historia.ave WHERE mean > 5 GROUP BY time(1d), * END
I can get the averages correctly and tagged by “name” but the second query gives nothing.
I tested also the query without having a continuous query, like this
SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY time(1d), *
ERR: GROUP BY requires at least one aggregate function
SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY time(1d)
ERR: GROUP BY requires at least one aggregate function
SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY *
As you can see, the error asks for an aggregate function, although I have time(1d). Without aggregate function the query succeeds but I need to have time() in a continuous query to automate the daily calculation !?!
I still need help in getting the continuous query to run.
I have tried multiple versions since my last post. i realized that this is a nested query instead of two separate queries. I could not have the query itself grouped by time(). This seems to be a known problem with subqueries. I was hoping to have a continuous query with a RESAMPLE clause but did not succeed.
If I run the query from the CLI (without continuous query) I get the results but calculated over all data and the output with the epoch time stamp.
SELECT (mean - 5) AS lamposumma FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari) WHERE mean > 5 GROUP BY “name”
name: ruuvi_Olari
tags: name=
time lamposumma
1970-01-01T00:00:00Z 16.061787905249258
Next I tried to run the query as a continuous query once a day for a daily average and save the result into an exisiting measurement “minmax”. No results in the database.
CREATE CONTINUOUS QUERY Query_lamposumma ON Olari_saa RESAMPLE EVERY 1d FOR 1d BEGIN SELECT (mean - 5) AS lamposumma INTO Olari_saa.historia.minmax FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari) WHERE mean > 5 GROUP BY “name” END
I cannot figure out why the continuous query does not work. Please, advice.
Hello @manttila,
Welcome! Sorry for the delay. Thanks for figuring out the nested subquery bit!
You need to group by time for your subquery to work with resample every 1d for 1d. Also you won’t see a data point until after 1d has passed. Please read this example closely. InfluxQL Continuous Queries | InfluxDB OSS 1.8 Documentation
Thank you for your support ! I learned that I should have the group by time() in the subquery. Now I can get the query grouping correctly when I run it on the influx cli ! However, I still cannot get it running automatically as a continuous query. I have tried several modifications without a success.
When I run the following query I get results correctly: SELECT (mean - 5) AS lamposumma FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari GROUP BY time(1d)) WHERE mean > 5 GROUP BY "name"
However, when I CREATE CONTINOUS QUERY … RESAMPLE EVERY 1d FOR 1d… INTO …
CREATE CONTINUOUS QUERY cq_lamsum ON Olari_saa RESAMPLE EVERY 1d FOR 1d BEGIN SELECT (mean - 5) AS lamposumma INTO Olari_saa.historia.lamsum FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari GROUP BY time(1d)) WHERE mean > 5 GROUP BY “name” END
Nothing happens. The query does not simply run. I cannot find any traces from the log either. I can see my other continuous queries running regularly (with simpler syntax) but not this one ! I am stuck here.
Maybe someone else’s eye can catch what is wrong. RESAMPLE EVERY 1d FOR 1d looks right to me. I have waited 1-2 days, tried shorter query names, tried different INTO clauses …
This is a frustrating issue. I have studied the documentation on the continuous queries without a success. Maybe my (real)case was too complicated for other readers to follow and to provide help. Here is a simplified test case that demonstrates the issue:
I am collecting temperature data “temperature” tagged with a location “name”. If I run the following query I get correct output:
select mean-5, “name” from (select mean(temperature) from kasvukausi.ruuvi_Olari group by “name”) where mean>5 and time > now()-1h
name: ruuvi_Olari
time mean name
2021-03-08T18:03:08.941034279Z 14.388529411764743 Kasvihuone
2021-03-08T18:03:08.941034279Z 16.92999999999997 Makuuhuone
2021-03-08T18:03:08.941034279Z 14.463596491228028 Olohuone
If I want to run the same query periodically as a continuous query, here once an hour, it simply does not work.
CREATE CONTINUOUS QUERY cq_test ON Olari_saa RESAMPLE EVERY 1h FOR 1h BEGIN SELECT mean - 5, “name” INTO Olari_saa.kasvukausi.test FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari GROUP BY “name”) WHERE mean > 5 END
There is nothing retrieved by the cq_test after several hours (kasvukausi is the default retention policy)
select * from test
What is the difference ? Why doesn’t the continuous query retrieve any data ? No error messages either !
CREATE CONTINUOUS QUERY cq_lamsum ON Olari_saa RESAMPLE EVERY 1d FOR 1d
BEGIN
SELECT
(mean - 5) AS lamposumma
INTO
Olari_saa.historia.lamsum
FROM
(SELECT
mean(temperature)
FROM
Olari_saa.kasvukausi.ruuvi_Olari
GROUP BY
time(1d))
WHERE mean > 5
GROUP BY time(1d), “name”
END
