SQL for Power Plant Performance Monitoring: Querying Time-Series Data with Window Functions

Power Plant Time-Series Analysis

Power plants generate massive streams of sensor data every second. Tracking temperature, pressure, vibration, and efficiency metrics over time is critical for predictive maintenance and performance optimization.

1. Daily Rolling Average Efficiency

Calculate the 7-day rolling average efficiency across all units to smooth out daily fluctuations.

SELECT
    unit_id,
    measurement_date,
    AVG(efficiency_percentage) OVER (
        PARTITION BY unit_id
        ORDER BY measurement_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM power_plant_sensor_readings
ORDER BY unit_id, measurement_date;

2. Detecting Anomalous Readings

Identify readings that deviate more than 2 standard deviations from the 30-day rolling average — a key indicator of potential equipment failure.

WITH rolling_stats AS (
    SELECT
        unit_id,
        measurement_date,
        efficiency_percentage,
        AVG(efficiency_percentage) OVER (
            PARTITION BY unit_id
            ORDER BY measurement_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) AS avg_30d,
        STDDEV(efficiency_percentage) OVER (
            PARTITION BY unit_id
            ORDER BY measurement_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) AS stddev_30d
    FROM power_plant_sensor_readings
)
SELECT
    unit_id,
    measurement_date,
    efficiency_percentage,
    avg_30d,
    efficiency_percentage - avg_30d AS deviation
FROM rolling_stats
WHERE ABS(efficiency_percentage - avg_30d) > 2 * STDDEV(stddev_30d)
ORDER BY deviation DESC;

3. Week-over-Week Performance Comparison

Compare this week's average efficiency against the same week last year to identify seasonal trends or degradation.

WITH current_week AS (
    SELECT
        unit_id,
        AVG(efficiency_percentage) AS avg_eff
    FROM power_plant_sensor_readings
    WHERE measurement_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY unit_id
),
previous_year_week AS (
    SELECT
        unit_id,
        AVG(efficiency_percentage) AS avg_eff
    FROM power_plant_sensor_readings
    WHERE measurement_date >= CURRENT_DATE - INTERVAL '8 days'
      AND measurement_date < CURRENT_DATE - INTERVAL '1 day'
      AND measurement_date >= CURRENT_DATE - INTERVAL '15 days'
      AND measurement_date < CURRENT_DATE - INTERVAL '7 days'
    GROUP BY unit_id
)
SELECT
    c.unit_id,
    c.avg_eff AS current_week_avg,
    p.avg_eff AS previous_year_week_avg,
    ROUND((c.avg_eff - p.avg_eff) / p.avg_eff * 100, 2) AS pct_change
FROM current_week c
JOIN previous_year_week p ON c.unit_id = p.unit_id
ORDER BY pct_change DESC;

4. Finding the Hottest Running Unit

Identify which unit has the highest average temperature over the last 24 hours for immediate attention.

SELECT
    unit_id,
    AVG(temperature_c) AS avg_temp_24h
FROM power_plant_sensor_readings
WHERE measurement_date >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY unit_id
ORDER BY avg_temp_24h DESC
LIMIT 1;

Key Takeaways for Production

• Window functions are essential for time-series analysis — they let you compute running totals, moving averages, and comparisons across rows without self-joins.
• Always index the partitioning and ordering columns (unit_id, measurement_date) for performance on billions of rows.
• BETWEEN vs. RANGE: ROWS BETWEEN is physical row-offset based, while RANGE BETWEEN is logical value-based. Use ROWS for time-series with possible duplicates.
• Be careful with NULL handling — window functions propagate NULLs. Use COALESCE or IGNORE NULLS where needed.