Definitions / outage_cluster
Outage cluster
detector, runs realtimeCustomers off supply that the control room does not know about yet: meters that sent a last gasp and no restore, grouped by transformer (or by feeder when three or more transformers on it are dark), on equipment no open ticket and no planned work covers.
control · Head of Network Operations · version 3
A meter is dark when its latest last gasp in the last lookback_minutes is at least sustained_minutes old and no power restore followed it. A transformer is reported when at least min_gasps of its meters are dark, they are at least min_share of its meters, and fewer of the meters that went dark with them have come back than are still dark; a transformer whose every meter is dark is reported after lone_minutes even with a single meter (a farm or a pumping station on its own transformer). Transformers inside an open OMS ticket (the agent's own tickets count for agent_ticket_hold_hours) or a planned outage are left out. Three or more dark transformers on one feeder are one feeder-level row.
Latest change: A transformer with every meter dark for lone_minutes is reported even when it has one meter; small rural transformers were never reported.
SQL
WITH gasps AS (
SELECT e.meter_id, max(e."at") AS gasp_at
FROM meter_events e
WHERE e.type = 'last_gasp' AND e."at" > {now} - INTERVAL ({lookback_minutes}) MINUTE AND e."at" <= {now}
GROUP BY e.meter_id
),
backs AS (
SELECT e.meter_id, max(e."at") AS back_at
FROM meter_events e
WHERE e.type = 'power_restore' AND e."at" > {now} - INTERVAL ({lookback_minutes}) MINUTE AND e."at" <= {now}
GROUP BY e.meter_id
),
state AS (
SELECT g.meter_id, g.gasp_at, m.feeder_id, m.transformer_id, t.position,
(b.back_at IS NULL OR b.back_at < g.gasp_at) AS dark
FROM gasps g
JOIN meters m ON m.id = g.meter_id
JOIN transformers t ON t.id = m.transformer_id
LEFT JOIN backs b ON b.meter_id = g.meter_id
),
covered AS (
SELECT DISTINCT t.id AS transformer_id
FROM outages o
JOIN transformers t ON t.feeder_id = o.feeder_id
AND (o.transformer_id = t.id OR (o.transformer_id IS NULL AND t.position >= o.from_position))
WHERE o.opened_at <= {now} AND coalesce(o.status, '') <> 'withdrawn'
AND (o.restored_at IS NULL OR o.restored_at > {now})
AND (o.source <> 'agent' OR o.opened_at > {now} - INTERVAL ({agent_ticket_hold_hours}) HOUR)
UNION
SELECT t.id
FROM planned_outages p
JOIN transformers t ON t.feeder_id = p.feeder_id
AND (p.transformer_id = t.id OR (p.transformer_id IS NULL AND t.position >= p.from_position))
WHERE p.cancelled_at IS NULL AND p.start_at - INTERVAL 30 MINUTE <= {now} AND p.end_at + INTERVAL 60 MINUTE >= {now}
),
size AS (SELECT transformer_id, count(*) AS meters FROM meters GROUP BY transformer_id),
per_tx AS (
SELECT s.feeder_id, s.transformer_id, min(s.position) AS position,
count(*) FILTER (WHERE s.dark AND s.gasp_at <= {now} - INTERVAL ({sustained_minutes}) MINUTE) AS dark,
count(*) FILTER (WHERE s.dark AND s.gasp_at <= {now} - INTERVAL ({lone_minutes}) MINUTE) AS dark_long,
count(*) FILTER (WHERE NOT s.dark) AS back,
min(s.gasp_at) FILTER (WHERE s.dark) AS first_gasp,
list(s.meter_id ORDER BY s.meter_id) FILTER (WHERE s.dark) AS meter_ids,
any_value(z.meters) AS meters
FROM state s
JOIN size z ON z.transformer_id = s.transformer_id
WHERE s.transformer_id NOT IN (SELECT transformer_id FROM covered)
GROUP BY s.feeder_id, s.transformer_id
),
hit AS (
SELECT * FROM per_tx
WHERE back < dark AND ((dark >= {min_gasps} AND dark >= {min_share} * meters) OR dark_long >= meters)
),
per_feeder AS (
SELECT feeder_id, count(*) AS transformers FROM hit GROUP BY feeder_id
),
rows AS (
SELECT 'feeder' AS subject_type, CAST(h.feeder_id AS TEXT) AS subject_id, h.feeder_id, NULL::INTEGER AS transformer_id,
min(h.first_gasp) AS started_at, sum(h.dark) AS dark_meters, sum(h.meters) AS customers_on_equipment,
min(h.position) AS from_position, list(h.transformer_id ORDER BY h.transformer_id) AS transformer_ids,
flatten(list(h.meter_ids)) AS meter_ids
FROM hit h JOIN per_feeder p ON p.feeder_id = h.feeder_id
WHERE p.transformers >= {section_min_transformers}
GROUP BY h.feeder_id
UNION ALL
SELECT 'transformer', CAST(h.transformer_id AS TEXT), h.feeder_id, h.transformer_id, h.first_gasp, h.dark, h.meters,
h.position, [h.transformer_id], h.meter_ids
FROM hit h JOIN per_feeder p ON p.feeder_id = h.feeder_id
WHERE p.transformers < {section_min_transformers}
),
calls AS (
SELECT r.subject_id, count(c.id) AS calls
FROM rows r, unnest(r.meter_ids) AS u(meter_id)
JOIN meters m ON m.id = u.meter_id
JOIN customer_calls c ON c.premise_id = m.premise_id AND c."at" >= r.started_at AND c."at" <= {now}
GROUP BY r.subject_id
),
priority AS (
SELECT r.subject_id, count(*) AS priority
FROM rows r, unnest(r.meter_ids) AS u(meter_id)
JOIN meters m ON m.id = u.meter_id
JOIN priority_register p ON p.premise_id = m.premise_id
GROUP BY r.subject_id
)
SELECT r.subject_type, r.subject_id, r.feeder_id, r.transformer_id, r.started_at,
CASE WHEN r.dark_meters >= 100 OR coalesce(p.priority, 0) > 0 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'feeder_id', r.feeder_id,
'feeder', f.code,
'transformer_id', r.transformer_id,
'affected_transformer_ids', r.transformer_ids,
'from_position', r.from_position,
'dark_meters', r.dark_meters,
'customers_on_equipment', r.customers_on_equipment,
'meter_ids', r.meter_ids[1:{max_listed_meters}],
'first_gasp', r.started_at,
'minutes_dark', round(date_diff('second', r.started_at, {now}) / 60.0, 1),
'customer_calls', coalesce(c.calls, 0),
'priority_customers', coalesce(p.priority, 0),
'open_tickets_network', (SELECT count(*) FROM outages o WHERE o.opened_at <= {now} AND o.source <> 'planned'
AND coalesce(o.status, '') <> 'withdrawn'
AND (o.restored_at IS NULL OR o.restored_at > {now}))
) AS evidence
FROM rows r
JOIN feeders f ON f.id = r.feeder_id
LEFT JOIN calls c ON c.subject_id = r.subject_id
LEFT JOIN priority p ON p.subject_id = r.subject_id
ORDER BY r.started_at, r.subject_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| control | count_between | pass | 0 | between 0.0 and 40.0 |
| control | storm_evening | FAIL | 0 | between 3.0 and 60.0 |
| control | unique | pass | {'duplicates': 0} | subject_id unique |
| control | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |