26 lines
1.7 KiB
MySQL
26 lines
1.7 KiB
MySQL
|
-- 06_inc_mttr_minutes.sql — fleettickets · mttr -> minutes; drop constant flags
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- 1. Redefine the mttr generated column as integer MINUTES (source is decimal
|
||
|
|
-- hours) — numeric is the analytics-friendly type for avg/percentile/buckets.
|
||
|
|
-- 2. Drop the is_alarm / is_auto_created / is_auto_closed generated columns: we
|
||
|
|
-- filter is_alarm=true at ingest, so all three are constant `false` in
|
||
|
|
-- tickets.inc (zero information). They remain in `raw` (audit + the loader's
|
||
|
|
-- pre-insert filter still reads raw->>'is_alarm'); is_actionable is kept.
|
||
|
|
-- raw is untouched; only derived columns change. Idempotent.
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
|
||
|
|
SET search_path = tickets, public;
|
||
|
|
|
||
|
|
-- mttr: hours -> integer minutes
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS mttr;
|
||
|
|
ALTER TABLE tickets.inc
|
||
|
|
ADD COLUMN IF NOT EXISTS mttr numeric GENERATED ALWAYS AS
|
||
|
|
(round(NULLIF(raw->>'mttr','')::numeric * 60)) STORED;
|
||
|
|
|
||
|
|
COMMENT ON COLUMN tickets.inc.mttr IS 'Mean time to resolve, in MINUTES (source raw.mttr is decimal hours).';
|
||
|
|
|
||
|
|
-- drop the constant boolean flags (still available in raw)
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_alarm;
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_auto_created;
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_auto_closed;
|