问题描述

这是配网设备故障类型表：https://omhost.net/misc2025/ftu-fault-types

请忽视设备故障、环境因素两大类型。重点考虑短路故障、接地故障、失压故障、过载故障、断路故障这5大类型，以及它们的二级分类。共有5个大类，每个大类有5个子类，总共25个二级分类。

这是遥测数仓表结构定义：https://omhost.net/notes2025/telemetry-sql-tables 数仓工具是timescaledb，支持time_bucket等时序函数。

需求：针对上述25个二级分类的故障类型，分析它们的故障表现，并根据遥测数据表，将故障表现转换为sql。比如：接地故障一般就是零序电流过高，那么对应的sql就是查零序电流阈值是否超标。

应用场景：一旦有ftu的数据入库，我能立刻知道，这个数据是否正常。如果异常，对应的是什么故障分类。

解决方案：故障特征分析与对应SQL查询

短路故障

1. 三相短路

   • 特征：三相电流突增，系统电压骤降至接近零
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   WHERE 
     (fr.IA_Current > 2 * AVG(fr.IA_Current) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     )) AND
     (fr.IB_Current > 2 * AVG(fr.IB_Current) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     )) AND
     (fr.IC_Current > 2 * AVG(fr.IC_Current) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     )) AND
     (fr.UAB_Voltage < 0.1 * AVG(fr.UAB_Voltage) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     )) AND
     (fr.UBC_Voltage < 0.1 * AVG(fr.UBC_Voltage) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     )) AND
     (fr.UCA_Voltage < 0.1 * AVG(fr.UCA_Voltage) OVER (
       PARTITION BY fr.FTUKey 
       ORDER BY fr.ReadingTimestamp
       RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
     ));
   
   

2. 两相短路

   • 特征：两相电流突增，相间电压降低
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp, 
          CASE 
            WHEN fr.IA_Current > threshold_a AND fr.IB_Current > threshold_b THEN 'A-B相短路'
            WHEN fr.IB_Current > threshold_b AND fr.IC_Current > threshold_c THEN 'B-C相短路'
            WHEN fr.IC_Current > threshold_c AND fr.IA_Current > threshold_a THEN 'C-A相短路'
          END AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   CROSS JOIN (
     SELECT 
       2 * PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IA_Current) AS threshold_a,
       2 * PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IB_Current) AS threshold_b,
       2 * PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IC_Current) AS threshold_c
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '24 hours'
   ) thresholds
   WHERE (
     (fr.IA_Current > thresholds.threshold_a AND fr.IB_Current > thresholds.threshold_b AND fr.UAB_Voltage < 0.7 * 
       AVG(fr.UAB_Voltage) OVER (
         PARTITION BY fr.FTUKey 
         ORDER BY fr.ReadingTimestamp
         RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
       )
     ) OR 
     (fr.IB_Current > thresholds.threshold_b AND fr.IC_Current > thresholds.threshold_c AND fr.UBC_Voltage < 0.7 * 
       AVG(fr.UBC_Voltage) OVER (
         PARTITION BY fr.FTUKey 
         ORDER BY fr.ReadingTimestamp
         RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
       )
     ) OR 
     (fr.IC_Current > thresholds.threshold_c AND fr.IA_Current > thresholds.threshold_a AND fr.UCA_Voltage < 0.7 * 
       AVG(fr.UCA_Voltage) OVER (
         PARTITION BY fr.FTUKey 
         ORDER BY fr.ReadingTimestamp
         RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 minute' PRECEDING
       )
     )
   );
   
   

3. 相间短路

   • 特征：短路相电流增大，相间电压降低
   • SQL查询 (类似于两相短路但使用时间桶分析):

   WITH baseline AS (
     SELECT 
       FTUKey,
       AVG(IA_Current) AS avg_ia,
       AVG(IB_Current) AS avg_ib,
       AVG(IC_Current) AS avg_ic,
       AVG(UAB_Voltage) AS avg_uab,
       AVG(UBC_Voltage) AS avg_ubc,
       AVG(UCA_Voltage) AS avg_uca
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '1 day' AND NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey
   )
   SELECT 
     fr.FTUKey, 
     d.FTU_ID, 
     time_bucket('1 minute', fr.ReadingTimestamp) AS event_time,
     CASE 
       WHEN fr.IA_Current > 1.5 * b.avg_ia AND fr.IB_Current > 1.5 * b.avg_ib AND fr.UAB_Voltage < 0.8 * b.avg_uab THEN 'A-B相间短路'
       WHEN fr.IB_Current > 1.5 * b.avg_ib AND fr.IC_Current > 1.5 * b.avg_ic AND fr.UBC_Voltage < 0.8 * b.avg_ubc THEN 'B-C相间短路'
       WHEN fr.IC_Current > 1.5 * b.avg_ic AND fr.IA_Current > 1.5 * b.avg_ia AND fr.UCA_Voltage < 0.8 * b.avg_uca THEN 'C-A相间短路'
     END AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN baseline b ON fr.FTUKey = b.FTUKey
   WHERE 
     ((fr.IA_Current > 1.5 * b.avg_ia AND fr.IB_Current > 1.5 * b.avg_ib AND fr.UAB_Voltage < 0.8 * b.avg_uab) OR 
      (fr.IB_Current > 1.5 * b.avg_ib AND fr.IC_Current > 1.5 * b.avg_ic AND fr.UBC_Voltage < 0.8 * b.avg_ubc) OR 
      (fr.IC_Current > 1.5 * b.avg_ic AND fr.IA_Current > 1.5 * b.avg_ia AND fr.UCA_Voltage < 0.8 * b.avg_uca))
     AND fr.ReadingTimestamp > NOW() - INTERVAL '1 hour'
   GROUP BY fr.FTUKey, d.FTU_ID, time_bucket('1 minute', fr.ReadingTimestamp), fault_type;
   
   

4. 金属性短路

   • 特征：短路点电阻极小，短路电流极大
   • SQL查询:

   WITH baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IA_Current) AS p95_ia,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IB_Current) AS p95_ib,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IC_Current) AS p95_ic
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '7 day' AND NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey
   )
   SELECT 
     fr.FTUKey, 
     d.FTU_ID, 
     fr.ReadingTimestamp,
     'Metal Short Circuit' AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN baseline b ON fr.FTUKey = b.FTUKey
   WHERE 
     ((fr.IA_Current > 3 * b.p95_ia) OR 
      (fr.IB_Current > 3 * b.p95_ib) OR 
      (fr.IC_Current > 3 * b.p95_ic))
     AND ((fr.UAB_Voltage < 0.3 * 380) OR (fr.UBC_Voltage < 0.3 * 380) OR (fr.UCA_Voltage < 0.3 * 380))
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

5. 电弧性短路

   • 特征：短路点形成电弧，电流波动，电流波形畸变
   • SQL查询 (检测快速波动):

   WITH current_variance AS (
     SELECT 
       FTUKey,
       time_bucket('1 minute', ReadingTimestamp) AS bucket_time,
       VARIANCE(IA_Current) AS var_ia,
       VARIANCE(IB_Current) AS var_ib,
       VARIANCE(IC_Current) AS var_ic
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('1 minute', ReadingTimestamp)
   ),
   baseline_variance AS (
     SELECT 
       FTUKey,
       AVG(var_ia) AS avg_var_ia,
       AVG(var_ib) AS avg_var_ib,
       AVG(var_ic) AS avg_var_ic
     FROM (
       SELECT 
         FTUKey,
         time_bucket('1 minute', ReadingTimestamp) AS bucket_time,
         VARIANCE(IA_Current) AS var_ia,
         VARIANCE(IB_Current) AS var_ib,
         VARIANCE(IC_Current) AS var_ic
       FROM FactPowerReadings
       WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '24 hours' AND NOW() - INTERVAL '1 hour'
       GROUP BY FTUKey, time_bucket('1 minute', ReadingTimestamp)
     ) t
     GROUP BY FTUKey
   )
   SELECT 
     cv.FTUKey, 
     d.FTU_ID, 
     cv.bucket_time AS detection_time,
     'Arc Short Circuit' AS fault_type
   FROM current_variance cv
   JOIN DimFTU d ON cv.FTUKey = d.FTUKey
   JOIN baseline_variance bv ON cv.FTUKey = bv.FTUKey
   WHERE 
     (cv.var_ia > 5 * bv.avg_var_ia OR cv.var_ib > 5 * bv.avg_var_ib OR cv.var_ic > 5 * bv.avg_var_ic)
     AND (cv.var_ia > 0.05 OR cv.var_ib > 0.05 OR cv.var_ic > 0.05);
   
   

接地故障

1. 单相接地

   • 特征：一相电压降低，零序电流增大
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp,
          CASE 
            WHEN fr.UA_Voltage < threshold_volt THEN 'A相接地'
            WHEN fr.UB_Voltage < threshold_volt THEN 'B相接地'
            WHEN fr.UC_Voltage < threshold_volt THEN 'C相接地'
          END AS fault_phase
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   CROSS JOIN (
     SELECT 0.7 * PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY UA_Voltage) AS threshold_volt
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '24 hours'
   ) thresholds
   WHERE 
     fr.I0_Current > 10 AND -- 零序电流阈值通常由系统设置，这里假设为10A
     (fr.UA_Voltage < thresholds.threshold_volt OR 
      fr.UB_Voltage < thresholds.threshold_volt OR 
      fr.UC_Voltage < thresholds.threshold_volt)
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

2. 低阻接地

   • 特征：接地电流较大，接地相电压接近零
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp,
          CASE 
            WHEN fr.UA_Voltage < 0.3 * baseline.avg_ua THEN 'A相低阻接地'
            WHEN fr.UB_Voltage < 0.3 * baseline.avg_ub THEN 'B相低阻接地'
            WHEN fr.UC_Voltage < 0.3 * baseline.avg_uc THEN 'C相低阻接地'
          END AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN (
     SELECT 
       FTUKey,
       AVG(UA_Voltage) AS avg_ua,
       AVG(UB_Voltage) AS avg_ub,
       AVG(UC_Voltage) AS avg_uc
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '1 day' AND NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey
   ) baseline ON fr.FTUKey = baseline.FTUKey
   WHERE 
     fr.I0_Current > 50 AND -- 低阻接地零序电流阈值较高
     (fr.UA_Voltage < 0.3 * baseline.avg_ua OR 
      fr.UB_Voltage < 0.3 * baseline.avg_ub OR 
      fr.UC_Voltage < 0.3 * baseline.avg_uc)
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

3. 高阻接地

   • 特征：接地电流较小，电压略有下降
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp,
          CASE 
            WHEN fr.UA_Voltage < 0.9 * baseline.avg_ua AND fr.UA_Voltage > 0.7 * baseline.avg_ua THEN 'A相高阻接地'
            WHEN fr.UB_Voltage < 0.9 * baseline.avg_ub AND fr.UB_Voltage > 0.7 * baseline.avg_ub THEN 'B相高阻接地'
            WHEN fr.UC_Voltage < 0.9 * baseline.avg_uc AND fr.UC_Voltage > 0.7 * baseline.avg_uc THEN 'C相高阻接地'
          END AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN (
     SELECT 
       FTUKey,
       AVG(UA_Voltage) AS avg_ua,
       AVG(UB_Voltage) AS avg_ub,
       AVG(UC_Voltage) AS avg_uc
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '1 day' AND NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey
   ) baseline ON fr.FTUKey = baseline.FTUKey
   WHERE 
     fr.I0_Current BETWEEN 5 AND 20 AND -- 高阻接地零序电流阈值较低
     (
       (fr.UA_Voltage < 0.9 * baseline.avg_ua AND fr.UA_Voltage > 0.7 * baseline.avg_ua) OR 
       (fr.UB_Voltage < 0.9 * baseline.avg_ub AND fr.UB_Voltage > 0.7 * baseline.avg_ub) OR 
       (fr.UC_Voltage < 0.9 * baseline.avg_uc AND fr.UC_Voltage > 0.7 * baseline.avg_uc)
     )
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

4. 间歇性接地

   • 特征：接地状态不稳定，零序电流波动
   • SQL查询:

   WITH zero_current_variance AS (
     SELECT 
       FTUKey,
       time_bucket('5 minute', ReadingTimestamp) AS bucket_time,
       VARIANCE(I0_Current) AS var_i0,
       MAX(I0_Current) AS max_i0,
       COUNT(*) AS readings_count
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('5 minute', ReadingTimestamp)
     HAVING COUNT(*) > 10 -- 确保有足够的数据点进行分析
   )
   SELECT 
     zv.FTUKey, 
     d.FTU_ID, 
     zv.bucket_time,
     'Intermittent Ground Fault' AS fault_type
   FROM zero_current_variance zv
   JOIN DimFTU d ON zv.FTUKey = d.FTUKey
   WHERE 
     zv.var_i0 > 25 AND -- 零序电流方差较大
     zv.max_i0 > 10;  -- 最大零序电流超过阈值
   
   

5. 中性点接地系统故障

   • 特征：中性点接地阻抗异常，系统不平衡
   • SQL查询（检测三相电压不平衡和零序电流异常）:

   WITH voltage_imbalance AS (
     SELECT 
       FTUKey,
       ReadingTimestamp,
       (ABS(UA_Voltage - UB_Voltage) + ABS(UB_Voltage - UC_Voltage) + ABS(UC_Voltage - UA_Voltage)) / 
       (UA_Voltage + UB_Voltage + UC_Voltage) * 100 AS imbalance_percent,
       I0_Current
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '4 hours'
   )
   SELECT 
     vi.FTUKey, 
     d.FTU_ID, 
     time_bucket('15 minute', vi.ReadingTimestamp) AS detection_time,
     AVG(vi.imbalance_percent) AS avg_imbalance,
     AVG(vi.I0_Current) AS avg_zero_current,
     'Neutral Point Ground System Fault' AS fault_type
   FROM voltage_imbalance vi
   JOIN DimFTU d ON vi.FTUKey = d.FTUKey
   WHERE vi.imbalance_percent > 5 AND vi.I0_Current > 3
   GROUP BY vi.FTUKey, d.FTU_ID, time_bucket('15 minute', vi.ReadingTimestamp)
   HAVING AVG(vi.imbalance_percent) > 5;
   
   

失压故障

1. 全电压失压

   • 特征：系统电压完全消失
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp, 'Complete Voltage Loss' AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   WHERE 
     (fr.UAB_Voltage < 30 OR fr.UAB_Voltage IS NULL) AND 
     (fr.UBC_Voltage < 30 OR fr.UBC_Voltage IS NULL) AND 
     (fr.UCA_Voltage < 30 OR fr.UCA_Voltage IS NULL) AND
     fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

2. 部分失压

   • 特征：部分相位或区域电压消失
   • SQL查询:

   SELECT fr.FTUKey, d.FTU_ID, fr.ReadingTimestamp,
          CASE 
            WHEN fr.UA_Voltage < 30 AND fr.UB_Voltage > 180 AND fr.UC_Voltage > 180 THEN 'A相失压'
            WHEN fr.UB_Voltage < 30 AND fr.UA_Voltage > 180 AND fr.UC_Voltage > 180 THEN 'B相失压'
            WHEN fr.UC_Voltage < 30 AND fr.UA_Voltage > 180 AND fr.UB_Voltage > 180 THEN 'C相失压'
            WHEN fr.UA_Voltage < 30 AND fr.UB_Voltage < 30 AND fr.UC_Voltage > 180 THEN 'A-B相失压'
            WHEN fr.UB_Voltage < 30 AND fr.UC_Voltage < 30 AND fr.UA_Voltage > 180 THEN 'B-C相失压'
            WHEN fr.UC_Voltage < 30 AND fr.UA_Voltage < 30 AND fr.UB_Voltage > 180 THEN 'C-A相失压'
          END AS fault_type
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   WHERE 
     ((fr.UA_Voltage < 30 AND fr.UB_Voltage > 180 AND fr.UC_Voltage > 180) OR
      (fr.UB_Voltage < 30 AND fr.UA_Voltage > 180 AND fr.UC_Voltage > 180) OR
      (fr.UC_Voltage < 30 AND fr.UA_Voltage > 180 AND fr.UB_Voltage > 180) OR
      (fr.UA_Voltage < 30 AND fr.UB_Voltage < 30 AND fr.UC_Voltage > 180) OR
      (fr.UB_Voltage < 30 AND fr.UC_Voltage < 30 AND fr.UA_Voltage > 180) OR
      (fr.UC_Voltage < 30 AND fr.UA_Voltage < 30 AND fr.UB_Voltage > 180))
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

3. 电压骤降

   • 特征：电压短时间内显著降低
   • SQL查询:

   WITH voltage_baseline AS (
     SELECT 
       FTUKey,
       AVG(UAB_Voltage) AS avg_uab,
       AVG(UBC_Voltage) AS avg_ubc,
       AVG(UCA_Voltage) AS avg_uca
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '24 hours' AND NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey
   )
   SELECT 
     fr.FTUKey, 
     d.FTU_ID, 
     fr.ReadingTimestamp,
     'Voltage Sag' AS fault_type,
     ROUND((1 - LEAST(fr.UAB_Voltage/vb.avg_uab, fr.UBC_Voltage/vb.avg_ubc, fr.UCA_Voltage/vb.avg_uca)) * 100, 2) AS sag_percent
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN voltage_baseline vb ON fr.FTUKey = vb.FTUKey
   WHERE 
     (fr.UAB_Voltage < 0.9 * vb.avg_uab OR fr.UBC_Voltage < 0.9 * vb.avg_ubc OR fr.UCA_Voltage < 0.9 * vb.avg_uca) AND
     (fr.UAB_Voltage > 0.1 * vb.avg_uab AND fr.UBC_Voltage > 0.1 * vb.avg_ubc AND fr.UCA_Voltage > 0.1 * vb.avg_uca) AND
     fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

4. 欠电压

   • 特征：持续的电压低于标准值
   • SQL查询:

   WITH sustained_undervoltage AS (
     SELECT 
       FTUKey,
       time_bucket('5 minute', ReadingTimestamp) AS bucket_time,
       AVG(UAB_Voltage) AS avg_uab,
       AVG(UBC_Voltage) AS avg_ubc,
       AVG(UCA_Voltage) AS avg_uca,
       COUNT(*) AS readings_count
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('5 minute', ReadingTimestamp)
     HAVING COUNT(*) > 10 -- 确保有足够的数据点进行分析
   )
   SELECT 
     su.FTUKey, 
     d.FTU_ID, 
     su.bucket_time,
     'Sustained Undervoltage' AS fault_type,
     LEAST(su.avg_uab, su.avg_ubc, su.avg_uca) AS min_voltage
   FROM sustained_undervoltage su
   JOIN DimFTU d ON su.FTUKey = d.FTUKey
   WHERE 
     su.avg_uab < 0.9 * 380 AND
     su.avg_ubc < 0.9 * 380 AND
     su.avg_uca < 0.9 * 380;
   
   

5. 电压不平衡

   • 特征：三相电压幅值或相位不平衡
   • SQL查询:

   WITH voltage_imbalance AS (
     SELECT 
       FTUKey,
       time_bucket('5 minute', ReadingTimestamp) AS bucket_time,
       (MAX(GREATEST(UA_Voltage, UB_Voltage, UC_Voltage)) - MIN(LEAST(UA_Voltage, UB_Voltage, UC_Voltage))) / 
        NULLIF(AVG(GREATEST(UA_Voltage, UB_Voltage, UC_Voltage)), 0) * 100 AS imbalance_percent,
       COUNT(*) AS readings_count
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('5 minute', ReadingTimestamp)
     HAVING COUNT(*) > 10 -- 确保有足够的数据点进行分析
   )
   SELECT 
     vi.FTUKey, 
     d.FTU_ID, 
     vi.bucket_time,
     'Voltage Imbalance' AS fault_type,
     vi.imbalance_percent
   FROM voltage_imbalance vi
   JOIN DimFTU d ON vi.FTUKey = d.FTUKey
   WHERE vi.imbalance_percent > 2; -- 电压不平衡超过2%
   
   

过载故障

1. 持续过载

   • 特征：长时间电流超过额定值
   • SQL查询:

   WITH current_baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IA_Current) AS p95_ia,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IB_Current) AS p95_ib,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY IC_Current) AS p95_ic
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '1 day'
     GROUP BY FTUKey
   ),
   sustained_overload AS (
     SELECT 
       fr.FTUKey,
       time_bucket('15 minute', fr.ReadingTimestamp) AS bucket_time,
       AVG(fr.IA_Current) AS avg_ia,
       AVG(fr.IB_Current) AS avg_ib,
       AVG(fr.IC_Current) AS avg_ic,
       cb.p95_ia AS threshold_ia,
       cb.p95_ib AS threshold_ib,
       cb.p95_ic AS threshold_ic
     FROM FactPowerReadings fr
     JOIN current_baseline cb ON fr.FTUKey = cb.FTUKey
     WHERE fr.ReadingTimestamp > NOW() - INTERVAL '4 hours'
     GROUP BY fr.FTUKey, time_bucket('15 minute', fr.ReadingTimestamp), cb.p95_ia, cb.p95_ib, cb.p95_ic
   )
   SELECT 
     so.FTUKey, 
     d.FTU_ID, 
     so.bucket_time,
     'Sustained Overload' AS fault_type,
     GREATEST(
       so.avg_ia / NULLIF(so.threshold_ia, 0), 
       so.avg_ib / NULLIF(so.threshold_ib, 0), 
       so.avg_ic / NULLIF(so.threshold_ic, 0)
     ) * 100 AS overload_percent
   FROM sustained_overload so
   JOIN DimFTU d ON so.FTUKey = d.FTUKey
   WHERE 
     (so.avg_ia > 1.2 * so.threshold_ia OR 
      so.avg_ib > 1.2 * so.threshold_ib OR 
      so.avg_ic > 1.2 * so.threshold_ic);
   
   

2. 短时过载

   • 特征：短时间内电流显著超过额定值
   • SQL查询:

   WITH current_baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY IA_Current) AS p99_ia,
       PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY IB_Current) AS p99_ib,
       PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY IC_Current) AS p99_ic
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '1 day'
     GROUP BY FTUKey
   )
   SELECT 
     fr.FTUKey, 
     d.FTU_ID, 
     fr.ReadingTimestamp,
     'Short-term Overload' AS fault_type,
     GREATEST(
       fr.IA_Current / NULLIF(cb.p99_ia, 0), 
       fr.IB_Current / NULLIF(cb.p99_ib, 0), 
       fr.IC_Current / NULLIF(cb.p99_ic, 0)
     ) * 100 AS overload_percent
   FROM FactPowerReadings fr
   JOIN DimFTU d ON fr.FTUKey = d.FTUKey
   JOIN current_baseline cb ON fr.FTUKey = cb.FTUKey
   WHERE 
     (fr.IA_Current > 1.5 * cb.p99_ia OR 
      fr.IB_Current > 1.5 * cb.p99_ib OR 
      fr.IC_Current > 1.5 * cb.p99_ic)
     AND fr.ReadingTimestamp > NOW() - INTERVAL '15 minutes';
   
   

3. 变压器过载

   • 特征：变压器负载超过额定容量
   • SQL查询 (基于功率值判断):

   WITH power_baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY ApparentPower_Total) AS p95_power
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '1 day'
     GROUP BY FTUKey
   ),
   sustained_power_overload AS (
     SELECT 
       fr.FTUKey,
       time_bucket('15 minute', fr.ReadingTimestamp) AS bucket_time,
       AVG(fr.ApparentPower_Total) AS avg_power,
       pb.p95_power AS threshold_power
     FROM FactPowerReadings fr
     JOIN power_baseline pb ON fr.FTUKey = pb.FTUKey
     WHERE fr.ReadingTimestamp > NOW() - INTERVAL '2 hours'
     GROUP BY fr.FTUKey, time_bucket('15 minute', fr.ReadingTimestamp), pb.p95_power
   )
   SELECT 
     spo.FTUKey, 
     d.FTU_ID, 
     spo.bucket_time,
     'Transformer Overload' AS fault_type,
     (spo.avg_power / NULLIF(spo.threshold_power, 0)) * 100 AS overload_percent
   FROM sustained_power_overload spo
   JOIN DimFTU d ON spo.FTUKey = d.FTUKey
   JOIN DimXTU x ON d.XTUKey = x.XTUKey
   WHERE 
     spo.avg_power > 1.1 * spo.threshold_power;
   
   

4. 线路过载

   • 特征：线路电流超过安全载流量
   • SQL查询:

   WITH current_max AS (
     SELECT 
       FTUKey,
       time_bucket('10 minute', ReadingTimestamp) AS bucket_time,
       MAX(GREATEST(IA_Current, IB_Current, IC_Current)) AS max_current
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('10 minute', ReadingTimestamp)
   ),
   current_baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY GREATEST(IA_Current, IB_Current, IC_Current)) AS safe_current_limit
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '1 day'
     GROUP BY FTUKey
   )
   SELECT 
     cm.FTUKey, 
     d.FTU_ID, 
     cm.bucket_time,
     'Line Overload' AS fault_type,
     (cm.max_current / NULLIF(cb.safe_current_limit, 0)) * 100 AS overload_percent
   FROM current_max cm
   JOIN DimFTU d ON cm.FTUKey = d.FTUKey
   JOIN current_baseline cb ON cm.FTUKey = cb.FTUKey
   WHERE cm.max_current > 1.2 * cb.safe_current_limit;
   
   

5. 开关设备过载

   • 特征：开关电流超过额定值
   • SQL查询 (基于电流波动和最大值):

   WITH switch_load AS (
     SELECT 
       FTUKey,
       time_bucket('15 minute', ReadingTimestamp) AS bucket_time,
       MAX(GREATEST(IA_Current, IB_Current, IC_Current)) AS max_current,
       VARIANCE(GREATEST(IA_Current, IB_Current, IC_Current)) AS current_variance
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '2 hours'
     GROUP BY FTUKey, time_bucket('15 minute', ReadingTimestamp)
   ),
   current_baseline AS (
     SELECT 
       FTUKey,
       PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY GREATEST(IA_Current, IB_Current, IC_Current)) AS p99_current
     FROM FactPowerReadings
     WHERE ReadingTimestamp BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '1 day'
     GROUP BY FTUKey
   )
   SELECT 
     sl.FTUKey, 
     d.FTU_ID, 
     sl.bucket_time,
     'Switch Equipment Overload' AS fault_type,
     (sl.max_current / NULLIF(cb.p99_current, 0)) * 100 AS overload_percent
   FROM switch_load sl
   JOIN DimFTU d ON sl.FTUKey = d.FTUKey
   JOIN current_baseline cb ON sl.FTUKey = cb.FTUKey
   WHERE 
     sl.max_current > 1.3 * cb.p99_current AND
     sl.current_variance > 50; -- 电流波动较大，可能是开关负载状态变化
   
   

断路故障

1. 导线断线

   • 特征：电力线路物理断开
   • SQL查询:

   WITH phase_current_status AS (
     SELECT 
       FTUKey,
       ReadingTimestamp,
       CASE WHEN IA_Current < 1 THEN TRUE ELSE FALSE END AS phase_a_zero,
       CASE WHEN IB_Current < 1 THEN TRUE ELSE FALSE END AS phase_b_zero,
       CASE WHEN IC_Current < 1 THEN TRUE ELSE FALSE END AS phase_c_zero,
       CASE 
         WHEN IA_Current < 1 AND IB_Current > 5 AND IC_Current > 5 THEN 'A相断线'
         WHEN IB_Current < 1 AND IA_Current > 5 AND IC_Current > 5 THEN 'B相断线'
         WHEN IC_Current < 1 AND IA_Current > 5 AND IB_Current > 5 THEN 'C相断线'
       END AS fault_phase
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '15 minutes'
   )
   SELECT 
     pcs.FTUKey, 
     d.FTU_ID, 
     pcs.ReadingTimestamp,
     pcs.fault_phase AS fault_type
   FROM phase_current_status pcs
   JOIN DimFTU d ON pcs.FTUKey = d.FTUKey
   WHERE 
     (pcs.phase_a_zero = TRUE AND pcs.phase_b_zero = FALSE AND pcs.phase_c_zero = FALSE) OR
     (pcs.phase_a_zero = FALSE AND pcs.phase_b_zero = TRUE AND pcs.phase_c_zero = FALSE) OR
     (pcs.phase_a_zero = FALSE AND pcs.phase_b_zero = FALSE AND pcs.phase_c_zero = TRUE);
   
   

2. 接头断开

   • 特征：连接点断开或接触不良
   • SQL查询 (检测电流不稳定和突变):

   WITH current_stability AS (
     SELECT 
       FTUKey,
       time_bucket('5 minute', ReadingTimestamp) AS bucket_time,
       AVG(IA_Current) AS avg_ia,
       AVG(IB_Current) AS avg_ib,
       AVG(IC_Current) AS avg_ic,
       VARIANCE(IA_Current) AS var_ia,
       VARIANCE(IB_Current) AS var_ib,
       VARIANCE(IC_Current) AS var_ic,
       MAX(IA_Current) / NULLIF(AVG(IA_Current), 0) AS max_avg_ratio_a,
       MAX(IB_Current) / NULLIF(AVG(IB_Current), 0) AS max_avg_ratio_b,
       MAX(IC_Current) / NULLIF(AVG(IC_Current), 0) AS max_avg_ratio_c
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '1 hour'
     GROUP BY FTUKey, time_bucket('5 minute', ReadingTimestamp)
   )
   SELECT 
     cs.FTUKey, 
     d.FTU_ID, 
     cs.bucket_time,
     CASE 
       WHEN cs.var_ia > 50 AND cs.max_avg_ratio_a > 2 THEN 'A相接头故障'
       WHEN cs.var_ib > 50 AND cs.max_avg_ratio_b > 2 THEN 'B相接头故障'
       WHEN cs.var_ic > 50 AND cs.max_avg_ratio_c > 2 THEN 'C相接头故障'
       ELSE 'Unknown Connection Fault'
     END AS fault_type
   FROM current_stability cs
   JOIN DimFTU d ON cs.FTUKey = d.FTUKey
   WHERE 
     (cs.var_ia > 50 AND cs.max_avg_ratio_a > 2) OR
     (cs.var_ib > 50 AND cs.max_avg_ratio_b > 2) OR
     (cs.var_ic > 50 AND cs.max_avg_ratio_c > 2);
   
   

3. 开关拒动

   • 特征：断路器或隔离开关无法正常操作
   • SQL查询 (这需要结合操作指令与状态反馈判断，假设有电流变化但开关状态未变):

   -- 注意：此查询需要结合开关操作信息，这里仅提供框架
   -- 实际应用中需要增加开关操作记录表的关联
   WITH switch_operations AS (
     SELECT 
       FTUKey,
       operation_time,
       target_status
     FROM switch_operation_log -- 假设的操作记录表
     WHERE operation_time > NOW() - INTERVAL '24 hours'
   ),
   current_changes AS (
     SELECT 
       FTUKey,
       ReadingTimestamp,
       LAG(GREATEST(IA_Current, IB_Current, IC_Current)) OVER (
         PARTITION BY FTUKey ORDER BY ReadingTimestamp
       ) AS previous_current,
       GREATEST(IA_Current, IB_Current, IC_Current) AS current_current,
       ABS(GREATEST(IA_Current, IB_Current, IC_Current) - LAG(GREATEST(IA_Current, IB_Current, IC_Current)) OVER (
         PARTITION BY FTUKey ORDER BY ReadingTimestamp
       )) / NULLIF(LAG(GREATEST(IA_Current, IB_Current, IC_Current)) OVER (
         PARTITION BY FTUKey ORDER BY ReadingTimestamp
       ), 0) * 100 AS current_change_percent
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '24 hours'
   )
   SELECT 
     so.FTUKey, 
     d.FTU_ID, 
     so.operation_time,
     'Switch Refusal to Operate' AS fault_type
   FROM switch_operations so
   JOIN DimFTU d ON so.FTUKey = d.FTUKey
   LEFT JOIN current_changes cc ON 
     so.FTUKey = cc.FTUKey AND 
     cc.ReadingTimestamp BETWEEN so.operation_time AND so.operation_time + INTERVAL '5 minutes'
   WHERE 
     (so.target_status = 'Open' AND cc.current_change_percent < 50) OR
     (so.target_status = 'Close' AND cc.current_change_percent < 50);
   
   

4. 熔断器熔断

   • 特征：熔断器动作，电路断开
   • SQL查询:

   WITH current_drop AS (
     SELECT 
       FTUKey,
       ReadingTimestamp,
       LAG(IA_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) AS prev_ia,
       LAG(IB_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) AS prev_ib,
       LAG(IC_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) AS prev_ic,
       IA_Current,
       IB_Current,
       IC_Current,
       CASE 
         WHEN LAG(IA_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) > 5 
              AND IA_Current < 1 THEN 'A相熔断器熔断'
         WHEN LAG(IB_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) > 5 
              AND IB_Current < 1 THEN 'B相熔断器熔断'
         WHEN LAG(IC_Current) OVER (PARTITION BY FTUKey ORDER BY ReadingTimestamp) > 5 
              AND IC_Current < 1 THEN 'C相熔断器熔断'
       END AS fault_type
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '15 minutes'
   )
   SELECT 
     cd.FTUKey, 
     d.FTU_ID, 
     cd.ReadingTimestamp,
     cd.fault_type
   FROM current_drop cd
   JOIN DimFTU d ON cd.FTUKey = d.FTUKey
   WHERE 
     (cd.prev_ia > 5 AND cd.IA_Current < 1) OR
     (cd.prev_ib > 5 AND cd.IB_Current < 1) OR
     (cd.prev_ic > 5 AND cd.IC_Current < 1);
   
   

5. 机械性断路

   • 特征：机械原因导致的电路断开
   • SQL查询 (基于电流、电压的异常组合模式):

   WITH circuit_break_pattern AS (
     SELECT 
       FTUKey,
       ReadingTimestamp,
       CASE 
         WHEN IA_Current < 1 AND IB_Current < 1 AND IC_Current < 1 
              AND (UAB_Voltage > 300 OR UBC_Voltage > 300 OR UCA_Voltage > 300)
         THEN 'Mechanical Circuit Break'
       END AS fault_type
     FROM FactPowerReadings
     WHERE ReadingTimestamp > NOW() - INTERVAL '15 minutes'
   )
   SELECT 
     cbp.FTUKey, 
     d.FTU_ID, 
     cbp.ReadingTimestamp,
     cbp.fault_type
   FROM circuit_break_pattern cbp
   JOIN DimFTU d ON cbp.FTUKey = d.FTUKey
   WHERE cbp.fault_type IS NOT NULL;
   
   

综合故障检测查询

为了整合所有故障类型的检测，可以创建一个综合故障检测存储过程或视图，例如：

CREATE OR REPLACE VIEW fault_detection_results AS
-- 短路故障检测
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Short Circuit' AS main_category 
FROM three_phase_short_circuit_detection
UNION ALL
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Short Circuit' AS main_category 
FROM two_phase_short_circuit_detection
UNION ALL
-- 接地故障检测  
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Ground Fault' AS main_category 
FROM single_phase_ground_fault_detection
UNION ALL
-- 失压故障检测
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Voltage Loss' AS main_category 
FROM voltage_loss_detection
UNION ALL
-- 过载故障检测
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Overload' AS main_category 
FROM overload_detection
UNION ALL
-- 断路故障检测
SELECT FTUKey, FTU_ID, detection_time, fault_type, 'Circuit Break' AS main_category 
FROM circuit_break_detection;

实时监控建议

1. 创建一个定时作业，每分钟或每5分钟执行上述查询，将结果写入故障记录表
2. 实现阈值自适应机制，基于历史数据动态调整检测阈值
3. 对检测到的故障进行分级处理，根据严重程度触发不同级别的告警
4. 建立故障关联分析，识别可能的故障传播路径

这些SQL查询提供了基于遥测数据识别各类故障的基础框架。根据实际情况，可能需要调整阈值、时间窗口和判断逻辑，以提高故障检测的准确率和可靠性。