根据提供的配网遥测数据仓库表结构，可以设计一些常见的业务分析SQL语句，供研究人员在Metabase中使用。这些查询将涵盖不同的分析场景，从基础的监测到更复杂的趋势分析。

1. 基础监测查询

最近24小时的电压异常监测

SELECT 
    dt.FullDateTime,
    df.FTU_ID,
    dx.Substation,
    f.UAB_Voltage,
    f.UBC_Voltage,
    f.UCA_Voltage
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimXTU dx ON df.XTUKey = dx.XTUKey
WHERE 
    dt.FullDateTime > NOW() - INTERVAL '24 hours'
AND 
    (f.UAB_Voltage > 240 OR f.UAB_Voltage < 200
    OR f.UBC_Voltage > 240 OR f.UBC_Voltage < 200
    OR f.UCA_Voltage > 240 OR f.UCA_Voltage < 200)
ORDER BY 
    dt.FullDateTime DESC;

变电站功率负载统计

SELECT 
    dx.Substation,
    AVG(f.ActivePower_Total) AS avg_active_power,
    MAX(f.ActivePower_Total) AS max_active_power,
    MIN(f.ActivePower_Total) AS min_active_power,
    STDDEV(f.ActivePower_Total) AS stddev_active_power
FROM 
    FactPowerReadings f
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimXTU dx ON df.XTUKey = dx.XTUKey
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 
    dx.Substation
ORDER BY 
    avg_active_power DESC;

2. 时间趋势分析

每小时平均功率趋势

SELECT 
    dt.Date,
    dt.Hour,
    AVG(f.ActivePower_Total) AS avg_active_power,
    AVG(f.ReactivePower_Total) AS avg_reactive_power,
    AVG(f.ApparentPower_Total) AS avg_apparent_power
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 
    dt.Date, dt.Hour
ORDER BY 
    dt.Date, dt.Hour;

工作日vs周末负载对比

SELECT 
    dt.IsWeekend,
    dt.Hour,
    AVG(f.ActivePower_Total) AS avg_active_power
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 
    dt.IsWeekend, dt.Hour
ORDER BY 
    dt.IsWeekend, dt.Hour;

3. 设备性能分析

FTU状态统计

SELECT 
    df.Status,
    COUNT(*) AS device_count,
    AVG(EXTRACT(DAY FROM CURRENT_DATE - df.InstallationDate)) AS avg_days_since_installation
FROM 
    DimFTU df
WHERE 
    df.IsCurrent = TRUE
GROUP BY 
    df.Status;

电流不平衡度分析

SELECT 
    df.FTU_ID,
    dx.Substation,
    dt.Date,
    AVG(f.IA_Current) AS avg_ia,
    AVG(f.IB_Current) AS avg_ib,
    AVG(f.IC_Current) AS avg_ic,
    (ABS(AVG(f.IA_Current) - AVG(f.IB_Current)) + 
     ABS(AVG(f.IB_Current) - AVG(f.IC_Current)) + 
     ABS(AVG(f.IC_Current) - AVG(f.IA_Current))) / 
    (AVG(f.IA_Current) + AVG(f.IB_Current) + AVG(f.IC_Current)) * 100 AS imbalance_percent
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimXTU dx ON df.XTUKey = dx.XTUKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 
    df.FTU_ID, dx.Substation, dt.Date
HAVING 
    (AVG(f.IA_Current) + AVG(f.IB_Current) + AVG(f.IC_Current)) > 0
ORDER BY 
    imbalance_percent DESC
LIMIT 20;

4. 地理分布分析

功率密度地理分布

SELECT 
    df.Latitude,
    df.Longitude,
    df.LocationDescription,
    AVG(f.ActivePower_Total) AS avg_active_power,
    MAX(f.ActivePower_Total) AS max_active_power
FROM 
    FactPowerReadings f
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '7 days'
AND 
    df.Latitude IS NOT NULL
AND 
    df.Longitude IS NOT NULL
GROUP BY 
    df.Latitude, df.Longitude, df.LocationDescription;

5. 高级分析

功率因数计算和分析

SELECT 
    dt.Date,
    df.FTU_ID,
    dx.Substation,
    AVG(f.ActivePower_Total / NULLIF(f.ApparentPower_Total, 0)) AS avg_power_factor
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimXTU dx ON df.XTUKey = dx.XTUKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '30 days'
AND 
    f.ApparentPower_Total > 0
GROUP BY 
    dt.Date, df.FTU_ID, dx.Substation
HAVING 
    AVG(f.ActivePower_Total / NULLIF(f.ApparentPower_Total, 0)) < 0.85
ORDER BY 
    avg_power_factor ASC;

负载增长率分析

WITH monthly_power AS (
    SELECT 
        dt.Year,
        dt.Month,
        dx.Substation,
        AVG(f.ActivePower_Total) AS avg_power
    FROM 
        FactPowerReadings f
    JOIN 
        DimTime dt ON f.TimestampKey = dt.TimestampKey
    JOIN 
        DimFTU df ON f.FTUKey = df.FTUKey
    JOIN 
        DimXTU dx ON df.XTUKey = dx.XTUKey
    GROUP BY 
        dt.Year, dt.Month, dx.Substation
)
SELECT 
    current_month.Year,
    current_month.Month,
    current_month.Substation,
    current_month.avg_power AS current_month_power,
    prev_month.avg_power AS previous_month_power,
    ((current_month.avg_power - prev_month.avg_power) / NULLIF(prev_month.avg_power, 0)) * 100 AS growth_rate
FROM 
    monthly_power current_month
LEFT JOIN 
    monthly_power prev_month 
ON 
    (current_month.Year = prev_month.Year AND current_month.Month = prev_month.Month + 1 AND current_month.Substation = prev_month.Substation)
    OR 
    (current_month.Year = prev_month.Year + 1 AND current_month.Month = 1 AND prev_month.Month = 12 AND current_month.Substation = prev_month.Substation)
WHERE 
    prev_month.avg_power IS NOT NULL
ORDER BY 
    current_month.Year DESC, current_month.Month DESC, growth_rate DESC;

电压稳定性评分

SELECT 
    df.FTU_ID,
    dx.Substation,
    dt.Date,
    AVG(f.UAB_Voltage) AS avg_uab,
    STDDEV(f.UAB_Voltage) AS stddev_uab,
    AVG(f.UBC_Voltage) AS avg_ubc,
    STDDEV(f.UBC_Voltage) AS stddev_ubc,
    AVG(f.UCA_Voltage) AS avg_uca,
    STDDEV(f.UCA_Voltage) AS stddev_uca,
    (STDDEV(f.UAB_Voltage) + STDDEV(f.UBC_Voltage) + STDDEV(f.UCA_Voltage)) / 
    (AVG(f.UAB_Voltage) + AVG(f.UBC_Voltage) + AVG(f.UCA_Voltage)) * 100 AS voltage_instability_score
FROM 
    FactPowerReadings f
JOIN 
    DimTime dt ON f.TimestampKey = dt.TimestampKey
JOIN 
    DimFTU df ON f.FTUKey = df.FTUKey
JOIN 
    DimXTU dx ON df.XTUKey = dx.XTUKey
WHERE 
    dt.Date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 
    df.FTU_ID, dx.Substation, dt.Date
ORDER BY 
    voltage_instability_score DESC
LIMIT 20;

这些SQL查询为研究人员提供了不同维度的电力系统分析，包括基础监控、时间趋势分析、设备性能分析、地理分布分析以及一些高级分析。您可以将这些查询保存在Metabase中，研究人员可以根据需要使用和修改它们。