125 lines
6.4 KiB
SQL
125 lines
6.4 KiB
SQL
-- =============================================================
|
||
-- R__067_vw_site_effective_price_economics.sql
|
||
-- EMS Platform – ekonomické views (závisí na vw_site_effective_price)
|
||
-- Musí běžet až PO R__061_vw_site_effective_price.sql (číselný prefix Flyway).
|
||
-- Repeatable migration
|
||
--
|
||
-- Pozn.: při změně pořadí / názvů výstupních sloupců nestačí CREATE OR REPLACE VIEW —
|
||
-- PostgreSQL hlásí 42P16 („cannot change name of view column …“). Nejdřív DROP.
|
||
-- =============================================================
|
||
|
||
DROP VIEW IF EXISTS ems.vw_economics_daily CASCADE;
|
||
DROP VIEW IF EXISTS ems.vw_economics_interval CASCADE;
|
||
|
||
CREATE VIEW ems.vw_economics_interval AS
|
||
SELECT
|
||
ai.site_id,
|
||
ai.interval_start,
|
||
-- Wh-based kWh (per-direction; u čistého importu/exportu max čítač vs. odhad z P_grid)
|
||
ROUND(
|
||
ems.fn_audit_grid_import_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
) / 1000,
|
||
4
|
||
)
|
||
AS import_kwh,
|
||
ROUND(
|
||
ems.fn_audit_grid_export_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
) / 1000,
|
||
4
|
||
)
|
||
AS export_kwh,
|
||
-- Směrové cashflow: kolik Kč za import ze sítě / kolik Kč za export do sítě
|
||
ROUND(
|
||
ems.fn_audit_grid_import_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
)
|
||
/ 1000.0 * COALESCE(ep.effective_buy_price_czk_kwh, 0), 4
|
||
) AS grid_import_cashflow_czk,
|
||
ROUND(
|
||
ems.fn_audit_grid_export_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
)
|
||
/ 1000.0 * COALESCE(ep.effective_sell_price_czk_kwh, 0), 4
|
||
) AS grid_export_revenue_czk,
|
||
-- Net cost (zpětná kompatibilita): import_cashflow - export_revenue
|
||
ROUND(
|
||
ems.fn_audit_grid_import_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
)
|
||
/ 1000.0 * COALESCE(ep.effective_buy_price_czk_kwh, 0)
|
||
- ems.fn_audit_grid_export_wh_for_economics(
|
||
ai.actual_grid_import_wh, ai.actual_grid_export_wh, ai.actual_grid_power_w
|
||
)
|
||
/ 1000.0 * COALESCE(ep.effective_sell_price_czk_kwh, 0), 4
|
||
) AS dynamic_cost_czk,
|
||
ai.actual_cost_czk AS stored_cost_czk,
|
||
ai.green_bonus_czk,
|
||
pi.expected_cost_czk AS planned_cost_czk,
|
||
pi.grid_setpoint_w AS planned_grid_w,
|
||
ai.actual_grid_power_w,
|
||
ep.effective_buy_price_czk_kwh,
|
||
ep.effective_sell_price_czk_kwh,
|
||
pi.effective_buy_price AS planned_buy_price,
|
||
pi.effective_sell_price AS planned_sell_price,
|
||
ai.actual_pv_power_w,
|
||
ai.actual_load_power_w,
|
||
ai.actual_ev_power_w,
|
||
ai.actual_heat_pump_power_w,
|
||
ai.actual_battery_power_w,
|
||
ai.actual_battery_soc_pct,
|
||
ai.actual_grid_import_wh,
|
||
ai.actual_grid_export_wh,
|
||
ai.actual_batt_charge_wh,
|
||
ai.actual_batt_discharge_wh,
|
||
ai.actual_pv_production_wh,
|
||
ai.actual_load_consumption_wh
|
||
FROM ems.audit_interval ai
|
||
LEFT JOIN ems.vw_site_effective_price ep
|
||
ON ep.site_id = ai.site_id AND ep.interval_start = ai.interval_start
|
||
LEFT JOIN ems.planning_interval pi
|
||
ON pi.run_id = ai.planning_run_id AND pi.interval_start = ai.interval_start;
|
||
|
||
COMMENT ON VIEW ems.vw_economics_interval IS
|
||
'Dynamické ekonomické vyhodnocení per 15min slot.
|
||
import/export kWh díky fn_audit_grid_*_wh_for_economics: primárně Wh z auditu (čítač Deye), u jednosměrného toku max s odhadem z průměrného grid_power_w (¼ h).
|
||
grid_import_cashflow_czk / grid_export_revenue_czk = směrové cashflow podle stejného Wh odhadu × efektivní cena.';
|
||
|
||
CREATE VIEW ems.vw_economics_daily AS
|
||
SELECT
|
||
site_id,
|
||
date_trunc('day', interval_start AT TIME ZONE 'Europe/Prague')::date AS day_local,
|
||
COUNT(*)::int AS interval_count,
|
||
ROUND(SUM(import_kwh), 3) AS import_kwh,
|
||
ROUND(SUM(export_kwh), 3) AS export_kwh,
|
||
ROUND(SUM(GREATEST(actual_pv_power_w, 0)::NUMERIC / 4000), 3) AS pv_kwh,
|
||
ROUND(SUM(GREATEST(actual_load_power_w, 0)::NUMERIC / 4000), 3) AS load_kwh,
|
||
ROUND(SUM(GREATEST(actual_ev_power_w, 0)::NUMERIC / 4000), 3) AS ev_kwh,
|
||
ROUND(SUM(GREATEST(actual_heat_pump_power_w, 0)::NUMERIC / 4000), 3) AS hp_kwh,
|
||
ROUND(SUM(GREATEST(actual_pv_power_w, 0)::NUMERIC / 4000)
|
||
- SUM(export_kwh), 3) AS pv_self_consumption_kwh,
|
||
-- Směrové cashflow (podle směru energie, ne znaménka peněz)
|
||
ROUND(SUM(grid_import_cashflow_czk), 2) AS grid_import_cashflow_czk,
|
||
ROUND(SUM(grid_export_revenue_czk), 2) AS grid_export_revenue_czk,
|
||
-- Staré sloupce (podle znaménka peněz – zpětná kompatibilita)
|
||
ROUND(SUM(CASE WHEN dynamic_cost_czk > 0
|
||
THEN dynamic_cost_czk ELSE 0 END), 2) AS import_cost_czk,
|
||
ROUND(SUM(CASE WHEN dynamic_cost_czk < 0
|
||
THEN ABS(dynamic_cost_czk) ELSE 0 END), 2) AS export_revenue_czk,
|
||
ROUND(SUM(dynamic_cost_czk), 2) AS net_cost_czk,
|
||
ROUND(COALESCE(SUM(green_bonus_czk), 0), 2) AS green_bonus_czk,
|
||
ROUND(-SUM(dynamic_cost_czk)
|
||
+ COALESCE(SUM(green_bonus_czk), 0), 2) AS total_balance_czk,
|
||
ROUND(SUM(planned_cost_czk), 2) AS planned_net_cost_czk,
|
||
ROUND(-COALESCE(SUM(planned_cost_czk), 0), 2) AS planned_balance_czk,
|
||
ROUND(SUM(dynamic_cost_czk)
|
||
- COALESCE(SUM(planned_cost_czk), 0), 2) AS deviation_cost_czk
|
||
FROM ems.vw_economics_interval
|
||
GROUP BY site_id,
|
||
date_trunc('day', interval_start AT TIME ZONE 'Europe/Prague')::date;
|
||
|
||
COMMENT ON VIEW ems.vw_economics_daily IS
|
||
'Denní souhrn ekonomiky. planned_balance_czk = jen síťové náklady (bez zeleného bonusu).
|
||
grid_import_cashflow_czk / grid_export_revenue_czk = směrové cashflow podle skutečného toku energie.';
|