Question Details

No question body available.

Tags

sql time-series apache-iotdb

Answers (1)

July 5, 2026 Score: 2 Rep: 81,899 Quality: Medium Completeness: 80%

The HAVING clause is a filter on the aggregated groups. It differs from the WHERE clause precisely in the fact that the WHERE clause is a filter on individual records whereas the HAVING clause is a filter on aggregation. So you cannot use a not aggregated field in your HAVING clause because it's meaningless once the aggregation already happened, but you can use aggregated fields that you grouped by, which are your:

  • plantid
  • deviceid

and you can also use aggregation functions, such as COUNT() in your case. Now that it is clear that the HAVING clause is a filter on aggregations and your aggregation is based on a relation generated by a subquery,

HAVING COUNT() > 1

means that you are only interested on groups that have more than one record. Given your inner query of

    SELECT datebin(10m, time) AS time, plantid, deviceid, AVG(temperature) AS bucketavgtemperature
    FROM alarmtemperaturesamples 
    WHERE time >= 2024-11-26 00:00:00
    AND time  80.0

is true, filtering out all records that fail to meet this criteria. So your outer record-level filter says that none of the records are interesting that don't have at least that average temperature so only the one with the average temperature of 85.0 "survives" the filter and hence your GROUP BY makes a group of this single record, which is filtered out because it is grouped by the single surviving element. Hence:

Does the outer COUNT(*) count rows produced by the inner aggregation, or the original raw rows?

COUNT(*) depends on the context. Your context is

GROUP BY plantid, device_id
HAVING COUNT(*) > 1

so basically you generated groups and you are only wanting to see groups in your result set if and only if the records they were aggregated from number at least two.

If I want to express that the same device exceeded the threshold in multiple 10-minute windows, is this nested query the correct shape?

Yes. But your input has no such device, as the treshold is exceeded by exactly one record in your subquery.