Use having on SQL Query-CodePudding

I'm use the next query to get the average of memory used, but only need get data when average is more than x value

SELECT AVG(system.memory.actual.used.pct) AS averageMemory from "metricbeat*" where  host.name.keyword='data.storage' HAVING AVG(system.memory.actual.used.pct)>0.3 and "@timestamp" BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z'

And show me the next error

Found 1 problem\nline 1:152: Cannot use HAVING filter on non-aggregate [@timestamp]; use WHERE instead"

And when use WHERE clause, like this

SELECT AVG(system.memory.actual.used.pct) AS averageMemory from "metricbeat*" where  host.name.keyword='data.storage' and averageMemory>0.3 and "@timestamp" BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z'

Show the next error

Found 1 problem\nline 1:8: Cannot use WHERE filtering on aggregate function [AVG(system.memory.actual.used.pct)], use HAVING instead

Im confused. Any idea?

CodePudding user response：

HAVING AVG(system.memory.actual.used.pct)>0.3 is correct, as HAVING expresses a constraint on aggregated data, and WHERE ... averageMemory>0.3 is not correct, as WHERE expresses a constraint at record level.

For the same reason, HAVING @timestamp BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z' is wrong, since @timestamp is not an aggregated value but a record level attribute.

So WHERE ... and @timestamp BETWEEN '2021-10-01T00:00:00.000Z HAVING AVG(system.memory.actual.used.pct)>0.3 should be the correct way.