c3a5258bf2
운전모드/폐합 기반 실시간 알람 — P1 마지막 조각. - kpi_alert 테이블(edge-trigger: 진입 active, 해제 resolved, DB상태라 크래시 무관). - Hc900LiveKpiService 알람 평가: 최신 history_1s 샘플로 운전모드(cleaning/drawdown/normal) 판정 + 폐합 이탈(normal & |closure-100|>tol). 룰: cleaning/drawdown(info), closure_deviation(warning). - GET /api/report/alerts. Report:LiveKpi:ClosureTolerancePct(기본 2%) config. 검증: 라이브 C-8111 drawdown 알람 발화(feed≈0 포착), opened_at 고정·updated_at 갱신(edge-trigger), /alerts 정상. (데모 sim은 임계 빈번교차로 flapping 가능 — M분 디바운스는 운영 튜닝.) P1 완료: P1a 1초버퍼 · P1b 연속집계 · P1c 온라인누적기 · P1d 알람. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
53 lines
2.7 KiB
SQL
53 lines
2.7 KiB
SQL
-- P1a: 1초 링버퍼 히스토리안 (hc900.history_1s)
|
|
-- 적용: psql "host=localhost dbname=iiot_platform user=postgres" -f scripts/sql/p1_historian.sql
|
|
-- 서비스(Hc900FastHistoryService)가 기동 시 동일 DDL을 멱등 적용하므로 수동 실행은 선택.
|
|
SET search_path TO hc900;
|
|
|
|
CREATE TABLE IF NOT EXISTS hc900.history_1s (
|
|
tagname text NOT NULL,
|
|
recorded_at timestamptz NOT NULL DEFAULT now(),
|
|
value text,
|
|
controller_id text
|
|
);
|
|
|
|
-- 하이퍼테이블 (1시간 청크 — evict/압축 단위)
|
|
SELECT create_hypertable('hc900.history_1s', 'recorded_at',
|
|
chunk_time_interval => INTERVAL '1 hour', if_not_exists => TRUE);
|
|
|
|
-- 압축 (6시간 지난 청크) + 링버퍼 보존 (14일 — 청크 DROP, 비용≈0)
|
|
ALTER TABLE hc900.history_1s SET (timescaledb.compress, timescaledb.compress_segmentby = 'tagname');
|
|
SELECT add_compression_policy('hc900.history_1s', INTERVAL '6 hours', if_not_exists => TRUE);
|
|
SELECT add_retention_policy('hc900.history_1s', INTERVAL '14 days', if_not_exists => TRUE);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_h1s_tag ON hc900.history_1s (tagname, recorded_at DESC);
|
|
|
|
-- P1b: 연속집계 history_1min — 1초 버퍼 evict 전 60초 롤업(장기 무손실, 2계층)
|
|
CREATE MATERIALIZED VIEW IF NOT EXISTS hc900.history_1min
|
|
WITH (timescaledb.continuous) AS
|
|
SELECT time_bucket('1 minute', recorded_at) AS bucket, tagname,
|
|
last(value, recorded_at) AS value, last(controller_id, recorded_at) AS controller_id
|
|
FROM hc900.history_1s GROUP BY bucket, tagname
|
|
WITH NO DATA;
|
|
|
|
-- 집계 lag(≤3h) ≪ 보존 윈도(14일) → raw 삭제 전 materialize 보장
|
|
SELECT add_continuous_aggregate_policy('hc900.history_1min',
|
|
start_offset => INTERVAL '3 hours', end_offset => INTERVAL '10 minutes',
|
|
schedule_interval => INTERVAL '5 minutes', if_not_exists => TRUE);
|
|
|
|
-- P1c: 메트릭엔진 드롭인 소스(bucket→recorded_at) + 온라인 KPI 누적 테이블
|
|
CREATE OR REPLACE VIEW hc900.history_1min_src AS
|
|
SELECT tagname, bucket AS recorded_at, value, controller_id FROM hc900.history_1min;
|
|
|
|
CREATE TABLE IF NOT EXISTS hc900.live_kpi (
|
|
column_id text NOT NULL, kpi text NOT NULL, window_start date NOT NULL,
|
|
value double precision, unit text, state text, excluded_min int, status text,
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (column_id, kpi, window_start));
|
|
|
|
-- P1d: 실시간 알람 (edge-trigger: 진입 active, 해제 resolved)
|
|
CREATE TABLE IF NOT EXISTS hc900.kpi_alert (
|
|
column_id text NOT NULL, rule text NOT NULL, severity text, active boolean NOT NULL DEFAULT true,
|
|
message text, value double precision,
|
|
opened_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), resolved_at timestamptz,
|
|
PRIMARY KEY (column_id, rule));
|