40 lines
1.1 KiB
SQL
40 lines
1.1 KiB
SQL
-- @param {String} $1:regionCode
|
|
-- Monthly average electricity price and cumulative datacenter capacity for a region
|
|
WITH months AS (
|
|
SELECT generate_series(
|
|
'2019-01-01'::timestamptz,
|
|
date_trunc('month', now()),
|
|
'1 month'
|
|
) AS month
|
|
),
|
|
monthly_prices AS (
|
|
SELECT
|
|
date_trunc('month', ep.timestamp) AS month,
|
|
AVG(ep.price_mwh) AS avg_price
|
|
FROM electricity_prices ep
|
|
JOIN grid_regions r ON ep.region_id = r.id
|
|
WHERE r.code = $1
|
|
GROUP BY 1
|
|
),
|
|
dc_capacity AS (
|
|
SELECT
|
|
make_timestamptz(d.year_opened, 1, 1, 0, 0, 0) AS opened_month,
|
|
SUM(d.capacity_mw) AS added_mw
|
|
FROM datacenters d
|
|
JOIN grid_regions r ON d.region_id = r.id
|
|
WHERE r.code = $1
|
|
GROUP BY d.year_opened
|
|
)
|
|
SELECT
|
|
m.month,
|
|
mp.avg_price,
|
|
COALESCE(
|
|
SUM(dc.added_mw) OVER (ORDER BY m.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
|
|
0
|
|
)::float AS cumulative_capacity_mw
|
|
FROM months m
|
|
LEFT JOIN monthly_prices mp ON mp.month = m.month
|
|
LEFT JOIN dc_capacity dc ON dc.opened_month <= m.month
|
|
AND dc.opened_month > m.month - INTERVAL '1 month'
|
|
ORDER BY m.month ASC
|