Definitions / zero_consumption
Zero consumption at an occupied premise
detector, runs daily 06:00A meter reporting complete reads of exactly zero for days at a premise with an account holder, after normal use before. A stuck register or a bypass; either way the reads are wrong.
metering · Head of Metering · version 2
Each of the last zero_days complete days has at least min_reads interval reads, zero import and zero export; the prior_days before them averaged at least min_prior_kwh_day; the premise is occupied (no move-out on or before now). Started on the first zero day of the run.
Latest change: Zero export as well: a meter wired in reverse shows zero import with export, and belongs to the reverse-flow check.
SQL
WITH days AS (
SELECT d.meter_id, d.day, d.kwh_import, d.kwh_export, d.reads,
d.day >= CAST({now} AS DATE) - {zero_days} AS in_run
FROM meter_daily d
WHERE d.day < CAST({now} AS DATE) AND d.day >= CAST({now} AS DATE) - ({zero_days} + {prior_days})
),
flagged AS (
SELECT meter_id,
count(*) FILTER (WHERE in_run AND reads >= {min_reads} AND kwh_import = 0 AND kwh_export = 0) AS zero_run,
avg(kwh_import) FILTER (WHERE NOT in_run AND reads > 0) AS prior_avg
FROM days GROUP BY meter_id
HAVING zero_run = {zero_days} AND prior_avg >= {min_prior_kwh_day}
),
first_zero AS (
SELECT f.meter_id, f.prior_avg,
coalesce(max(d.day) FILTER (WHERE d.kwh_import > 0 OR d.kwh_export > 0) + 1, min(d.day)) AS first_day
FROM flagged f JOIN meter_daily d ON d.meter_id = f.meter_id AND d.day < CAST({now} AS DATE)
GROUP BY f.meter_id, f.prior_avg
)
SELECT 'meter' AS subject_type, CAST(z.meter_id AS TEXT) AS subject_id, z.meter_id, m.feeder_id, m.premise_id,
CAST(z.first_day AS TIMESTAMP) AS started_at, 'medium' AS severity,
json_object(
'meter_id', z.meter_id,
'serial', m.serial,
'premise_id', m.premise_id,
'account_no', p.account_no,
'class', p.class,
'feeder_id', m.feeder_id,
'zero_since', z.first_day,
'prior_kwh_per_day', round(z.prior_avg, 2)
) AS evidence
FROM first_zero z JOIN meters m ON m.id = z.meter_id JOIN premises p ON p.id = m.premise_id
WHERE p.status = 'occupied' OR p.moved_out_at > {now}
ORDER BY started_at, z.meter_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| metering | count_between | pass | 22 | between 5.0 and 60.0 |
| metering | unique | pass | {'duplicates': 0} | meter_id unique |
| metering | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 22} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |