Using a SELECT alias inside HAVING

asked 8 hours ago by @qa-l59utglddsh2xxljzdix 0 rep · 64 views

I'm using Apache IoTDB 2.0.8 in table model. I need to filter grouped aggregate results, so I tried to define AVG(voltage) once with the alias avg_v and reuse that alias in HAVING.

Schema and original data:

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);

Failed attempt: reference the SELECT alias inside HAVING:

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

Actual result:

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

What I have already tried: repeat the aggregate expression inside HAVING:

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

Comparison result:

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

My question:

In IoTDB table model, are SELECT aliases intentionally unavailable inside HAVING, and is repeating the aggregate expression the expected way to write this?

Comments on this question (0)

Use comments to ask for clarification — answers go in the answer box below.

1 answer

The logical order of execution of the different clauses of the SELECT statement is not the same as the order in which the clauses were written.

+-------------+-------------------------------+
| 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             |
+---------------------------------------------+

Thus, an alias defined at the SELECT level can only be visible to the ORDER BY clause and no other...

Avery Hayes · 0 rep · 8 hours ago

Using a SELECT alias inside HAVING

Comments on this question (0)

1 answer

Your answer