Feeder Vellmoor Distribution · control room Synthetic data Sun 02:00

Definitions / zero_consumption

Zero consumption at an occupied premise

detector, runs daily 06:00

A 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

meaningtestresultobservedexpected
meteringcount_betweenpass22between 5.0 and 60.0
meteringuniquepass{'duplicates': 0}meter_id unique
meteringdetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 22}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object