35 lines
1.6 KiB
SQL
35 lines
1.6 KiB
SQL
-- ============================================================
|
||
-- 15min continuous aggregate telemetrie střídače (dashboard sloty)
|
||
-- ============================================================
|
||
-- Zarovnáno s 15min sloty UI (UTC time_bucket = floorSlotUtcMs v frontendu).
|
||
-- Hodinový CA telemetry_inverter_hourly zůstává pro dlouhé grafy / legacy.
|
||
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS ems.telemetry_inverter_15m
|
||
WITH (timescaledb.continuous) AS
|
||
SELECT
|
||
time_bucket('15 minutes', measured_at) AS slot_start,
|
||
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 slot_start, site_id
|
||
WITH NO DATA;
|
||
|
||
-- Refresh: ≥2× time_bucket (15 min) → start_offset > 30 min
|
||
SELECT add_continuous_aggregate_policy(
|
||
'ems.telemetry_inverter_15m',
|
||
start_offset => INTERVAL '45 minutes',
|
||
end_offset => INTERVAL '1 minute',
|
||
schedule_interval => INTERVAL '15 minutes'
|
||
);
|
||
|
||
-- Timescale CA není v katalogu „materialized view“ – stejně jako V011 u telemetry_inverter_hourly.
|
||
COMMENT ON VIEW ems.telemetry_inverter_15m IS
|
||
'Čtvrthodinové agregáty telemetrie střídače. TimescaleDB continuous aggregate.
|
||
Refresh každých 15 minut. Dashboard přehled (sloty 15 min).
|
||
View vw_telemetry_15m_7d je v repeatable R__vw_telemetry_15m_7d.sql.';
|