PostgreSQL时序数据：如何过滤含零值的整行时间戳？

要实现过滤掉所有包含零值的时间区间（你的20秒分组桶）对应的整组数据，可以通过先识别存在无效数据的时间桶，再在主查询中排除这些桶的方式实现，无需指定具体部件列，适配不同机器的传感器数量差异。

修改后的SQL查询 #

WITH invalid_time_buckets AS (
  -- 找出当前机器、时间范围内存在0值的所有20秒时间桶
  SELECT DISTINCT
    time_bucket('20 sec', time) AS invalid_bucket
  FROM temp
  WHERE
    machineno = '$MachineNo'
    AND $__timeFilter(time)
    AND value = 0
)
SELECT 
  time_bucket('20 sec', time) AS time,
  part AS metric,
  MAX(value)
FROM temp
WHERE
  machineno = '$MachineNo'
  AND $__timeFilter(time)
  -- 排除存在0值的时间桶
  AND time_bucket('20 sec', time) NOT IN (SELECT invalid_bucket FROM invalid_time_buckets)
GROUP BY time, part
ORDER BY time;

逻辑说明 #

1. CTE invalid_time_buckets：先查询出当前选中机器、时间范围内，所有包含至少一条value=0记录的20秒时间桶，用DISTINCT去重避免重复的桶记录。
2. 主查询过滤：在WHERE子句中添加条件，确保当前分组的时间桶不在无效桶列表中，彻底排除所有包含过0值的时间段数据。
3. 通用性：整个查询只依赖表中通用的machineno、time、value字段，不需要指定具体的part（传感器）列，适配不同机器的传感器数量差异。

补充说明 #

如果你的需求是过滤原始时间戳（而非分组后的时间桶）——即只要某个原始时间点有任何传感器值为0，就排除该时间点的所有记录，可以将CTE和主查询中的time_bucket('20 sec', time)替换为原始的time字段，修改后的SQL如下：

WITH invalid_timestamps AS (
  SELECT DISTINCT time
  FROM temp
  WHERE
    machineno = '$MachineNo'
    AND $__timeFilter(time)
    AND value = 0
)
SELECT 
  time_bucket('20 sec', time) AS time,
  part AS metric,
  MAX(value)
FROM temp
WHERE
  machineno = '$MachineNo'
  AND $__timeFilter(time)
  AND time NOT IN (SELECT time FROM invalid_timestamps)
GROUP BY time, part
ORDER BY time;

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