基于其他表过滤TimescaleDB数据的最优SQL查询构建问题

你的核心问题在于原EXISTS子查询没有先完成过滤表的小时级聚合，且关联逻辑错误。以下是符合需求的正确写法及关键说明：

核心逻辑 #

过滤条件依赖其他表的「1小时平均值」，因此子查询必须先对过滤表按小时桶聚合，再通过时间桶与主查询关联，同时保证时间范围与主查询一致。

单过滤条件示例 #

以你提供的table_2中var_id=4的小时平均值>500为例，正确查询如下：

SELECT 
  var_id, 
  time_bucket('1 hour', data_time) AS bucket, 
  AVG(data_value) AS avg_value, 
  AVG(data_status) AS avg_status 
FROM data."table_1" 
WHERE 
  var_id IN (1, 2, 3) 
  AND data_time BETWEEN '2014-01-01T00:00' AND '2014-01-31T23:59:59' 
  -- 正确的过滤子查询
  AND EXISTS (
    SELECT 1 
    FROM data."table_2" 
    WHERE 
      var_id = 4 
      -- 子查询时间范围与主查询保持一致
      AND data_time BETWEEN '2014-01-01T00:00' AND '2014-01-31T23:59:59'
    -- 先按小时桶聚合过滤表数据
    GROUP BY time_bucket('1 hour', data_time)
    -- 用HAVING判断聚合后的条件，并关联主查询的时间桶
    HAVING AVG(data_value) > 500.0
      AND time_bucket('1 hour', data_time) = time_bucket('1 hour', data."table_1".data_time)
  )
GROUP BY bucket, var_id 
ORDER BY bucket, var_id

多过滤条件扩展 #

若需要同时添加table_3中var_id=12的小时平均值<90的过滤，只需追加一个独立的EXISTS子查询：

-- 在WHERE中追加
AND EXISTS (
  SELECT 1 
  FROM data."table_3" 
  WHERE 
    var_id = 12 
    AND data_time BETWEEN '2014-01-01T00:00' AND '2014-01-31T23:59:59'
  GROUP BY time_bucket('1 hour', data_time)
  HAVING AVG(data_value) < 90.0
    AND time_bucket('1 hour', data_time) = time_bucket('1 hour', data."table_1".data_time)
)

同表过滤场景 #

若过滤条件与主变量同表（比如table_1中var_id=5的小时平均值>10），只需给子查询的表加别名避免冲突：

AND EXISTS (
  SELECT 1 
  FROM data."table_1" AS t1_filter
  WHERE 
    t1_filter.var_id = 5 
    AND t1_filter.data_time BETWEEN '2014-01-01T00:00' AND '2014-01-31T23:59:59'
  GROUP BY time_bucket('1 hour', t1_filter.data_time)
  HAVING AVG(t1_filter.data_value) > 10.0
    AND time_bucket('1 hour', t1_filter.data_time) = time_bucket('1 hour', data."table_1".data_time)
)

关键注意点 #

• 子查询必须先聚合再过滤：用GROUP BY生成小时桶，HAVING判断聚合后的条件，不能直接用原始数据匹配
• 时间范围对齐：子查询的data_time范围必须和主查询完全一致，避免跨时间范围的错误匹配
• 表别名区分：同表过滤时必须给子查询表加别名，防止字段冲突

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