I'm using the table model of Apache IoTDB 2.0.5 and trying to implement a query to find records higher than the average value of the same group. I need to use non-equality comparisons in the join condition to define the "same group" relationship, but I'm hitting a limitation.

Partial Raw Data

  CREATE TABLE sensor_readings (
  `time` TIMESTAMP `TIME`,
  device_id STRING TAG,
  group_id STRING ATTRIBUTE,
  temperature FLOAT FIELD
  );
  
  CREATE TABLE group_hierarchy (
  child_group STRING TAG,
  parent_group STRING ATTRIBUTE,
  level INT32 FIELD
  );
  
  INSERT INTO sensor_readings VALUES
  (2024-01-01T00:00:00, 'D1', 'A', 25.0),
  (2024-01-01T01:00:00, 'D1', 'A', 30.0),
  (2024-01-02T00:00:00, 'D1', 'A', 35.0),
  (2024-01-01T00:00:00, 'D2', 'B', 20.0),
  (2024-01-01T01:00:00, 'D2', 'B', 28.0),
  (2024-01-01T02:00:00, 'D2', 'B', 32.0);
  
  INSERT INTO group_hierarchy VALUES
  (2024-01-01T00:00:00, 'A', 'ROOT', 1),
  (2024-01-01T00:00:00, 'B', 'A', 2);

Query Statement

I want to find device records where the temperature is higher than the average temperature of the parent group, requiring hierarchical comparisons in the join condition:

  SELECT * FROM sensor_readings t1
  WHERE temperature > (
  SELECT AVG(temperature)
  FROM sensor_readings t2
  WHERE t2.group_id IN (
  SELECT child_group
  FROM group_hierarchy t3
  WHERE t3.level < (
  SELECT level FROM group_hierarchy t4
  WHERE t4.child_group = t1.group_id
  )
  )
  );

Error Message

Bash
  Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Given correlated subquery is not supported

Then I simplified the query to find records where temperature is higher than the average of other groups at the same level:

  SELECT * FROM sensor_readings t1
  WHERE temperature > (
  SELECT AVG(temperature)
  FROM sensor_readings t2
  WHERE t2.group_id != t1.group_id
  );

Still getting an error:

Bash
  Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Unsupported Join creteria [(\"group_id_2\" <> \"group_id\")] after predicate push down

Expected Result

Find records where temperature is higher than the average of other groups:

• D1 (Group A): temperature > average of Group B (26.67) → 30.0, 35.0

• D2 (Group B): temperature > average of Group A (30.0) → 32.0

Expected result set:

  +-----------------------------+----------+---------+-----------+
  | `time`| device_id|group_id|temperature|
  +-----------------------------+----------+---------+-----------+
  |2024-01-01T01:00:00.000+08:00| D1| A| 30.0|
  |2024-01-01T02:00:00.000+08:00| D1| A| 35.0|
  |2024-01-01T02:00:00.000+08:00| D2| B| 32.0|
  +-----------------------------+----------+---------+-----------+

Question

How should I write the query to achieve the expected result?