用于平均多个数据并与一个数据点进行比较以判断偏移量的SQL查询

您的查询中没有任何可能产生虚假值的内容.也许值得重新审视你的WHERE个条件:

1. 如果您只想要这些来源:

   X = {01-18} and Y = {1,3,5,7,9,11,13,15,17,19,21,23}.
   

   你可以明确地表达这一点，而不是诉诸正则表达式中的catch—all %:

   "name" SIMILAR TO 
   'INV(0[1-9]|1[0-8])\_INVERTER\_SG\_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)' 
   ESCAPE '\'
   
   "name" ~ 
   'INV(0[1-9]|1[0-8])_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)' 
   

   它对SQL标准正则表达式(SIMILAR TO)和POSIX语法(~)都很有效，除了转义部分:_下划线是LIKE和SIMILAR TO中的单字符分号.\反斜杠是他们的默认转义字符，所以ESCAPE '\'只是为了冗长.

   我不会怀疑你忽略了一个文本的变体，你的助手抓住了你的意图，但技术上可能是这样的.

2. 您还可以确保您得到的意外值不在那里，因为您向表中提供的数据具有与在这里读取数据时不同的TimeZone设置.在这种情况下，

   "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()`
   

   如果源在后面，可能会给你less比最后24小时的数据，或者如果源在前面4小时，你会给你4小时到28小时之间的24小时切片.如果你的"timestamp"列实际上是timestamptz，这不应该是一个问题.

3. 您没有分享什么是"线"，以及您如何准确地绘制它们，但这也可能是错误的来源.

不幸的是，如果没有一个可复制的例子，这大多只是猜测.

Window functions可以得到相同的结果，速度明显更快，代码更少:DB<>Fiddle demo

SELECT "timestamp", value FROM (
    SELECT "timestamp",
           value,
           ABS(value - avg(value)over w1) / avg(value)over w1 as deviation
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    WINDOW w1 AS (PARTITION BY "timestamp") ) AS subq
WHERE .3 < deviation
ORDER BY "timestamp"

由于您正在处理时间序列数据，因此可以删除索引中的default 10% empty space以加快速度.你也可以把所有感兴趣的东西都放入指数中，得到index-only scans:

create index tnv_idx on st_values ("timestamp","name")
  include("value")
  with(fillfactor=100);

如果您仍有需要运行顺序扫描的查询，这些查询可能会受益于cluster:

cluster st_values using tnv_idx;