def price_network_query(
time_series: TimeSeries,
group_field: str = "bs.network_id",
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
) -> str:
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
__query = """
select
time_bucket_gapfill('{trunc}', bs.trading_interval) as trading_interval,
{group_field},
avg(bs.price) as price
from balancing_summary bs
where
bs.trading_interval <= '{date_max}' and
bs.trading_interval >= '{date_min}' and
{network_query}
{network_region_query}
1=1
group by 1, 2
order by 1 desc
"""
timezone = time_series.network.timezone_database
network_region_query = ""
if network_region:
network_region_query = f"bs.network_region='{network_region}' and "
group_field = "bs.network_region"
network_query = "bs.network_id IN ({}) and ".format(
networks_to_in(networks_query))
if len(networks_query) > 1:
group_field = "'AU'"
date_max = time_series.get_range().end
date_min = time_series.get_range().start
query = dedent(
__query.format(
network_query=network_query,
trunc=time_series.interval.interval_sql,
network_region_query=network_region_query,
timezone=timezone,
date_max=date_max,
date_min=date_min,
group_field=group_field,
))
return query
def energy_network_fueltech_query(
time_series: TimeSeries,
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
coalesce_with: Optional[int] = 0,
) -> str:
"""
Get Energy for a network or network + region
based on a year
"""
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
__query = """
select
date_trunc('{trunc}', t.trading_day),
t.fueltech_id,
coalesce(sum(t.energy) / 1000, {coalesce_with}) as fueltech_energy,
coalesce(sum(t.market_value), {coalesce_with}) as fueltech_market_value,
coalesce(sum(t.emissions), {coalesce_with}) as fueltech_emissions
from at_facility_daily t
left join facility f on t.facility_code = f.code
where
t.trading_day <= '{date_max}'::date and
t.trading_day >= '{date_min}'::date and
t.fueltech_id not in ('imports', 'exports', 'interconnector') and
{network_query}
{network_region_query}
1=1
group by 1, 2
order by 1 desc;
"""
network_region_query = ""
date_range = time_series.get_range()
if network_region:
network_region_query = f"f.network_region='{network_region}' and"
networks_list = networks_to_in(networks_query)
network_query = "t.network_id IN ({}) and ".format(networks_list)
query = dedent(
__query.format(
trunc=date_range.interval.trunc,
date_min=date_range.start.date(),
date_max=date_range.end.date(),
network_query=network_query,
network_region_query=network_region_query,
coalesce_with=coalesce_with,
))
return query
def demand_network_region_query(time_series: TimeSeries,
network_region: str | None,
networks: list[NetworkSchema] = []) -> str:
"""Get the network demand energy and market_value"""
if not networks:
networks = [time_series.network]
if time_series.network not in networks:
networks.append(time_series.network)
___query = """
select
date_trunc('{trunc}', trading_day) as trading_day,
network_id,
{network_region_select}
round(sum(demand_energy), 4),
round(sum(demand_market_value), 4)
from at_network_demand
where
{network_query}
{network_region}
trading_day >= '{date_min}'::date and
trading_day <= '{date_max}'::date
group by 1,2 {group_by}
order by
1 asc
"""
network_region_query = ""
network_region_select = f"'{time_series.network.code}' as network_region,"
group_by = ""
if network_region:
network_region_query = f"network_region='{network_region}' and"
network_region_select = "network_region,"
group_by = ",3"
date_range = time_series.get_range()
networks_list = networks_to_in(networks)
network_query = "network_id IN ({}) and ".format(networks_list)
query = dedent(
___query.format(
trunc=time_series.interval.trunc,
date_min=date_range.start.date(),
date_max=date_range.end.date(),
network_id=time_series.network.code,
network_region=network_region_query,
network_region_select=network_region_select,
group_by=group_by,
network_query=network_query,
))
return query
def network_demand_query(
time_series: TimeSeries,
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
) -> str:
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
__query = """
select
trading_interval at time zone '{timezone}',
network_id,
max(demand_total) as demand
from balancing_summary bs
where
bs.trading_interval <= '{date_max}' and
bs.trading_interval >= '{date_min}' and
{network_query}
{network_region_query}
1=1
group by
1, {groups_additional}
order by 1 desc;
"""
group_keys = ["network_id"]
network_region_query = ""
if network_region:
group_keys.append("network_region")
network_region_query = f"bs.network_region = '{network_region}' and "
groups_additional = ", ".join(group_keys)
network_query = "bs.network_id IN ({}) and ".format(
networks_to_in(networks_query))
date_max = time_series.get_range().end
date_min = time_series.get_range().start
query = __query.format(
timezone=time_series.network.timezone_database,
date_max=date_max,
date_min=date_min,
network_id=time_series.network.code,
network_query=network_query,
network_region_query=network_region_query,
groups_additional=groups_additional,
)
return dedent(query)
def power_network_rooftop_query(
time_series: TimeSeries,
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
forecast: bool = False,
) -> str:
"""Query power stats"""
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
__query = """
select
time_bucket_gapfill('30 minutes', fs.trading_interval) AS trading_interval,
ft.code as fueltech_code,
{agg_func}(fs.generated) as facility_power
from facility_scada fs
join facility f on fs.facility_code = f.code
join fueltech ft on f.fueltech_id = ft.code
where
{forecast_query}
f.fueltech_id = 'solar_rooftop' and
{network_query}
{network_region_query}
fs.trading_interval <= '{date_max}' and
fs.trading_interval >= '{date_min}'
group by 1, 2
order by 1 desc
"""
network_region_query: str = ""
wem_apvi_case: str = ""
agg_func = "sum"
timezone: str = time_series.network.timezone_database
forecast_query = f"fs.is_forecast is {forecast} and"
if network_region:
network_region_query = f"f.network_region='{network_region}' and "
if NetworkWEM in networks_query:
# silly single case we'll refactor out
# APVI network is used to provide rooftop for WEM so we require it
# in country-wide totals
wem_apvi_case = "or (f.network_id='APVI' and f.network_region='WEM')"
agg_func = "max"
network_query = "(f.network_id IN ({}) {}) and ".format(
networks_to_in(networks_query), wem_apvi_case)
date_max = time_series.get_range().end
date_min = time_series.get_range().start
if forecast:
# @TODO work out what in get_range is mashing this
date_min = time_series.start + timedelta(minutes=30)
date_max = date_min + timedelta(hours=3)
query = dedent(
__query.format(
network_query=network_query,
network_region_query=network_region_query,
timezone=timezone,
date_max=date_max,
date_min=date_min,
forecast_query=forecast_query,
agg_func=agg_func,
))
return query
def power_network_fueltech_query(
time_series: TimeSeries,
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
) -> str:
"""Query power stats"""
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
__query = """
select
time_bucket_gapfill('{trunc}', fs.trading_interval) AS trading_interval,
ft.code as fueltech_code,
coalesce(sum(fs.generated), 0) as fueltech_power
from facility_scada fs
join facility f on fs.facility_code = f.code
join fueltech ft on f.fueltech_id = ft.code
where
fs.is_forecast is False and
f.fueltech_id is not null and
f.fueltech_id not in ({fueltechs_exclude}) and
{network_query}
{network_region_query}
fs.trading_interval <= '{date_max}' and
fs.trading_interval >= '{date_min}'
{fueltech_filter}
group by 1, 2
"""
network_region_query: str = ""
fueltech_filter: str = ""
wem_apvi_case: str = ""
timezone: str = time_series.network.timezone_database
fueltechs_excluded = ["exports", "imports", "interconnector"]
if NetworkNEM in networks_query:
fueltechs_excluded.append("solar_rooftop")
if network_region:
network_region_query = f"f.network_region='{network_region}' and "
if NetworkWEM in networks_query:
# silly single case we'll refactor out
# APVI network is used to provide rooftop for WEM so we require it
# in country-wide totals
wem_apvi_case = "or (f.network_id='APVI' and f.network_region='WEM')"
network_query = "(f.network_id IN ({}) {}) and ".format(
networks_to_in(networks_query), wem_apvi_case)
date_max = time_series.get_range().end
date_min = time_series.get_range().start
fueltechs_exclude = ", ".join("'{}'".format(i) for i in fueltechs_excluded)
query = dedent(
__query.format(
network_query=network_query,
trunc=time_series.interval.interval_sql,
network_region_query=network_region_query,
timezone=timezone,
date_max=date_max,
date_min=date_min,
fueltech_filter=fueltech_filter,
wem_apvi_case=wem_apvi_case,
fueltechs_exclude=fueltechs_exclude,
))
return query
def energy_network_fueltech_query(
time_series: TimeSeries,
network_region: Optional[str] = None,
networks_query: Optional[List[NetworkSchema]] = None,
) -> str:
"""
Get Energy for a network or network + region
based on a year
"""
if not networks_query:
networks_query = [time_series.network]
if time_series.network not in networks_query:
networks_query.append(time_series.network)
if time_series.interval.interval > 1440:
__query = """
select
date_trunc('{trunc}', t.trading_day) as trading_month,
t.fueltech_id,
coalesce(sum(t.fueltech_energy) / 1000 , 0) as fueltech_energy,
coalesce(sum(t.fueltech_market_value), 0) as fueltech_market_value,
coalesce(sum(t.fueltech_emissions), 0) as fueltech_emissions
from
(select
time_bucket_gapfill('1 day', t.ti_day_aest) as trading_day,
t.fueltech_id,
sum(t.energy) as fueltech_energy,
sum(t.market_value) as fueltech_market_value,
sum(t.emissions) as fueltech_emissions
from mv_facility_all t
where
t.ti_day_aest <= '{date_max}' and
t.ti_day_aest >= '{date_min}' and
t.fueltech_id not in ('imports', 'exports') and
{network_query}
{network_region_query}
1=1
group by 1, 2) as t
group by 1, 2
order by
1 desc;
"""
else:
__query = """
select
t.ti_{trunc_name} as trading_day,
t.fueltech_id,
coalesce(sum(t.energy) / 1000, 0) as fueltech_energy,
coalesce(sum(t.market_value), 0) as fueltech_market_value,
coalesce(sum(t.emissions), 0) as fueltech_emissions
from mv_facility_all t
where
t.trading_interval <= '{date_max}' and
t.trading_interval >= '{date_min}' and
t.fueltech_id not in ('imports', 'exports') and
{network_query}
{network_region_query}
1=1
group by 1, 2
order by
trading_day desc;
"""
network_region_query = ""
date_range = time_series.get_range()
if network_region:
network_region_query = f"t.network_region='{network_region}' and"
networks_list = networks_to_in(networks_query)
network_query = "t.network_id IN ({}) and ".format(networks_list)
trunc_name = "{}_{}".format(
time_series.interval.trunc, time_series.network.timezone_database
).lower()
query = dedent(
__query.format(
trunc=date_range.interval.trunc,
trunc_name=trunc_name,
date_min=date_range.start,
date_max=date_range.end,
network_query=network_query,
network_region_query=network_region_query,
)
)
return query