Definitions / transformer_overload
Transformer overload
detector, runs hourlyA monitored distribution transformer loaded past its nameplate rating for consecutive hours: cumulative heating, not a passing peak.
assets · Head of Asset Management · version 1
The most recent run of complete hourly readings above threshold_pct of the rating, ending within the last two hours, lasting at least min_hours, within look_back_hours. One row per transformer, started at the run's first hour.
Latest change: First version.
SQL
WITH recent AS (
SELECT r.transformer_id, r."at", r.load_kva, t.rating_kva, t.feeder_id,
r.load_kva > t.rating_kva * {threshold_pct} / 100.0 AS over
FROM transformer_readings r JOIN transformers t ON t.id = r.transformer_id
WHERE r."at" > {now} - INTERVAL ({look_back_hours}) HOUR AND r."at" + INTERVAL 1 HOUR <= {now}
),
grouped AS (
SELECT *, sum(CASE WHEN over THEN 0 ELSE 1 END) OVER (PARTITION BY transformer_id ORDER BY "at"
ROWS UNBOUNDED PRECEDING) AS grp
FROM recent
),
runs AS (
SELECT transformer_id, any_value(feeder_id) AS feeder_id, any_value(rating_kva) AS rating_kva,
min("at") AS started_at, max("at") AS last_hour, count(*) AS hours, max(load_kva) AS peak_kva
FROM grouped WHERE over GROUP BY transformer_id, grp
)
SELECT 'transformer' AS subject_type, CAST(r.transformer_id AS TEXT) AS subject_id, r.transformer_id, r.feeder_id,
r.started_at, CASE WHEN r.peak_kva >= 1.5 * r.rating_kva THEN 'high' ELSE 'medium' END AS severity,
json_object(
'transformer_id', r.transformer_id,
'transformer', t.code,
'feeder_id', r.feeder_id,
'rating_kva', r.rating_kva,
'peak_kva', r.peak_kva,
'peak_pct', round(100.0 * r.peak_kva / r.rating_kva, 1),
'hours_over', r.hours,
'last_hour', r.last_hour
) AS evidence
FROM runs r JOIN transformers t ON t.id = r.transformer_id
WHERE r.hours >= {min_hours} AND r.last_hour + INTERVAL 1 HOUR > {now} - INTERVAL 2 HOUR
QUALIFY row_number() OVER (PARTITION BY r.transformer_id ORDER BY r.started_at DESC) = 1
ORDER BY r.started_at, r.transformer_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| assets | count_between | pass | 0 | between 0.0 and 30.0 |
| assets | unique | pass | {'duplicates': 0} | transformer_id unique |
| assets | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |