以下是一系列可直接添加到您的Metabase BI平台的SQL查询，用于长期监控设备健康状况。这些查询基于您的TimescaleDB数据仓库设计，并针对不同的监控需求进行了优化。
-- 设备电气参数异常统计（过去24小时）
WITH parameter_limits AS (
SELECT
'UA_Voltage' AS param, 5.79 AS lower_limit, 6.10 AS upper_limit
UNION ALL
SELECT 'UB_Voltage', 5.70, 5.94
UNION ALL
SELECT 'UC_Voltage', 5.75, 5.99
UNION ALL
SELECT 'UAB_Voltage', 9.78, 10.19
UNION ALL
SELECT 'UBC_Voltage', 9.77, 10.17
UNION ALL
SELECT 'IA_Current', 6.62, 19.23
UNION ALL
SELECT 'IB_Current', 5.51, 16.95
UNION ALL
SELECT 'Power_COS', 0.68, 1.0
),
abnormal_readings AS (
SELECT
f.ftukey,
dt.hour,
CASE
WHEN f.ua_voltage < (SELECT lower_limit FROM parameter_limits WHERE param = 'UA_Voltage')
OR f.ua_voltage > (SELECT upper_limit FROM parameter_limits WHERE param = 'UA_Voltage')
THEN 1 ELSE 0
END AS ua_abnormal,
CASE
WHEN f.ub_voltage < (SELECT lower_limit FROM parameter_limits WHERE param = 'UB_Voltage')
OR f.ub_voltage > (SELECT upper_limit FROM parameter_limits WHERE param = 'UB_Voltage')
THEN 1 ELSE 0
END AS ub_abnormal,
CASE
WHEN f.uc_voltage < (SELECT lower_limit FROM parameter_limits WHERE param = 'UC_Voltage')
OR f.uc_voltage > (SELECT upper_limit FROM parameter_limits WHERE param = 'UC_Voltage')
THEN 1 ELSE 0
END AS uc_abnormal,
CASE
WHEN f.uab_voltage < (SELECT lower_limit FROM parameter_limits WHERE param = 'UAB_Voltage')
OR f.uab_voltage > (SELECT upper_limit FROM parameter_limits WHERE param = 'UAB_Voltage')
THEN 1 ELSE 0
END AS uab_abnormal,
CASE
WHEN f.ubc_voltage < (SELECT lower_limit FROM parameter_limits WHERE param = 'UBC_Voltage')
OR f.ubc_voltage > (SELECT upper_limit FROM parameter_limits WHERE param = 'UBC_Voltage')
THEN 1 ELSE 0
END AS ubc_abnormal,
CASE
WHEN f.ia_current < (SELECT lower_limit FROM parameter_limits WHERE param = 'IA_Current')
OR f.ia_current > (SELECT upper_limit FROM parameter_limits WHERE param = 'IA_Current')
THEN 1 ELSE 0
END AS ia_abnormal,
CASE
WHEN f.ib_current < (SELECT lower_limit FROM parameter_limits WHERE param = 'IB_Current')
OR f.ib_current > (SELECT upper_limit FROM parameter_limits WHERE param = 'IB_Current')
THEN 1 ELSE 0
END AS ib_abnormal,
CASE
WHEN f.power_cos < (SELECT lower_limit FROM parameter_limits WHERE param = 'Power_COS')
OR f.power_cos > (SELECT upper_limit FROM parameter_limits WHERE param = 'Power_COS')
THEN 1 ELSE 0
END AS cos_abnormal
FROM factpowerreadings f
JOIN dimtime dt ON f.timestampkey = dt.timestampkey
WHERE f.readingtimestamp >= NOW() - INTERVAL '24 hours'
)
SELECT
ftukey AS "设备ID",
COUNT(*) AS "总读数",
SUM(ua_abnormal) AS "UA电压异常次数",
SUM(ub_abnormal) AS "UB电压异常次数",
SUM(uc_abnormal) AS "UC电压异常次数",
SUM(uab_abnormal) AS "UAB线电压异常次数",
SUM(ubc_abnormal) AS "UBC线电压异常次数",
SUM(ia_abnormal) AS "A相电流异常次数",
SUM(ib_abnormal) AS "B相电流异常次数",
SUM(cos_abnormal) AS "功率因数异常次数",
ROUND(100.0 * (SUM(ua_abnormal) + SUM(ub_abnormal) + SUM(uc_abnormal) +
SUM(uab_abnormal) + SUM(ubc_abnormal) + SUM(ia_abnormal) +
SUM(ib_abnormal) + SUM(cos_abnormal)) /
(COUNT(*) * 8), 2) AS "参数异常率(%)"
FROM abnormal_readings
GROUP BY ftukey
ORDER BY "参数异常率(%)" DESC;
-- 三相不平衡度趋势（按小时聚合）
WITH hourly_data AS (
SELECT
f.ftukey,
time_bucket('1 hour', f.readingtimestamp) AS hour_bucket,
AVG(f.ia_current) AS avg_ia,
AVG(f.ib_current) AS avg_ib,
AVG(f.ic_current) AS avg_ic,
AVG(f.ua_voltage) AS avg_ua,
AVG(f.ub_voltage) AS avg_ub,
AVG(f.uc_voltage) AS avg_uc
FROM factpowerreadings f
WHERE
f.readingtimestamp >= NOW() - INTERVAL '7 days'
AND f.ia_current > 0 AND f.ib_current > 0 AND f.ic_current > 0
AND f.ua_voltage > 0 AND f.ub_voltage > 0 AND f.uc_voltage > 0
GROUP BY f.ftukey, hour_bucket
)
SELECT
d.ftukey AS "设备ID",
df.devicename AS "设备名称",
d.hour_bucket AS "时间",
-- 电流不平衡度计算 (最大偏差/平均值)
ROUND(100.0 * GREATEST(
ABS(d.avg_ia - (d.avg_ia + d.avg_ib + d.avg_ic)/3),
ABS(d.avg_ib - (d.avg_ia + d.avg_ib + d.avg_ic)/3),
ABS(d.avg_ic - (d.avg_ia + d.avg_ib + d.avg_ic)/3)
) / ((d.avg_ia + d.avg_ib + d.avg_ic)/3), 2) AS "电流不平衡度(%)",
-- 电压不平衡度计算
ROUND(100.0 * GREATEST(
ABS(d.avg_ua - (d.avg_ua + d.avg_ub + d.avg_uc)/3),
ABS(d.avg_ub - (d.avg_ua + d.avg_ub + d.avg_uc)/3),
ABS(d.avg_uc - (d.avg_ua + d.avg_ub + d.avg_uc)/3)
) / ((d.avg_ua + d.avg_ub + d.avg_uc)/3), 2) AS "电压不平衡度(%)"
FROM hourly_data d
JOIN dimftu df ON d.ftukey = df.ftukey
ORDER BY hour_bucket DESC, "电流不平衡度(%)" DESC;
-- 设备健康综合评分（过去7天）
WITH parameter_stats AS (
SELECT
f.ftukey,
-- 三相电流不平衡指标
AVG(GREATEST(
ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3)
) / NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0)) AS avg_current_imbalance,
-- 三相电压不平衡指标
AVG(GREATEST(
ABS(f.ua_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.ub_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.uc_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3)
) / NULLIF(((f.ua_voltage + f.ub_voltage + f.uc_voltage)/3), 0)) AS avg_voltage_imbalance,
-- 功率因数稳定性
STDDEV(f.power_cos) AS cos_stability,
-- 参数超限率计算
SUM(CASE WHEN f.ua_voltage < 5.79 OR f.ua_voltage > 6.10 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ub_voltage < 5.70 OR f.ub_voltage > 5.94 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.uc_voltage < 5.75 OR f.uc_voltage > 5.99 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ia_current < 6.62 OR f.ia_current > 19.23 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ib_current < 5.51 OR f.ib_current > 16.95 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.power_cos < 0.68 OR f.power_cos > 1.0 THEN 1 ELSE 0 END) AS total_violations,
COUNT(*) * 6 AS total_checks -- 6个参数被检查
FROM factpowerreadings f
WHERE f.readingtimestamp >= NOW() - INTERVAL '7 days'
GROUP BY f.ftukey
)
SELECT
ps.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
df.substation AS "变电站",
df.powersupplyoffice AS "供电所",
-- 计算健康分数 (100分满分)
ROUND(100 - (
-- 三相不平衡分量(最多扣30分)
LEAST(30, ROUND(ps.avg_current_imbalance * 100)) -
-- 电压不平衡分量(最多扣30分)
LEAST(30, ROUND(ps.avg_voltage_imbalance * 100)) -
-- 功率因数不稳定性(最多扣20分)
LEAST(20, ROUND(ps.cos_stability * 50)) -
-- 参数超限率(最多扣20分)
LEAST(20, ROUND(20.0 * ps.total_violations / NULLIF(ps.total_checks, 0)))
), 2) AS "健康评分",
ROUND(100.0 * ps.total_violations / NULLIF(ps.total_checks, 0), 2) AS "参数超限率(%)",
ROUND(ps.avg_current_imbalance * 100, 2) AS "平均电流不平衡度(%)",
ROUND(ps.avg_voltage_imbalance * 100, 2) AS "平均电压不平衡度(%)"
FROM parameter_stats ps
JOIN dimftu df ON ps.ftukey = df.ftukey
ORDER BY "健康评分";
-- 短路故障预警检测（电压骤降+电流骤升）
WITH voltage_current_changes AS (
SELECT
f.ftukey,
f.readingtimestamp,
f.ua_voltage, f.ub_voltage, f.uc_voltage,
f.ia_current, f.ib_current, f.ic_current,
LAG(f.ua_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_ua,
LAG(f.ub_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_ub,
LAG(f.uc_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_uc,
LAG(f.ia_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_ia,
LAG(f.ib_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_ib,
LAG(f.ic_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) AS prev_ic,
-- 计算变化率
CASE WHEN LAG(f.ua_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) > 0
THEN (f.ua_voltage - LAG(f.ua_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp)) /
LAG(f.ua_voltage) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp)
ELSE 0 END AS ua_change_ratio,
CASE WHEN LAG(f.ia_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp) > 0
THEN (f.ia_current - LAG(f.ia_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp)) /
LAG(f.ia_current) OVER (PARTITION BY f.ftukey ORDER BY f.readingtimestamp)
ELSE 0 END AS ia_change_ratio
FROM factpowerreadings f
WHERE f.readingtimestamp >= NOW() - INTERVAL '3 days'
)
SELECT
vc.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
vc.readingtimestamp AS "时间",
ROUND(vc.ua_voltage, 2) AS "UA电压",
ROUND(vc.prev_ua, 2) AS "先前UA电压",
ROUND(vc.ua_change_ratio * 100, 2) AS "UA变化率(%)",
ROUND(vc.ub_voltage, 2) AS "UB电压",
ROUND(vc.uc_voltage, 2) AS "UC电压",
ROUND(vc.ia_current, 2) AS "IA电流",
ROUND(vc.prev_ia, 2) AS "先前IA电流",
ROUND(vc.ia_change_ratio * 100, 2) AS "IA变化率(%)",
CASE
WHEN (vc.ua_voltage/NULLIF(vc.prev_ua,0) < 0.7 AND vc.ub_voltage/NULLIF(vc.prev_ub,0) < 0.7 AND vc.uc_voltage/NULLIF(vc.prev_uc,0) < 0.7)
AND (vc.ia_current/NULLIF(vc.prev_ia,0) > 2 OR vc.ib_current/NULLIF(vc.prev_ib,0) > 2 OR vc.ic_current/NULLIF(vc.prev_ic,0) > 2)
THEN 'SC01-可能三相短路'
WHEN ((vc.ua_voltage/NULLIF(vc.prev_ua,0) < 0.7 AND vc.ub_voltage/NULLIF(vc.prev_ub,0) < 0.7) OR
(vc.ub_voltage/NULLIF(vc.prev_ub,0) < 0.7 AND vc.uc_voltage/NULLIF(vc.prev_uc,0) < 0.7) OR
(vc.ua_voltage/NULLIF(vc.prev_ua,0) < 0.7 AND vc.uc_voltage/NULLIF(vc.prev_uc,0) < 0.7))
AND (vc.ia_current/NULLIF(vc.prev_ia,0) > 2 OR vc.ib_current/NULLIF(vc.prev_ib,0) > 2 OR vc.ic_current/NULLIF(vc.prev_ic,0) > 2)
THEN 'SC02-可能两相短路'
WHEN (vc.ua_voltage/NULLIF(vc.prev_ua,0) < 0.7 OR vc.ub_voltage/NULLIF(vc.prev_ub,0) < 0.7 OR vc.uc_voltage/NULLIF(vc.prev_uc,0) < 0.7)
AND (vc.ia_current/NULLIF(vc.prev_ia,0) > 2 OR vc.ib_current/NULLIF(vc.prev_ib,0) > 2 OR vc.ic_current/NULLIF(vc.prev_ic,0) > 2)
THEN 'SC03-可能相间短路'
ELSE '正常'
END AS "故障预警"
FROM voltage_current_changes vc
JOIN dimftu df ON vc.ftukey = df.ftukey
WHERE
(vc.ua_voltage/NULLIF(vc.prev_ua,0) < 0.8 OR vc.ub_voltage/NULLIF(vc.prev_ub,0) < 0.8 OR vc.uc_voltage/NULLIF(vc.prev_uc,0) < 0.8)
OR (vc.ia_current/NULLIF(vc.prev_ia,0) > 1.5 OR vc.ib_current/NULLIF(vc.prev_ib,0) > 1.5 OR vc.ic_current/NULLIF(vc.prev_ic,0) > 1.5)
ORDER BY vc.readingtimestamp DESC;
-- 接地故障监测（零序电流异常）
SELECT
f.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
f.readingtimestamp AS "时间",
ROUND(f.i0_current, 3) AS "零序电流",
ROUND(f.ia_current, 2) AS "A相电流",
ROUND(f.ib_current, 2) AS "B相电流",
ROUND(f.ic_current, 2) AS "C相电流",
-- 三相电流矢量和，理论上应接近零序电流
ROUND(SQRT(POWER(f.ia_current, 2) + POWER(f.ib_current, 2) + POWER(f.ic_current, 2) -
f.ia_current*f.ib_current - f.ia_current*f.ic_current - f.ib_current*f.ic_current), 3) AS "三相不平衡分量",
CASE
WHEN f.i0_current > 0.5 AND f.i0_current/NULLIF((f.ia_current + f.ib_current + f.ic_current)/3, 0) > 0.2
THEN 'GF01-可能单相接地'
WHEN f.i0_current > 0.2 AND f.i0_current/NULLIF((f.ia_current + f.ib_current + f.ic_current)/3, 0) > 0.1
THEN 'GF03-可能高阻接地'
ELSE '正常'
END AS "接地故障预警"
FROM factpowerreadings f
JOIN dimftu df ON f.ftukey = df.ftukey
WHERE
f.readingtimestamp >= NOW() - INTERVAL '7 days'
AND f.i0_current > 0.1 -- 零序电流阈值
ORDER BY f.i0_current DESC
LIMIT 1000;
-- 设备过载监测
WITH load_stats AS (
SELECT
f.ftukey,
-- 按小时分组计算负载指标
time_bucket('1 hour', f.readingtimestamp) AS hour_bucket,
AVG(f.activePower_total) AS avg_active_power,
MAX(f.activePower_total) AS max_active_power,
MIN(f.activePower_total) AS min_active_power,
-- 计算设备额定功率的估计值 (基于历史数据)
PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY f.activePower_total) AS rated_power_est
FROM factpowerreadings f
WHERE
f.readingtimestamp >= NOW() - INTERVAL '30 days'
AND f.activePower_total > 0
GROUP BY f.ftukey, hour_bucket
)
SELECT
ls.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
ls.hour_bucket AS "时间",
ROUND(ls.avg_active_power, 2) AS "平均功率(kW)",
ROUND(ls.max_active_power, 2) AS "最大功率(kW)",
ROUND(ls.rated_power_est, 2) AS "参考额定功率(kW)",
ROUND(ls.max_active_power / NULLIF(ls.rated_power_est, 0) * 100, 2) AS "负载率(%)",
CASE
WHEN ls.max_active_power > ls.rated_power_est * 1.3 THEN 'OL01-持续过载'
WHEN ls.max_active_power > ls.rated_power_est * 1.1 THEN 'OL02-轻度过载'
ELSE '正常负载'
END AS "负载状态"
FROM load_stats ls
JOIN dimftu df ON ls.ftukey = df.ftukey
WHERE ls.max_active_power > ls.rated_power_est * 1.05 -- 显示负载超过额定105%的情况
ORDER BY "负载率(%)" DESC;
-- 关键参数稳定性趋势（月度）
WITH monthly_stats AS (
SELECT
f.ftukey,
DATE_TRUNC('month', f.readingtimestamp) AS month,
-- 电压稳定性统计
AVG(f.ua_voltage) AS avg_ua,
STDDEV(f.ua_voltage) AS stddev_ua,
MAX(f.ua_voltage) - MIN(f.ua_voltage) AS range_ua,
-- 电流稳定性统计
AVG(f.ia_current) AS avg_ia,
STDDEV(f.ia_current) AS stddev_ia,
MAX(f.ia_current) - MIN(f.ia_current) AS range_ia,
-- 功率因数统计
AVG(f.power_cos) AS avg_cos,
STDDEV(f.power_cos) AS stddev_cos
FROM factpowerreadings f
WHERE
f.readingtimestamp >= NOW() - INTERVAL '12 months'
AND f.ua_voltage > 0 AND f.ia_current > 0
GROUP BY f.ftukey, month
)
SELECT
ms.ftukey AS "设备ID",
df.devicename AS "设备名称",
ms.month AS "月份",
ROUND(ms.avg_ua, 2) AS "平均A相电压",
ROUND(ms.stddev_ua, 4) AS "A相电压标准差",
ROUND(ms.range_ua, 2) AS "A相电压波动范围",
-- 标准差/平均值 = 变异系数，衡量稳定性
ROUND(ms.stddev_ua / ms.avg_ua * 100, 2) AS "A相电压变异系数(%)",
ROUND(ms.avg_ia, 2) AS "平均A相电流",
ROUND(ms.stddev_ia, 4) AS "A相电流标准差",
ROUND(ms.stddev_ia / ms.avg_ia * 100, 2) AS "A相电流变异系数(%)",
ROUND(ms.avg_cos, 3) AS "平均功率因数",
ROUND(ms.stddev_cos, 4) AS "功率因数标准差"
FROM monthly_stats ms
JOIN dimftu df ON ms.ftukey = df.ftukey
ORDER BY ms.month DESC, "A相电压变异系数(%)" DESC;
-- 设备负载特性分析（日负载曲线）
WITH daily_load_profile AS (
SELECT
f.ftukey,
dt.hour AS hour_of_day,
AVG(f.activePower_total) AS avg_active_power,
AVG(f.reactivePower_total) AS avg_reactive_power,
AVG(f.power_cos) AS avg_power_factor,
STDDEV(f.activePower_total) AS stddev_active_power
FROM factpowerreadings f
JOIN dimtime dt ON f.timestampkey = dt.timestampkey
WHERE
f.readingtimestamp >= NOW() - INTERVAL '30 days'
AND f.activePower_total > 0
GROUP BY f.ftukey, dt.hour
ORDER BY f.ftukey, dt.hour
)
SELECT
dlp.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
dlp.hour_of_day AS "小时",
ROUND(dlp.avg_active_power, 2) AS "平均有功功率(kW)",
ROUND(dlp.avg_reactive_power, 2) AS "平均无功功率(kVar)",
ROUND(dlp.avg_power_factor, 3) AS "平均功率因数",
ROUND(dlp.stddev_active_power, 2) AS "有功功率标准差",
ROUND(dlp.stddev_active_power / NULLIF(dlp.avg_active_power, 0) * 100, 2) AS "负载波动率(%)"
FROM daily_load_profile dlp
JOIN dimftu df ON dlp.ftukey = df.ftukey
ORDER BY dlp.ftukey, dlp.hour_of_day;
-- 设备健康度月度变化趋势
WITH monthly_health_indicators AS (
SELECT
f.ftukey,
DATE_TRUNC('month', f.readingtimestamp) AS month,
-- 三相不平衡指标
AVG(GREATEST(
ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3)
) / NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0)) AS current_imbalance,
AVG(GREATEST(
ABS(f.ua_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.ub_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.uc_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3)
) / NULLIF(((f.ua_voltage + f.ub_voltage + f.uc_voltage)/3), 0)) AS voltage_imbalance,
-- 异常率指标
SUM(CASE WHEN f.ua_voltage < 5.79 OR f.ua_voltage > 6.10 OR
f.ub_voltage < 5.70 OR f.ub_voltage > 5.94 OR
f.uc_voltage < 5.75 OR f.uc_voltage > 5.99 OR
f.power_cos < 0.68 THEN 1 ELSE 0 END) AS total_abnormal,
COUNT(*) AS total_readings
FROM factpowerreadings f
WHERE f.readingtimestamp >= NOW() - INTERVAL '12 months'
GROUP BY f.ftukey, month
)
SELECT
mhi.ftukey AS "设备ID",
df.devicename AS "设备名称",
mhi.month AS "月份",
-- 计算总健康分数 (满分100)
ROUND(100 - (
LEAST(30, mhi.current_imbalance * 100) -
LEAST(30, mhi.voltage_imbalance * 100) -
LEAST(40, 40.0 * mhi.total_abnormal / NULLIF(mhi.total_readings, 0))
), 2) AS "健康评分",
ROUND(mhi.current_imbalance * 100, 2) AS "电流不平衡度(%)",
ROUND(mhi.voltage_imbalance * 100, 2) AS "电压不平衡度(%)",
ROUND(100.0 * mhi.total_abnormal / NULLIF(mhi.total_readings, 0), 2) AS "参数异常率(%)"
FROM monthly_health_indicators mhi
JOIN dimftu df ON mhi.ftukey = df.ftukey
ORDER BY mhi.ftukey, mhi.month;
-- 变电站下各设备健康状况对比
WITH device_health AS (
SELECT
f.ftukey,
-- 三相不平衡度
AVG(ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3) /
NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0) +
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3) /
NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0) +
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3) /
NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0)) / 3 AS avg_current_imbalance,
-- 电压稳定性
STDDEV(f.ua_voltage) / NULLIF(AVG(f.ua_voltage), 0) AS ua_instability,
STDDEV(f.ub_voltage) / NULLIF(AVG(f.ub_voltage), 0) AS ub_instability,
STDDEV(f.uc_voltage) / NULLIF(AVG(f.uc_voltage), 0) AS uc_instability,
-- 功率因数稳定性
STDDEV(f.power_cos) AS cos_stability,
-- 参数超限比例
SUM(CASE WHEN f.ua_voltage < 5.79 OR f.ua_voltage > 6.10 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ub_voltage < 5.70 OR f.ub_voltage > 5.94 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.uc_voltage < 5.75 OR f.uc_voltage > 5.99 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ia_current < 6.62 OR f.ia_current > 19.23 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.ib_current < 5.51 OR f.ib_current > 16.95 THEN 1 ELSE 0 END) +
SUM(CASE WHEN f.power_cos < 0.68 OR f.power_cos > 1.0 THEN 1 ELSE 0 END) AS total_violations,
COUNT(*) * 6 AS total_params_checked
FROM factpowerreadings f
WHERE f.readingtimestamp >= NOW() - INTERVAL '30 days'
GROUP BY f.ftukey
)
SELECT
df.substation AS "变电站",
df.feeder AS "馈线",
df.ftukey AS "设备ID",
df.devicename AS "设备名称",
-- 健康评分计算
ROUND(100 - (
LEAST(25, dh.avg_current_imbalance * 100) -
LEAST(25, (dh.ua_instability + dh.ub_instability + dh.uc_instability) * 100 / 3) -
LEAST(25, dh.cos_stability * 50) -
LEAST(25, 25.0 * dh.total_violations / NULLIF(dh.total_params_checked, 0))
), 2) AS "健康评分",
ROUND(dh.avg_current_imbalance * 100, 2) AS "电流不平衡度(%)",
ROUND((dh.ua_instability + dh.ub_instability + dh.uc_instability) * 100 / 3, 2) AS "电压不稳定度(%)",
ROUND(100.0 * dh.total_violations / NULLIF(dh.total_params_checked, 0), 2) AS "参数超限率(%)"
FROM device_health dh
JOIN dimftu df ON dh.ftukey = df.ftukey
ORDER BY df.substation, "健康评分";
-- 馈线健康等级评估
WITH feeder_health AS (
SELECT
df.substation,
df.feeder,
COUNT(DISTINCT f.ftukey) AS device_count,
-- 各种健康指标的平均值
AVG(
GREATEST(
ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3)
) / NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0)
) AS avg_current_imbalance,
AVG(STDDEV(f.ua_voltage) / NULLIF(AVG(f.ua_voltage), 0)) OVER (PARTITION BY df.feeder) AS avg_voltage_instability,
SUM(CASE WHEN f.ua_voltage < 5.79 OR f.ua_voltage > 6.10 OR
f.ub_voltage < 5.70 OR f.ub_voltage > 5.94 OR
f.uc_voltage < 5.75 OR f.uc_voltage > 5.99 OR
f.ia_current < 6.62 OR f.ia_current > 19.23 OR
f.ib_current < 5.51 OR f.ib_current > 16.95 OR
f.power_cos < 0.68 THEN 1 ELSE 0 END) AS total_violations,
COUNT(*) * 6 AS total_checks
FROM factpowerreadings f
JOIN dimftu df ON f.ftukey = df.ftukey
WHERE
f.readingtimestamp >= NOW() - INTERVAL '30 days'
AND df.feeder IS NOT NULL
GROUP BY df.substation, df.feeder
)
SELECT
substation AS "变电站",
feeder AS "馈线",
device_count AS "设备数量",
ROUND(100 - (
LEAST(30, avg_current_imbalance * 100) -
LEAST(30, avg_voltage_instability * 100) -
LEAST(40, 40.0 * total_violations / NULLIF(total_checks, 0))
), 2) AS "健康评分",
CASE
WHEN 100 - (LEAST(30, avg_current_imbalance * 100) -
LEAST(30, avg_voltage_instability * 100) -
LEAST(40, 40.0 * total_violations / NULLIF(total_checks, 0))) >= 90 THEN 'A-优'
WHEN 100 - (LEAST(30, avg_current_imbalance * 100) -
LEAST(30, avg_voltage_instability * 100) -
LEAST(40, 40.0 * total_violations / NULLIF(total_checks, 0))) >= 80 THEN 'B-良'
WHEN 100 - (LEAST(30, avg_current_imbalance * 100) -
LEAST(30, avg_voltage_instability * 100) -
LEAST(40, 40.0 * total_violations / NULLIF(total_checks, 0))) >= 70 THEN 'C-中'
WHEN 100 - (LEAST(30, avg_current_imbalance * 100) -
LEAST(30, avg_voltage_instability * 100) -
LEAST(40, 40.0 * total_violations / NULLIF(total_checks, 0))) >= 60 THEN 'D-差'
ELSE 'E-劣'
END AS "健康等级",
ROUND(avg_current_imbalance * 100, 2) AS "平均电流不平衡度(%)",
ROUND(avg_voltage_instability * 100, 2) AS "平均电压不稳定度(%)",
ROUND(100.0 * total_violations / NULLIF(total_checks, 0), 2) AS "参数超限率(%)"
FROM feeder_health
ORDER BY "健康评分" DESC;
-- 区域故障风险评估（基于地理位置）
WITH location_risks AS (
SELECT
df.ftukey,
df.latitude,
df.longitude,
df.city,
df.county,
df.powersupplyoffice,
-- 故障风险指标
AVG(GREATEST(
ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3)
) / NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0)) * 30 + -- 电流不平衡分量
AVG(GREATEST(
ABS(f.ua_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.ub_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3),
ABS(f.uc_voltage - (f.ua_voltage + f.ub_voltage + f.uc_voltage)/3)
) / NULLIF(((f.ua_voltage + f.ub_voltage + f.uc_voltage)/3), 0)) * 20 + -- 电压不平衡分量
(SUM(CASE WHEN f.ua_voltage < 5.79 OR f.ua_voltage > 6.10 OR
f.ub_voltage < 5.70 OR f.ub_voltage > 5.94 OR
f.uc_voltage < 5.75 OR f.uc_voltage > 5.99 OR
f.power_cos < 0.68 THEN 1 ELSE 0 END) * 1.0 /
NULLIF(COUNT(*), 0)) * 50 AS risk_score -- 参数超限分量
FROM factpowerreadings f
JOIN dimftu df ON f.ftukey = df.ftukey
WHERE
f.readingtimestamp >= NOW() - INTERVAL '30 days'
AND df.latitude IS NOT NULL AND df.longitude IS NOT NULL
GROUP BY df.ftukey, df.latitude, df.longitude, df.city, df.county, df.powersupplyoffice
)
SELECT
ftukey AS "设备ID",
latitude AS "纬度",
longitude AS "经度",
city AS "城市",
county AS "区县",
powersupplyoffice AS "供电所",
ROUND(risk_score, 2) AS "风险评分",
CASE
WHEN risk_score >= 40 THEN '高风险'
WHEN risk_score >= 20 THEN '中风险'
ELSE '低风险'
END AS "风险等级"
FROM location_risks
ORDER BY risk_score DESC;
-- 设备异常预警（综合多种故障特征）
WITH device_warnings AS (
SELECT
f.ftukey,
-- 电压异常
SUM(CASE WHEN (f.ua_voltage < 5.79 OR f.ua_voltage > 6.10) AND
(f.ub_voltage < 5.70 OR f.ub_voltage > 5.94) AND
(f.uc_voltage < 5.75 OR f.uc_voltage > 5.99)
THEN 1 ELSE 0 END) AS three_phase_voltage_abnormal,
-- 电流异常
SUM(CASE WHEN (f.ia_current < 6.62 OR f.ia_current > 19.23) AND
(f.ib_current < 5.51 OR f.ib_current > 16.95)
THEN 1 ELSE 0 END) AS current_abnormal,
-- 三相不平衡异常
SUM(CASE WHEN GREATEST(
ABS(f.ia_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ib_current - (f.ia_current + f.ib_current + f.ic_current)/3),
ABS(f.ic_current - (f.ia_current + f.ib_current + f.ic_current)/3)
) / NULLIF(((f.ia_current + f.ib_current + f.ic_current)/3), 0) > 0.2
THEN 1 ELSE 0 END) AS imbalance_abnormal,
-- 功率因数异常
SUM(CASE WHEN f.power_cos < 0.68 THEN 1 ELSE 0 END) AS cos_abnormal,
-- 零序电流异常
SUM(CASE WHEN f.i0_current > 0.2 THEN 1 ELSE 0 END) AS zero_seq_current_abnormal,
-- 总记录数
COUNT(*) AS total_records,
-- 最近数据时间
MAX(f.readingtimestamp) AS latest_reading
FROM factpowerreadings f
WHERE f.readingtimestamp >= NOW() - INTERVAL '7 days'
GROUP BY f.ftukey
)
SELECT
dw.ftukey AS "设备ID",
df.devicename AS "设备名称",
df.feeder AS "馈线",
dw.latest_reading AS "最新数据时间",
-- 异常统计
dw.three_phase_voltage_abnormal AS "三相电压异常次数",
dw.current_abnormal AS "电流异常次数",
dw.imbalance_abnormal AS "三相不平衡次数",
dw.cos_abnormal AS "功率因数异常次数",
dw.zero_seq_current_abnormal AS "零序电流异常次数",
-- 异常率计算
ROUND(100.0 * (dw.three_phase_voltage_abnormal + dw.current_abnormal +
dw.imbalance_abnormal + dw.cos_abnormal + dw.zero_seq_current_abnormal) /
(dw.total_records * 5), 2) AS "异常率(%)",
-- 预警级别
CASE
WHEN 100.0 * (dw.three_phase_voltage_abnormal + dw.current_abnormal +
dw.imbalance_abnormal + dw.cos_abnormal + dw.zero_seq_current_abnormal) /
(dw.total_records * 5) >= 20 THEN '严重预警'
WHEN 100.0 * (dw.three_phase_voltage_abnormal + dw.current_abnormal +
dw.imbalance_abnormal + dw.cos_abnormal + dw.zero_seq_current_abnormal) /
(dw.total_records * 5) >= 10 THEN '中度预警'
WHEN 100.0 * (dw.three_phase_voltage_abnormal + dw.current_abnormal +
dw.imbalance_abnormal + dw.cos_abnormal + dw.zero_seq_current_abnormal) /
(dw.total_records * 5) >= 5 THEN '轻度预警'
ELSE '正常'
END AS "预警级别",
-- 可能故障类型
CASE
WHEN dw.three_phase_voltage_abnormal > 0 AND dw.current_abnormal > 0 AND dw.imbalance_abnormal > 0
THEN '可能短路故障(SC)'
WHEN dw.zero_seq_current_abnormal > 0 AND dw.three_phase_voltage_abnormal > 0
THEN '可能接地故障(GF)'
WHEN dw.three_phase_voltage_abnormal > 0 AND dw.imbalance_abnormal = 0
THEN '可能失压故障(VL)'
WHEN dw.current_abnormal > 0 AND dw.cos_abnormal > 0
THEN '可能过载故障(OL)'
WHEN dw.imbalance_abnormal > 0
THEN '可能三相不平衡(AC02/AV02)'
ELSE '无明确故障类型'
END AS "可能故障类型"
FROM device_warnings dw
JOIN dimftu df ON dw.ftukey = df.ftukey
WHERE dw.three_phase_voltage_abnormal > 0
OR dw.current_abnormal > 0
OR dw.imbalance_abnormal > 0
OR dw.cos_abnormal > 0
OR dw.zero_seq_current_abnormal > 0
ORDER BY "异常率(%)" DESC;
这些SQL查询可以直接添加到Metabase中，为您提供设备健康状况的全面监控。您可以根据需要调整阈值参数和时间范围。对于长期监控，建议创建以下仪表板：
这些查询采用TimescaleDB的时间分析功能和阈值报告中的参数范围，能够有效识别潜在故障并提供长期监控支持。