PostgreSQL逆变器电压异常点查询调试求助

你的查询出现虚假异常点，大概率是两个核心原因：

• 当某时间点的平均电压接近0时，微小的数值差会被放大，导致误判为异常
• 未排除同一时间点仅单条数据的情况（若该数据本身异常，会直接触发偏差条件）

以下是修正后的查询：

WITH average AS (
    SELECT
        "timestamp",
        AVG(value) AS avg_value,
        COUNT(value) AS value_count -- 统计该时间点的有效数据条数
    FROM
        st_values
    WHERE
        "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
        -- 用正则精确匹配设备名称格式，避免LIKE误匹配无关数据
        AND "name" ~ '^INV\d{2}_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)$'
    GROUP BY "timestamp"
    HAVING COUNT(value) >= 2 -- 至少2条数据才计算平均值，确保参考性
)
SELECT
    v."timestamp",
    v.value,
    v."name", -- 新增设备名称，方便定位具体异常逆变器
    av.avg_value,
    ROUND(ABS(v.value - av.avg_value)/av.avg_value * 100, 2) AS deviation_percent -- 显示偏差百分比，便于验证
FROM
    st_values v
JOIN
    average av ON av."timestamp" = v."timestamp"
WHERE
    v."timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
    AND v."name" ~ '^INV\d{2}_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)$'
    AND av.avg_value > 0 -- 排除平均电压为0的情况，避免除以0或极小值误判
    AND ABS(v.value - av.avg_value)/av.avg_value > 0.3
ORDER BY
    v."timestamp";

关键修正说明： #

• 精确匹配设备名称：替换LIKE为正则表达式，严格匹配INVxx_INVERTER_SG_Branch VoltageY的格式（xx为01-18，Y为指定奇数），避免混入无关设备数据
• 过滤单数据时间点：通过HAVING COUNT(value) >=2确保平均值是基于多个设备的有效数据计算，避免单条异常数据直接触发判断
• 防护极小值分母：添加av.avg_value >0条件，避免因电压接近0导致的偏差放大误判
• 新增辅助字段：加入设备名称、平均电压、偏差百分比，方便在Grafana中直接验证异常的真实性

额外排查建议： #

1. 检查表中是否存在重复的timestamp+name记录，这类数据会干扰平均值计算，可通过以下语句排查：

SELECT "timestamp", "name", COUNT(*) 
FROM st_values 
GROUP BY "timestamp", "name" 
HAVING COUNT(*) >1;

2. 在Grafana中查看查询返回的原始数据，确认虚假红点对应的timestamp和value是否真的存在于表中，排除可视化层的显示误差

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