Definitions / reverse_flow_unregistered
Reverse flow with no solar registered
detector, runs daily 06:00Energy flowing out to the network from a premise with no generation registered: panels connected without telling the network, or a meter wired backwards. Both need a metering case before the next bill.
metering · Head of Metering · version 1
At least min_days of the last look_back_days complete days exported more than min_export_kwh, and the premise has no solar registration dated on or before now. Started on the first day the meter data shows an export above min_export_kwh, so a run is one incident however long it lasts.
Latest change: First version.
SQL
WITH out AS (
SELECT d.meter_id, count(*) AS days_exporting, max(d.kwh_export) AS max_export,
sum(d.kwh_export) AS export_kwh, sum(d.kwh_import) AS import_kwh
FROM meter_daily d
WHERE d.day < CAST({now} AS DATE) AND d.day >= CAST({now} AS DATE) - {look_back_days}
AND d.kwh_export > {min_export_kwh}
GROUP BY d.meter_id
HAVING count(*) >= {min_days}
),
began AS (
SELECT d.meter_id, min(d.day) AS first_day
FROM meter_daily d JOIN out o ON o.meter_id = d.meter_id
WHERE d.day < CAST({now} AS DATE) AND d.kwh_export > {min_export_kwh}
GROUP BY d.meter_id
)
SELECT 'meter' AS subject_type, CAST(o.meter_id AS TEXT) AS subject_id, o.meter_id, m.feeder_id, m.premise_id,
CAST(b.first_day AS TIMESTAMP) AS started_at, 'low' AS severity,
json_object(
'meter_id', o.meter_id,
'serial', m.serial,
'premise_id', m.premise_id,
'account_no', p.account_no,
'feeder_id', m.feeder_id,
'days_exporting', o.days_exporting,
'export_kwh', round(o.export_kwh, 2),
'import_kwh_same_days', round(o.import_kwh, 2),
'looks_reversed', o.import_kwh = 0
) AS evidence
FROM out o JOIN began b ON b.meter_id = o.meter_id JOIN meters m ON m.id = o.meter_id
JOIN premises p ON p.id = m.premise_id
LEFT JOIN solar_registrations s ON s.premise_id = m.premise_id AND s.registered_at <= {now}
WHERE s.premise_id IS NULL
ORDER BY started_at, o.meter_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| metering | count_between | pass | 32 | between 5.0 and 60.0 |
| metering | unique | pass | {'duplicates': 0} | meter_id unique |
| metering | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 32} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |