59 lines
2.5 KiB
SQL
59 lines
2.5 KiB
SQL
-- ============================================================
|
||
-- V011__indexes_and_aggregates.sql
|
||
-- Doplňuje V010__indexes.sql: indexy na forecast + hourly CA telemetrie.
|
||
-- (Indexy planning_run, planning_interval, market_price, audit, mode_log,
|
||
-- ev_session jsou již ve V010 – zde se neopakují, aby nevznikly duplicitní B-stromy.)
|
||
-- ============================================================
|
||
|
||
-- ============================================================
|
||
-- Indexy pro výkon (forecast – nové oproti V010)
|
||
-- ============================================================
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_forecast_run_site_array
|
||
ON ems.forecast_pv_run (site_id, pv_array_id, created_at DESC);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_forecast_interval_run_start
|
||
ON ems.forecast_pv_interval (run_id, interval_start);
|
||
|
||
-- ============================================================
|
||
-- TimescaleDB Continuous Aggregates pro dashboard výkon
|
||
-- ============================================================
|
||
|
||
-- Hodinové agregáty telemetrie střídače (pro graf posledních 7 dní)
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS ems.telemetry_inverter_hourly
|
||
WITH (timescaledb.continuous) AS
|
||
SELECT
|
||
time_bucket('1 hour', measured_at) AS hour,
|
||
site_id,
|
||
AVG(pv_power_w)::INT AS avg_pv_w,
|
||
AVG(battery_power_w)::INT AS avg_battery_w,
|
||
AVG(grid_power_w)::INT AS avg_grid_w,
|
||
AVG(load_power_w)::INT AS avg_load_w,
|
||
LAST(battery_soc_percent, measured_at) AS last_soc_pct,
|
||
COUNT(*) AS sample_count
|
||
FROM ems.telemetry_inverter
|
||
GROUP BY hour, site_id
|
||
WITH NO DATA;
|
||
|
||
-- Refresh policy: každých 15 minut. Okno musí pokrývat ≥2× time_bucket (1h) → min. šířka >2h.
|
||
SELECT add_continuous_aggregate_policy(
|
||
'ems.telemetry_inverter_hourly',
|
||
start_offset => INTERVAL '2 hours 15 minutes',
|
||
end_offset => INTERVAL '1 minute',
|
||
schedule_interval => INTERVAL '15 minutes'
|
||
);
|
||
|
||
-- ============================================================
|
||
-- View pro použití v dashboardu (7 dní zpět)
|
||
-- ============================================================
|
||
|
||
CREATE OR REPLACE VIEW ems.vw_telemetry_hourly_7d AS
|
||
SELECT *
|
||
FROM ems.telemetry_inverter_hourly
|
||
WHERE hour >= now() - INTERVAL '7 days'
|
||
ORDER BY hour DESC;
|
||
|
||
COMMENT ON VIEW ems.telemetry_inverter_hourly IS
|
||
'Hodinové agregáty telemetrie střídače. TimescaleDB continuous aggregate.
|
||
Refresh každých 15 minut. Používat pro grafy delší než 1 den.';
|