我在表模型中使用 Apache IoTDB 2.0.8。我需要筛选分组聚合结果，因此我尝试先将AVG(voltage) 定义一次，并用别名 avg_v，再在 HAVING 中复用该别名。

模式与原始数据：

CREATE TABLE voltage_log (device_id STRING TAG, region STRING TAG, voltage DOUBLE FIELD);
INSERT INTO voltage_log(`time`, device_id, region, voltage) VALUES (1000, 'D1', 'north', 220.0);
INSERT INTO voltage_log(`time`, device_id, region, voltage) VALUES (2000, 'D2', 'north', 225.0);
INSERT INTO voltage_log(`time`, device_id, region, voltage) VALUES (3000, 'D3', 'south', 198.0);
INSERT INTO voltage_log(`time`, device_id, region, voltage) VALUES (4000, 'D4', 'south', 202.0);

失败尝试：在 HAVING 中引用 SELECT 别名：

SELECT region, AVG(voltage) AS avg_v 
FROM voltage_log 
`GROUP BY` region 
HAVING avg_v > 210;

实际结果：

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 616: Column 'avg_v' cannot be resolved

我已经尝试过：在 HAVING 内重复聚合表达式：

SELECT region, AVG(voltage) AS avg_v 
FROM voltage_log 
`GROUP BY` region 
HAVING AVG(voltage) > 210;

比较结果：

  +------+-----+
  |region|avg_v|
  +------+-----+
  | north|222.5|
  +------+-----+

我的问题：

在IoTDB表模型中，SELECT 的别名是否被故意设置为在 HAVING 内不可见，重复聚合表达式是否是编写此类查询的预期做法？

解决方案

SELECT语句中不同子句的执行逻辑顺序并不等于子句被书写的顺序。

+-------------+-------------------------------+
| SQL CLAUSE  | WRITTEN ORDER | LOGICAL ORDER |
+---------------------------------------------+
| SELECT      | 1             | 6             |
+---------------------------------------------+
| FROM        | 2             | 1             |
+---------------------------------------------+
| WHERE       | 3             | 2             |
+------------------------------------_--------+
| GROUP BY    | 4             | 3             |
+---------------------------------------------+
| HAVING      | 5             | 4             |
+---------------------------------------------+
| WINDOW      | 6             | 5             |
+---------------------------------------------+
| ORDER BY    | 7             | 7             |
+---------------------------------------------+

因此，在SELECT级定义的别名只能对ORDER BY子句可见，不能被其他子句看到……

备选方案

嵌套查询可以让你避免把该聚合函数写两遍：

SELECT region, avg_v 
FROM (SELECT region, AVG(voltage) AS avg_v        
      FROM voltage_log        
      GROUP BY region)   
WHERE avg_v > 210;