我在Apache IoTDB 2.0.8的表模型上运行一个告警摘要。该SQL先在子查询中把温度数据按10分钟分桶，然后外层查询统计穿越阈值的分桶数量。内部结果单独看起来还算合理，但外层的HAVING过滤掉的设备分组数量比我预期的要多。

模式与测试数据：

CREATE TABLE alarm_temperature_samples (time TIMESTAMP TIME, plant_id STRING TAG, device_id STRING TAG, temperature DOUBLE FIELD);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 00:00:00', '1001', 'device_a', 85.0);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 00:30:00', '1001', 'device_a', 72.0);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 01:00:00', '1001', 'device_a', 72.0);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 01:30:00', '3001', 'device_b', 72.0);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 02:00:00', '3001', 'device_b', 72.0);
INSERT INTO alarm_temperature_samples(time, plant_id, device_id, temperature) VALUES ('2024-11-26 02:30:00', '3001', 'device_b', 72.0);

告警摘要所使用的嵌套聚合查询：

SELECT plant_id, device_id
FROM (
    SELECT date_bin(10m, time) AS time, plant_id, device_id, AVG(temperature) AS bucket_avg_temperature
    FROM alarm_temperature_samples 
    WHERE time >= 2024-11-26 00:00:00
    AND time <= 2024-11-29 00:00:00 
    GROUP BY 1, plant_id, device_id
)
WHERE bucket_avg_temperature > 80.0 
GROUP BY plant_id, device_id
HAVING COUNT(*) > 1;

运行结果：

单独检查的内部10分钟聚合：

SELECT *
FROM alarm_temperature_samples 
ORDER BY time LIMIT 5;

结果：

我想要确认的是：

外层的COUNT(*) 是统计由内部聚合产生的行，还是原始未聚合的行？如果我想表达同一设备在多个10分钟窗口内超过阈值，这个嵌套查询的结构是否正确？

解决方案

HAVING 子句是对聚合后分组的过滤。它与 WHERE 子句的区别恰恰在于 WHERE 子句是对单条记录的过滤，而 HAVING 子句是对聚合的过滤。因此你不能在 HAVING 子句中使用未聚合的字段，因为聚合已经发生后它就没有意义，但你可以使用你分组时的聚合字段，即你的：

• plant_id
• device_id

你也可以使用聚合函数，例如在你的场景中使用 COUNT(*)。现在已经清楚 HAVING 子句是对聚合进行过滤，并且你的聚合基于子查询生成的关系，

HAVING COUNT(*) > 1

这意味着你只对包含多于一条记录的分组感兴趣。考虑到你内部查询的

    SELECT date_bin(10m, time) AS time, plant_id, device_id, AVG(temperature) AS bucket_avg_temperature
    FROM alarm_temperature_samples 
    WHERE time >= 2024-11-26 00:00:00
    AND time <= 2024-11-29 00:00:00 
    GROUP BY 1, plant_id, device_id

基本上，你会生成一个关系，其中包含按分桶分组的记录，以及进入你指定时间区间的记录，它们对应的 plant_id 和 device_id。

然而，你的外层 WHERE 子句是在你生成的关系的每条记录上运行并检查

WHERE bucket_avg_temperature > 80.0

是否为真，该条件为真时保留符合条件的记录，否则全部过滤。因此，你的外层按记录的筛选表示没有任何记录符合条件，至少达到这个平均温度的记录才会通过筛选，因此只有平均温度为85.0的那条记录“存活”下来，从而你的 GROUP BY 将这条记录聚成一个只有该记录的分组，但该分组被过滤，因为它是由唯一存活的元素来分组。

外层COUNT(*) 是统计由内部聚合产生的行，还是原始的未聚合行？

COUNT(*) 取决于上下文。你的上下文是

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

所以基本上你生成了分组，只有在它们聚合自的记录数至少为两条时，才希望在结果集中看到这些分组。

如果我想表达同一设备在多个10分钟窗口内超过阈值，这个嵌套查询的结构正确吗？

是的。但你的输入没有这样的设备，因为在你的子查询中超过阈值的记录恰好只有一条。