86 lines
3.2 KiB
SQL
86 lines
3.2 KiB
SQL
-- =============================================================
|
||
-- R__061_vw_site_effective_price.sql
|
||
-- EMS Platform – view efektivních cen per site
|
||
-- Repeatable migration
|
||
-- =============================================================
|
||
|
||
CREATE OR REPLACE VIEW ems.vw_site_effective_price AS
|
||
WITH cfg_price AS (
|
||
SELECT
|
||
smc.site_id,
|
||
smc.tariff_id,
|
||
smc.hdo_code_id,
|
||
smc.system_services_czk_kwh,
|
||
smc.buy_margin_fixed_czk,
|
||
smc.buy_margin_percent,
|
||
smc.sell_margin_fixed_czk,
|
||
smc.sell_margin_percent,
|
||
mip.interval_start,
|
||
mip.interval_end,
|
||
mip.market_source,
|
||
mip.buy_raw_price_czk_kwh,
|
||
mip.sell_raw_price_czk_kwh,
|
||
(mip.interval_start AT TIME ZONE 'Europe/Prague')::time AS local_prague_time,
|
||
EXTRACT(DOW FROM mip.interval_start AT TIME ZONE 'Europe/Prague')::integer AS prague_dow
|
||
FROM ems.market_interval_price mip
|
||
CROSS JOIN ems.site_market_config smc
|
||
WHERE smc.valid_from <= mip.interval_start
|
||
AND (smc.valid_to IS NULL OR smc.valid_to > mip.interval_start)
|
||
),
|
||
rated AS (
|
||
SELECT
|
||
cp.*,
|
||
CASE
|
||
WHEN cp.hdo_code_id IS NOT NULL AND EXISTS (
|
||
SELECT 1
|
||
FROM ems.hdo_code_window w
|
||
WHERE w.hdo_code_id = cp.hdo_code_id
|
||
AND (
|
||
w.day_type = 'all'
|
||
OR (w.day_type = 'workday' AND cp.prague_dow BETWEEN 1 AND 5)
|
||
OR (w.day_type = 'weekend' AND cp.prague_dow IN (0, 6))
|
||
)
|
||
AND w.rate_type = 'VT'
|
||
AND cp.local_prague_time >= w.window_from
|
||
AND cp.local_prague_time < w.window_to
|
||
) THEN 'VT'::text
|
||
ELSE 'NT'::text
|
||
END AS rate_type
|
||
FROM cfg_price cp
|
||
)
|
||
SELECT
|
||
r.site_id,
|
||
r.interval_start,
|
||
r.interval_end,
|
||
r.market_source,
|
||
r.buy_raw_price_czk_kwh,
|
||
r.sell_raw_price_czk_kwh,
|
||
r.buy_margin_fixed_czk,
|
||
r.buy_margin_percent,
|
||
r.sell_margin_fixed_czk,
|
||
r.sell_margin_percent,
|
||
ems.fn_effective_buy_price(r.site_id, r.interval_start) AS effective_buy_price_czk_kwh,
|
||
ems.fn_effective_sell_price(r.site_id, r.interval_start) AS effective_sell_price_czk_kwh,
|
||
r.rate_type,
|
||
COALESCE(
|
||
(
|
||
SELECT dtr.price_czk_kwh
|
||
FROM ems.distribution_tariff_rate dtr
|
||
WHERE dtr.tariff_id = r.tariff_id
|
||
AND dtr.rate_type = r.rate_type
|
||
AND dtr.valid_from <= r.interval_start::date
|
||
AND (dtr.valid_to IS NULL OR dtr.valid_to > r.interval_start::date)
|
||
ORDER BY dtr.valid_from DESC
|
||
LIMIT 1
|
||
),
|
||
0::numeric
|
||
) AS dist_rate_czk_kwh,
|
||
COALESCE(r.system_services_czk_kwh, 0::numeric) AS system_services_czk_kwh
|
||
FROM rated r;
|
||
|
||
COMMENT ON VIEW ems.vw_site_effective_price IS
|
||
'Efektivní nákupní a prodejní ceny elektřiny per lokalita a 15min interval.
|
||
rate_type NT/VT dle HDO oken; dist_rate = variabilní distribuce bez DPH.
|
||
effective_buy z fn_effective_buy_price: u spot složky OTE asymetrická procentní marže (kladná raw ×(1+p/100), záporná ×(1−p/100)).
|
||
effective_sell z fn_effective_sell_price (marže bez DPH).';
|