Apache IoTDB 2.0.5关联子查询报错，如何实现跨组平均值比较查询？

IoTDB 2.0.5对关联子查询（子查询引用外部表字段）和非等值连接的支持有限，因此需要通过预计算分组统计值+交叉连接/等值连接的方式实现需求。

一、查询温度高于其他组平均值的记录 #

先预计算每个组的平均温度，再将原始数据与该统计结果做交叉连接，过滤掉同组数据后比较温度：

SELECT t1.*
FROM sensor_readings t1,
     (SELECT group_id AS other_group, AVG(temperature) AS other_avg
      FROM sensor_readings
      GROUP BY group_id) t2
WHERE t1.group_id != t2.other_group
  AND t1.temperature > t2.other_avg

逻辑说明： #

• 子查询(SELECT group_id AS other_group, AVG(temperature) AS other_avg FROM sensor_readings GROUP BY group_id)预计算所有组的平均温度；
• 交叉连接（笛卡尔积）原始表与统计结果，通过t1.group_id != t2.other_group过滤掉同组的平均数据；
• 最后筛选出温度高于对应其他组平均值的记录，与预期结果完全匹配。

二、扩展：查询温度高于父组平均值的记录 #

如果需要实现最初的父组层级查询，先通过group_hierarchy关联父组与对应平均温度，再与原始表做等值连接过滤：

SELECT t1.*
FROM sensor_readings t1
JOIN (
    SELECT gh.child_group, ga.group_avg AS parent_avg
    FROM group_hierarchy gh
    JOIN (
        SELECT group_id, AVG(temperature) AS group_avg
        FROM sensor_readings
        GROUP BY group_id
    ) ga ON gh.parent_group = ga.group_id
) t2 ON t1.group_id = t2.child_group
WHERE t1.temperature > t2.parent_avg

逻辑说明： #

• 最内层子查询预计算所有组的平均温度；
• 中间层通过group_hierarchy将每个子组关联到其父组的平均温度；
• 最后将原始表与父组平均数据做等值连接，筛选出温度高于父组平均的记录。

原查询报错原因 #

• 第一个报错是因为IoTDB 2.0.5不支持关联子查询（子查询中引用了外部表t1.group_id）；
• 第二个报错是因为非等值连接条件（t2.group_id != t1.group_id）无法被IoTDB的谓词下推机制处理，导致查询不被支持。

内容的提问来源于stack exchange，提问作者MGS