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