def analysis_cf_comparison(self): exec_str = ''' DROP TABLE IF EXISTS {sc_out}.analysis_cf_comparison CASCADE; SELECT mt_id, pp_id, ca_id, run_id, 'var'::VARCHAR AS var_par, value / NULLIF(cap * 8760, 0) AS cf INTO {sc_out}.analysis_cf_comparison FROM {sc_out}.var_mt_erg_mt NATURAL LEFT JOIN ( SELECT pp_id, ca_id, run_id, value AS cap FROM {sc_out}.par_cap_pwr_leg) AS cap UNION ALL SELECT mt_id, pp_id, ca_id, run_id, 'par'::VARCHAR AS var_par, value AS cf FROM {sc_out}.par_cf_max '''.format(**self.format_kw) aql.exec_sql(exec_str, db=self.db) aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, 'analysis_cf_comparison'], [self.sc_out, 'def_run']) aql.joinon(self.db, ['pp', 'nd_id', 'pt_id', 'fl_id'], ['pp_id'], [self.sc_out, 'analysis_cf_comparison'], [self.sc_out, 'def_plant']) aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, 'analysis_cf_comparison'], [self.sc_out, 'def_node']) aql.joinon(self.db, ['pt'], ['pt_id'], [self.sc_out, 'analysis_cf_comparison'], [self.sc_out, 'def_pp_type']) aql.joinon(self.db, ['fl'], ['fl_id'], [self.sc_out, 'analysis_cf_comparison'], [self.sc_out, 'def_fuel'])
def wrapper(self, *args, **kwargs): f(self, *args, **kwargs) if len(self.sw_columns) > 0: aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, func_name], [self.sc_out, 'def_loop'], verbose=True)
def generate_analysis_time_series(self, energy_only=False): ''' Generates basic (full time resolution) x (pp_type) x (run_id) table. ''' print(self.in_run_id) tb_name = 'analysis_time_series' self.generate_view_time_series_subset() aql.init_table(tb_name, ([ 'sy', 'ca_id', 'pp_id', 'bool_out', 'run_id', 'pwrerg_cat', 'nd_id', 'fl_id', 'pt_id', *self.tm_cols, ('value', 'DECIMAL'), 'fl', 'nd', 'pt', ('value_posneg', 'DECIMAL') ] + [(c, 'VARCHAR') for c in self.sw_columns]), schema=self.sc_out, ref_schema=self.sc_out, db=self.db) if not energy_only: print('Inserting power...') exec_str = (''' INSERT INTO {sc_out}.{tb_name} (sy, ca_id, pp_id, bool_out, value, run_id, pwrerg_cat, value_posneg) SELECT * FROM {sc_out}.analysis_time_series_view_power WHERE run_id IN {in_run_id}; ''').format(tb_name=tb_name, **self.format_kw) aql.exec_sql(exec_str, db=self.db) print('Inserting cross-sector consumption...') exec_str = (''' INSERT INTO {sc_out}.{tb_name} (sy, ca_id, pp_id, bool_out, value, run_id, pwrerg_cat, value_posneg) SELECT * FROM {sc_out}.analysis_time_series_view_crosssector WHERE run_id IN {in_run_id}; ''').format(tb_name=tb_name, **self.format_kw) aql.exec_sql(exec_str, db=self.db) if 'var_sy_erg_st' in aql.get_sql_tables(self.sc_out, self.db): print('Inserting energy...') exec_str = (''' INSERT INTO {sc_out}.{tb_name} (sy, ca_id, pp_id, bool_out, value, run_id, pwrerg_cat, value_posneg) SELECT * FROM {sc_out}.analysis_time_series_view_energy WHERE run_id IN {in_run_id}; ''').format(tb_name=tb_name, **self.format_kw) aql.exec_sql(exec_str, db=self.db) # add timemap indices aql.joinon(self.db, [c for c in self.tm_cols if (not c == 'sy')], ['sy'], [self.sc_out, tb_name], [self.sc_out, 'tm_soy_full'], new_columns=False) return exec_str
def generate_complete_dual_supply(self): ''' Adds loop and sy columns to the dual_supply table.''' print(self.in_run_id) tb_name = 'analysis_time_series_dual_supply' aql.init_table( tb_name, (['sy', 'nd_id', 'ca_id', 'run_id', 'value', 'nd', *self.tm_cols] + [(c, 'VARCHAR') for c in self.sw_columns] + [('mc', 'DECIMAL')]), schema=self.sc_out, ref_schema=self.sc_out, pk=['sy', 'nd_id', 'run_id'], db=self.db) exec_str_0 = (''' INSERT INTO {sc_out}.{tb_name} (sy, nd_id, ca_id, value, run_id) SELECT * FROM {sc_out}.dual_supply WHERE run_id in {in_run_id} ''').format(tb_name=tb_name, sc_out=self.sc_out, in_run_id=self.in_run_id) aql.exec_sql(exec_str_0, db=self.db) # add timemap indices aql.joinon(self.db, [c for c in self.tm_cols if not c == 'sy'], ['sy'], [self.sc_out, 'analysis_time_series_dual_supply'], [self.sc_out, 'tm_soy_full'], new_columns=False) exec_str_1 = (''' UPDATE {sc_out}.analysis_time_series_dual_supply SET mc = value / weight; '''.format(sc_out=self.sc_out)) aql.exec_sql(exec_str_1, db=self.db) return (exec_str_0 + exec_str_1)
def analysis_price_comparison(self, valmin=-20, valmax=150, nbins=170): ''' Merge the supply constraint shadow prices with the historic electricity prices and bin them. Output tables are -- analysis_price_comparison: Complete hourly model and historic electricity prices with corresponding bins. ''' self.format_kw.update(dict(valmin=valmin, valmax=valmax, nbins=nbins)) exec_str = ''' DROP TABLE IF EXISTS {sc_out}.analysis_price_comparison CASCADE; DROP VIEW IF EXISTS {sc_out}._view_analysis_prices_complete CASCADE; DROP VIEW IF EXISTS {sc_out}._view_analysis_prices_stats_0 CASCADE; DROP VIEW IF EXISTS {sc_out}._view_analysis_prices_stats_at CASCADE; CREATE VIEW {sc_out}._view_analysis_prices_stats_0 AS SELECT hy, nd_id, ca_id, price_eur_mwh AS price, volume_mwh AS volume, price_eur_mwh * volume_mwh AS price_volume, -1 AS run_id, 'stats' AS sta_mod FROM {sc_out}.profprice_comp NATURAL LEFT JOIN {sc_out}.def_run WHERE swhy_vl = 'yr2015'; /* Double Germany for Austria */ CREATE VIEW {sc_out}._view_analysis_prices_stats_at AS SELECT hy, (SELECT nd_id FROM {sc_out}.def_node WHERE nd = 'AT0') AS nd_id, ca_id, price, volume, price_volume, run_id, sta_mod FROM {sc_out}._view_analysis_prices_stats_0 WHERE nd_id IN (SELECT nd_id FROM {sc_out}.def_node WHERE nd = 'DE0'); CREATE VIEW {sc_out}._view_analysis_prices_complete AS SELECT hy, nd_id, ca_id, value / weight AS price, volume * weight AS volume, value * volume AS price_volume, run_id, 'model' AS sta_mod FROM {sc_out}.hoy_soy NATURAL LEFT JOIN {sc_out}.dual_supply NATURAL LEFT JOIN {sc_out}.tm_soy NATURAL LEFT JOIN ( SELECT sy, nd_id, run_id, SUM(value) AS volume FROM {sc_out}.var_sy_pwr NATURAL LEFT JOIN {sc_out}.def_plant WHERE bool_out=False GROUP BY sy, nd_id, run_id) AS tb_volume UNION ALL SELECT * FROM {sc_out}._view_analysis_prices_stats_0 UNION ALL SELECT * FROM {sc_out}._view_analysis_prices_stats_at ; WITH table_complete_binned AS ( SELECT tb_complete.*, WIDTH_BUCKET(tb_complete.price, {valmin}, {valmax}, {nbins}) AS bucket FROM {sc_out}._view_analysis_prices_complete AS tb_complete ), bucket_list AS ( SELECT {valmin} + (bucket - 1) * ({valmax} - {valmin})::FLOAT / {nbins}::FLOAT AS low, {valmin} + (bucket) * ({valmax} - {valmin})::FLOAT / {nbins}::FLOAT AS high, ({valmax} - {valmin}) / {nbins} AS bin_width, bucket FROM (SELECT generate_series(0, {nbins} + 1) AS bucket, 1 AS dummy) AS bcks ) SELECT *, 0.5 * (low + high) AS bin_center, CASE WHEN price < low THEN 1 ELSE 0 END AS check_low, CASE WHEN price > high THEN 1 ELSE 0 END AS check_high INTO {sc_out}.analysis_price_comparison FROM table_complete_binned NATURAL LEFT JOIN bucket_list '''.format(**self.format_kw) aql.exec_sql(exec_str, db=self.db) aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, 'analysis_price_comparison'], [self.sc_out, 'def_run']) aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, 'analysis_price_comparison'], [self.sc_out, 'def_node']) aql.joinon(self.db, ['ca', 'fl_id'], ['ca_id'], [self.sc_out, 'analysis_price_comparison'], [self.sc_out, 'def_encar']) aql.joinon(self.db, ['fl'], ['fl_id'], [self.sc_out, 'analysis_price_comparison'], [self.sc_out, 'def_fuel']) exec_strg = ''' ALTER TABLE {sc_out}.analysis_price_comparison ADD COLUMN IF NOT EXISTS season VARCHAR(20), ADD COLUMN IF NOT EXISTS dow_name VARCHAR(10), ADD COLUMN IF NOT EXISTS hour SMALLINT, ADD COLUMN IF NOT EXISTS wom SMALLINT, ADD COLUMN IF NOT EXISTS hom SMALLINT, ADD COLUMN IF NOT EXISTS mt_id SMALLINT, ADD COLUMN IF NOT EXISTS how SMALLINT; UPDATE {sc_out}.analysis_price_comparison AS prc SET season = tm.season, dow_name = tm.dow_name, hour = tm.hour, wom = tm.wom, hom = tm.hom, mt_id = tm.mt_id, how = tm.how FROM {sc_out}.tm_soy_full AS tm WHERE prc.hy = tm.sy; '''.format(**self.format_kw) aql.exec_sql(exec_strg, db=self.db) exec_str = ''' DROP TABLE IF EXISTS {sc_out}.analysis_price_weighted; SELECT nd_id, ca_id, run_id, sta_mod, SUM(price * volume) / SUM(volume) AS price_weighted, AVG(price) AS price_averaged INTO {sc_out}.analysis_price_weighted FROM {sc_out}._view_analysis_prices_complete GROUP BY nd_id, ca_id, run_id, sta_mod; '''.format(**self.format_kw) aql.exec_sql(exec_str, db=self.db) aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, 'analysis_price_weighted'], [self.sc_out, 'def_run']) aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, 'analysis_price_weighted'], [self.sc_out, 'def_node'])
def analysis_production_comparison(self): ''' This merges the model results from sc_out.var_yr_erg_yr with the input data erg_inp from sc_out.fuel_node_encar as well as the inter-node transmission from lp_input.imex_comp with for comparison and calibration. TODO: Copy imex_comp to the output schema!!! ''' # get model results exec_str = ''' DROP TABLE IF EXISTS {sc_out}.analysis_production_comparison CASCADE; SELECT fl_id, nd_id, ca_id, value, bool_out, 'model'::VARCHAR AS sta_mod, run_id INTO {sc_out}.analysis_production_comparison FROM {sc_out}.var_yr_erg_yr AS erg NATURAL LEFT JOIN {sc_out}.def_run AS dflp NATURAL LEFT JOIN {sc_out}.def_plant AS dfpp WHERE run_id IN (0, -1) ; '''.format(**self.format_kw) aql.exec_sql(exec_str, db=self.db) # add stats df_erg_inp = aql.read_sql(self.db, self.sc_out, 'fuel_node_encar').set_index( ['fl_id', 'nd_id', 'ca_id']) df_erg_inp = df_erg_inp[[ c for c in df_erg_inp.columns if 'erg_inp' in c ]] df_erg_inp = df_erg_inp.stack().reset_index().rename(columns={ 'level_3': 'swhy_vl', 0: 'value' }) df_erg_inp['swhy_vl'] = df_erg_inp['swhy_vl'].replace({ 'erg_inp': 'erg_inp_yr2015' }).map(lambda x: x[-6:]) df_erg_inp['sta_mod'] = 'stats' df_erg_inp['run_id'] = -1 df_erg_inp['bool_out'] = False # add imex stats # if 'export' in self.mps.dict_fl_id.keys(): # # df_imex = aql.read_sql(self.db, self.sc_out, 'imex_comp') # # df_imex = df_imex.set_index(['nd_id', 'nd_2_id']).stack().reset_index() # df_imex = df_imex.rename(columns={'level_2': 'swhy_vl', 0: 'value'}) # # df_imex['value'] *= 1000 # # df_imex['swhy_vl'] = df_imex['swhy_vl'].replace({'erg_trm': 'erg_trm_yr2015'}).map(lambda x: x[-6:]) # df_imex['sta_mod'] = 'stats' # df_imex['ca_id'] = self.mps.dict_ca_id['EL'] # df_imex = df_imex.join(df_swhy, on=df_swhy.index.names) # df_imex = df_imex.loc[-df_imex.run_id.apply(np.isnan)].drop('swhy_vl', axis=1) # # df_imex_exp = df_imex.groupby(['nd_id', 'sta_mod', 'ca_id', 'run_id'])['value'].sum().reset_index() # df_imex_exp['fl_id'] = self.mps.dict_fl_id['export'] # df_imex_exp['bool_out'] = True # df_imex_imp = df_imex.groupby(['nd_2_id', 'sta_mod', 'ca_id', 'run_id'])['value'].sum().reset_index() # df_imex_imp['fl_id'] = self.mps.dict_fl_id['import'] # df_imex_imp['bool_out'] = False # df_imex_imp = df_imex_imp.rename(columns={'nd_2_id': 'nd_id'}) # else: # df_imex_imp = pd.DataFrame() # df_imex_exp = pd.DataFrame() df_erg_inp = pd.concat([df_erg_inp]) aql.write_sql(df_erg_inp, self.db, self.sc_out, 'analysis_production_comparison', 'append') aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, 'analysis_production_comparison'], [self.sc_out, 'def_run']) aql.joinon(self.db, ['fl'], ['fl_id'], [self.sc_out, 'analysis_production_comparison'], [self.sc_out, 'def_fuel']) aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, 'analysis_production_comparison'], [self.sc_out, 'def_node'])
def analysis_production_comparison_hourly(self, stats_years=['2015'], sy_only=False): # generating model data time series self.generate_analysis_time_series(False) # rename original table to _soy and expand to hours exec_strg = ''' DROP TABLE IF EXISTS {sc_out}.analysis_time_series_soy CASCADE; ALTER TABLE {sc_out}.analysis_time_series RENAME TO analysis_time_series_soy; '''.format(**self.format_kw) aql.exec_sql(exec_strg, db=self.db) if not sy_only: exec_strg = ''' SELECT run_id, bool_out, fl, nd, {sw_year_col}, hy AS sy, value, value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, 'model'::VARCHAR AS sta_mod, pwrerg_cat INTO {sc_out}.analysis_time_series FROM {sc_out}.hoy_soy AS hs LEFT JOIN {sc_out}.analysis_time_series_soy AS ts ON hs.sy = ts.sy; '''.format(**self.format_kw) aql.exec_sql(exec_strg, db=self.db) # insert rows of entsoe data after adding some convenience columns exec_strg = ''' ALTER TABLE {sc_out}.analysis_time_series DROP CONSTRAINT IF EXISTS analysis_time_series_fl_fkey; DELETE FROM {sc_out}.analysis_time_series WHERE sta_mod <> 'model'; INSERT INTO {sc_out}.analysis_time_series(run_id, bool_out, fl, nd, {sw_year_col}, sy, value, value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, sta_mod, pwrerg_cat) SELECT -1::SMALLINT AS run_id, False::BOOLEAN AS bool_out, --'EL' AS ca, fl_id AS fl, nd_id AS nd, 'yr' || tm.year::VARCHAR AS {sw_year_col}, tm.hy AS sy, value, value AS value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, 'stats_entsoe'::VARCHAR AS sta_mod, 'pwr'::VARCHAR AS pwrerg_cat FROM profiles_raw.entsoe_generation AS ent LEFT JOIN {sc_out}.tm_soy_full AS tm ON tm.sy = ent.hy WHERE ent.year IN ({st_yr}) AND ent.nd_id IN {in_nd}; INSERT INTO {sc_out}.analysis_time_series(run_id, bool_out, fl, nd, {sw_year_col}, sy, value, value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, sta_mod, pwrerg_cat) SELECT -1::SMALLINT AS run_id, False::BOOLEAN AS bool_out, --'EL' AS ca, fl_id AS fl, nd_id AS nd, 'yr' || tm.year::VARCHAR AS {sw_year_col}, tm.hy AS sy, value, value AS value_posneg, dow, dow_type, hom, hour, how, ent.mt_id, season, wk_id, wom, 'stats_rte_eco2mix'::VARCHAR AS sta_mod, 'pwr'::VARCHAR AS pwrerg_cat FROM profiles_raw.rte_production_eco2mix AS ent LEFT JOIN {sc_out}.tm_soy_full AS tm ON tm.sy = ent.hy WHERE ent.year IN ({st_yr}); INSERT INTO {sc_out}.analysis_time_series(run_id, bool_out, fl, nd, {sw_year_col}, sy, value, value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, sta_mod, pwrerg_cat) SELECT -1::SMALLINT AS run_id, False::BOOLEAN AS bool_out, --'EL' AS ca, fl_id AS fl, nd_id AS nd, 'yr' || tb.year::VARCHAR AS {sw_year_col}, ts.slot AS sy, CASE WHEN fl_id = 'dmnd' THEN -1 ELSE 1 END * 1000 * value, CASE WHEN fl_id = 'dmnd' THEN -1 ELSE 1 END * 1000 * value AS value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, 'stats_agora'::VARCHAR AS sta_mod, 'pwr'::VARCHAR AS pwrerg_cat FROM profiles_raw.agora_profiles AS tb LEFT JOIN (SELECT datetime, slot FROM profiles_raw.timestamp_template WHERE year IN ({st_yr})) AS ts ON ts.datetime = tb."DateTime" LEFT JOIN {sc_out}.tm_soy_full AS tm ON tm.sy = ts.slot WHERE tb.year IN ({st_yr}); WITH tb_raw AS ( SELECT nd_to AS nd, 'import_' || nd_from AS fl, False::BOOLEAN AS bool_out, value, year, hy FROM profiles_raw.entsoe_cross_border UNION ALL SELECT nd_from AS nd, 'export_' || nd_to AS fl, True::BOOLEAN AS bool_out, -value AS value, year, hy FROM profiles_raw.entsoe_cross_border ) INSERT INTO {sc_out}.analysis_time_series(run_id, bool_out, fl, nd, {sw_year_col}, sy, value, value_posneg, dow, dow_type, hom, hour, how, mt_id, season, wk_id, wom, sta_mod, pwrerg_cat) SELECT -1::SMALLINT AS run_is, bool_out, fl, nd, 'yr2015'::VARCHAR AS {sw_year_col}, tb_raw.hy AS sy, value, value AS value_posneg, dow, 'NONE'::VARCHAR AS dow_type, hom, hour, how, mt_id, season, EXTRACT(week FROM datetime)::SMALLINT - 1 AS wk_id, wom, 'stats_imex_entsoe'::VARCHAR AS sta_mod, 'pwr'::VARCHAR AS pwrerg_cat FROM (SELECT * FROM profiles_raw.timestamp_template WHERE year = 2015) AS ts LEFT JOIN tb_raw ON tb_raw.year = ts.year AND tb_raw.hy = ts.slot '''.format(**self.format_kw, st_yr=', '.join(stats_years)) aql.exec_sql(exec_strg, db=self.db) aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, 'analysis_time_series'], [self.sc_out, 'def_run']) for col in self.sw_columns: exec_strg = ''' UPDATE {sc_out}.analysis_time_series SET {col} = 'none' WHERE {col} IS NULL; '''.format(**self.format_kw, col=col) aql.exec_sql(exec_strg, db=self.db)
def generate_analysis_time_series_transmission(self): exec_str = (''' DROP VIEW IF EXISTS analysis_time_series_transmission_0 CASCADE; DROP TABLE IF EXISTS {sc_out}.analysis_time_series_transmission CASCADE; CREATE VIEW analysis_time_series_transmission_0 AS WITH trrv AS ( SELECT sy, run_id, dfnd.nd AS nd, CAST('IMPORT_FROM_' AS VARCHAR) || dfnd_2.nd AS pt, False AS bool_out, CAST('TRANSMISSION' AS VARCHAR) AS pp_broad_cat, CAST('import' AS VARCHAR) AS fl, value, value AS value_posneg FROM {sc_out}.var_tr_trm_rv AS tr LEFT JOIN {sc_out}.def_node AS dfnd ON dfnd.nd_id = tr.nd_id LEFT JOIN {sc_out}.def_node AS dfnd_2 ON dfnd_2.nd_id = tr.nd_2_id WHERE run_id IN {in_run_id} ), trsd AS ( SELECT sy, run_id, dfnd.nd AS nd, CAST('EXPORT_TO_' AS VARCHAR) || dfnd_2.nd AS pt, True AS bool_out, CAST('TRANSMISSION' AS VARCHAR) AS pp_broad_cat, CAST('export' AS VARCHAR) AS fl, value, - value AS value_posneg FROM {sc_out}.var_tr_trm_sd AS tr LEFT JOIN {sc_out}.def_node AS dfnd ON dfnd.nd_id = tr.nd_id LEFT JOIN {sc_out}.def_node AS dfnd_2 ON dfnd_2.nd_id = tr.nd_2_id WHERE run_id IN {in_run_id} ), tm AS ( SELECT * FROM profiles_raw.timestamp_template WHERE year = 2015 ), trall AS ( SELECT * FROM trrv UNION ALL SELECT * FROM trsd ) SELECT 0::SMALLINT AS bool_out, trall.value, trall.run_id, tm.*, trall.pt, trall.pp_broad_cat, trall.nd, trall.fl, trall.value_posneg FROM trall LEFT JOIN tm ON tm.slot = trall.sy; SELECT * INTO {sc_out}.analysis_time_series_transmission{sff} FROM {sc_out}.analysis_time_series WHERE NOT pp LIKE '%TRNS%' UNION ALL SELECT * FROM analysis_time_series_transmission_0; ''').format(sc_out=self.sc_out, in_run_id=self.in_run_id, sff=self._suffix) aql.exec_sql(exec_str, db=self.db) # add timemap indices aql.joinon( self.db, [c for c in self.tm_cols if (not c == 'sy')], ['sy'], ['public', 'analysis_time_series_transmission' + self._suffix], ['public', 'timemap_' + str(self.time_res)]) return exec_str
def analysis_agg_filtdiff(self): ''' New style. ''' list_sw_not_st = [c for c in self.sw_columns if not c == 'swst_vl'] sw_not_st = (', '.join(list_sw_not_st) + (', ' if len(list_sw_not_st) > 0 else '')) join_ref = ' AND ' + '\nAND '.join( ['tbrel.{} = tbrf.{}'.format(c, c) for c in list_sw_not_st]) slct_pp_id_st = aql.read_sql(self.db, self.sc_out, 'def_plant', filt=[('pp', ['%STO%'], ' LIKE '), ('pp_id', self.slct_pp_id), ('nd_id', self._nd_id) ])['pp_id'].tolist() lst_pp_id_st = self.list_to_str(slct_pp_id_st) slct_pp_id_non_st = [ pp for pp in self.slct_pp_id if not pp in slct_pp_id_st ] lst_pp_id_non_st = self.list_to_str(slct_pp_id_non_st) run_id_zero_st = self.list_to_str( aql.read_sql(self.db, self.sc_out, 'def_loop', filt=[('swst', [0]), ('run_id', self.slct_run_id)])['run_id']) run_id_nonzero_st = self.list_to_str( aql.read_sql(self.db, self.sc_out, 'def_loop', filt=[('swst', [0], ' <> '), ('run_id', self.slct_run_id)])['run_id']) self.format_kw.update({ 'sw_not_st': sw_not_st, 'join_ref': join_ref, 'lst_pp_id_st': lst_pp_id_st, 'lst_pp_id_non_st': lst_pp_id_non_st, 'run_id_zero_st': run_id_zero_st, 'run_id_nonzero_st': run_id_nonzero_st, 'out_tb': 'analysis_agg_filtdiff' }) cols = aql.init_table('analysis_agg_filtdiff', [ ('sy', 'SMALLINT'), ('pp_id', 'SMALLINT'), ('bool_out', 'BOOLEAN'), ('run_id', 'SMALLINT'), ('run_id_rf', 'SMALLINT'), ('pp_id_st', 'SMALLINT'), ('bool_out_st', 'BOOLEAN'), ('value_st', 'DOUBLE PRECISION'), ('value', 'DOUBLE PRECISION'), ('value_ref', 'DOUBLE PRECISION'), ('value_diff', 'DOUBLE PRECISION'), ], schema=self.sc_out, ref_schema=self.sc_out, pk=[ 'sy', 'pp_id', 'bool_out', 'run_id', 'run_id_rf', 'pp_id_st', 'bool_out_st' ], db=self.db) exec_strg = ''' /* CREATE MAP BETWEEN run_ids and reference run_ids */ DROP VIEW IF EXISTS temp_map_run_id_ref CASCADE; CREATE VIEW temp_map_run_id_ref AS WITH ref_st AS ( SELECT swvr_vl, swtc_vl, swpt_vl, swyr_vl, swco_vl, run_id AS run_id_rf FROM {sc_out}.def_loop WHERE swst_vl = '0.00%' AND run_id IN {run_id_zero_st} ) SELECT dflp.run_id, ref_st.run_id_rf FROM {sc_out}.def_loop AS dflp NATURAL JOIN ref_st WHERE run_id IN {run_id_nonzero_st} ORDER BY run_id; '''.format(**self.format_kw) aql.exec_sql(exec_strg, db=self.db) exec_strg = ''' /* FILTER TIME SERIES TABLE */ DROP TABLE IF EXISTS temp_analysis_time_series_subset CASCADE; SELECT sy, ca_id, nd_id, ts.pp_id, pp, bool_out, value, ts.run_id, value_posneg, run_id_rf INTO temp_analysis_time_series_subset FROM {sc_out}.analysis_time_series_view_power AS ts LEFT JOIN {sc_out}.def_plant AS dfpp ON dfpp.pp_id = ts.pp_id LEFT JOIN {sc_out}.def_loop AS dflp ON dflp.run_id = ts.run_id LEFT JOIN {sc_out}.def_pp_type AS dfpt ON dfpt.pt_id = dfpp.pt_id LEFT JOIN temp_map_run_id_ref AS run_id_ref ON run_id_ref.run_id = ts.run_id WHERE dfpp.pp_id IN {list_slct_pp_id} AND ts.run_id IN {in_run_id}; '''.format(**self.format_kw) aql.exec_sql(exec_strg, db=self.db) exec_strg = ''' WITH mask_st AS ( SELECT sy, pp_id AS pp_id_st, bool_out AS bool_out_st, value AS value_st, run_id FROM temp_analysis_time_series_subset WHERE pp_id IN {lst_pp_id_st} AND run_id IN (SELECT DISTINCT run_id FROM temp_map_run_id_ref WHERE NOT run_id IN (SELECT DISTINCT run_id_rf FROM temp_map_run_id_ref)) ), tb_pp AS ( SELECT sy, pp_id, bool_out, value AS value, run_id, run_id_rf FROM temp_analysis_time_series_subset WHERE pp_id IN {lst_pp_id_non_st} AND run_id IN (SELECT DISTINCT run_id FROM temp_map_run_id_ref WHERE NOT run_id IN (SELECT DISTINCT run_id_rf FROM temp_map_run_id_ref)) ), tb_pp_ref AS ( SELECT sy, pp_id, bool_out, value AS value_ref, run_id_rf FROM temp_analysis_time_series_subset WHERE pp_id IN {lst_pp_id_non_st} AND run_id IN (SELECT DISTINCT run_id_rf FROM temp_map_run_id_ref) ) INSERT INTO {sc_out}.{out_tb} ({cols}) SELECT tb_pp.sy, tb_pp.pp_id, tb_pp.bool_out, tb_pp.run_id, tb_pp.run_id_rf, pp_id_st, bool_out_st, value_st, value, value_ref, value - value_ref AS value_diff FROM tb_pp LEFT JOIN tb_pp_ref ON tb_pp.sy = tb_pp_ref.sy AND tb_pp.pp_id = tb_pp_ref.pp_id AND tb_pp.bool_out = tb_pp_ref.bool_out AND tb_pp.run_id_rf = tb_pp_ref.run_id_rf FULL OUTER JOIN mask_st ON mask_st.sy = tb_pp.sy AND mask_st.run_id = tb_pp.run_id WHERE ABS(value_st) > 1; '''.format(**self.format_kw, cols=cols) aql.exec_sql(exec_strg, db=self.db) aql.joinon(self.db, ['mt_id', 'season'], ['sy'], [self.sc_out, 'analysis_agg_filtdiff'], [self.sc_out, 'tm_soy_full']) # aggregated table cols = aql.init_table('analysis_agg_filtdiff_agg', [ ('pp_id', 'SMALLINT'), ('bool_out', 'BOOLEAN'), ('run_id', 'SMALLINT'), ('run_id_rf', 'SMALLINT'), ('pp_id_st', 'SMALLINT'), ('bool_out_st', 'BOOLEAN'), ('value_diff_agg', 'DOUBLE PRECISION'), ], schema=self.sc_out, ref_schema=self.sc_out, pk=[ 'pp_id', 'bool_out', 'run_id', 'run_id_rf', 'pp_id_st', 'bool_out_st' ], db=self.db) exec_strg = ''' INSERT INTO {sc_out}.analysis_agg_filtdiff_agg ({cols}) SELECT pp_id, bool_out, run_id, run_id_rf, pp_id_st, bool_out_st, SUM(value_diff) AS value_diff_agg FROM {sc_out}.analysis_agg_filtdiff GROUP BY pp_id, bool_out, run_id, run_id_rf, pp_id_st, bool_out_st '''.format(**self.format_kw, cols=cols) aql.exec_sql(exec_strg, db=self.db) for tb in ['analysis_agg_filtdiff_agg', 'analysis_agg_filtdiff']: aql.joinon(self.db, { 'pp': 'pp_st', 'pt_id': 'pt_id_st' }, {'pp_id': 'pp_id_st'}, [self.sc_out, tb], [self.sc_out, 'def_plant']) aql.joinon(self.db, {'pt': 'pt_st'}, {'pt_id': 'pt_id_st'}, [self.sc_out, tb], [self.sc_out, 'def_pp_type'])
def wrapper(self, *args, **kwargs): f(self, *args, **kwargs) aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, f.__name__], [self.sc_out, 'def_node'])
def chgdch_filtered_difference(self, use_views_only): ''' This query applies a binary filter charging for charging/discharging and subtracts the zero-storage case for each hour. ''' list_sw_not_st = [c for c in self.sw_columns if not c == 'swst_vl'] sw_not_st = ', '.join(list_sw_not_st) + ( ', ' if len(list_sw_not_st) > 0 else '') join_ref = ' AND ' + '\nAND '.join( ['tbrel.{} = tbrf.{}'.format(c, c) for c in list_sw_not_st]) slct_pp_id_st = aql.read_sql(self.db, self.sc_out, 'def_plant', filt=[('pp', ['%STO%'], ' LIKE '), ('nd_id', self._nd_id)])['pp_id'] lst_pp_id_st = self.list_to_str(slct_pp_id_st) slct_pp_id_non_st = [ pp for pp in self.slct_pp_id if not pp in slct_pp_id_st.tolist() ] lst_pp_id_non_st = self.list_to_str(slct_pp_id_non_st) run_id_zero_st = self.list_to_str( aql.read_sql(self.db, self.sc_out, 'def_loop', filt=[('swst', [0]), ('run_id', self.slct_run_id)])['run_id']) run_id_nonzero_st = self.list_to_str( aql.read_sql(self.db, self.sc_out, 'def_loop', filt=[('swst', [0], ' <> '), ('run_id', self.slct_run_id)])['run_id']) self.format_kw.update({ 'sw_not_st': sw_not_st, 'join_ref': join_ref, 'lst_pp_id_st': lst_pp_id_st, 'lst_pp_id_non_st': lst_pp_id_non_st, 'run_id_zero_st': run_id_zero_st, 'run_id_nonzero_st': run_id_nonzero_st }) if use_views_only: self.format_kw[ 'out_tb'] = 'analysis_agg_filtdiff_views' + self._suffix else: self.format_kw[ 'out_tb'] = 'analysis_agg_filtdiff_tables' + self._suffix if not use_views_only: aql.init_table( 'temp_analysis_time_series_subset', [ 'sy', 'ca_id', 'pp_id', 'bool_out', 'value', 'run_id', ('value_posneg', 'DECIMAL'), ('value_diff', 'DECIMAL'), # ('value_ref', 'DECIMAL') ], ref_schema=self.sc_out) exec_str = ''' /* FILTER TIME SERIES TABLE */ INSERT INTO temp_analysis_time_series_subset (sy, ca_id, pp_id, bool_out, value, run_id, value_posneg) SELECT sy, ca_id, ts.pp_id, bool_out, value, ts.run_id, value_posneg FROM {sc_out}.analysis_time_series_view_power AS ts LEFT JOIN {sc_out}.def_plant AS dfpp ON dfpp.pp_id = ts.pp_id LEFT JOIN {sc_out}.def_loop AS dflp ON dflp.run_id = ts.run_id LEFT JOIN {sc_out}.def_pp_type AS dfpt ON dfpt.pt_id = dfpp.pt_id WHERE (ts.pp_id NOT IN {lst_pp_id_st} or pp LIKE '%HYD_STO' OR pt LIKE swtc_vl||'_STO') AND dfpp.pp_id IN {list_slct_pp_id}; '''.format(**self.format_kw) # print(exec_str) aql.exec_sql(exec_str, time_msg='Filter var_sy_pwr and write to table') aql.init_table('temp_tbrf', [ 'sy', 'pp_id', 'bool_out', 'run_id', ('value_ref', 'DECIMAL') ], ref_schema=self.sc_out) exec_str = ''' /* CREATE TABLE WITH NO-STORAGE REFERENCE VALUES */ INSERT INTO temp_tbrf (sy, pp_id, bool_out, run_id, value_ref) SELECT sy, pp_id, bool_out, run_id, value AS value_ref FROM temp_analysis_time_series_subset WHERE run_id IN {run_id_zero_st}; '''.format(**self.format_kw) aql.exec_sql(exec_str, time_msg='Write data to ref table') aql.exec_sql('''ALTER TABLE temp_tbrf ADD PRIMARY KEY(sy, pp_id, bool_out, run_id)''', time_msg='Add pk to temp_tbrf') aql.init_table('temp_map_run_id_ref', [ 'run_id', ('run_id_ref', 'SMALLINT', self.sc_out + '.def_loop(run_id)') ], ref_schema=self.sc_out, pk=['run_id']) exec_str = ''' /* CREATE MAP BETWEEN run_ids and reference run_ids */ INSERT INTO temp_map_run_id_ref (run_id, run_id_ref) WITH ref_st AS ( SELECT {sw_not_st}run_id AS run_id_rf FROM {sc_out}.def_loop WHERE swst_vl = '0.0%' ) SELECT dflp.run_id, ref_st.run_id_rf FROM {sc_out}.def_loop AS dflp NATURAL JOIN ref_st ORDER BY run_id; '''.format(**self.format_kw) aql.exec_sql(exec_str, time_msg='Create reference run_id map') aql.joinon(['run_id_ref'], ['run_id'], ['public', 'temp_analysis_time_series_subset'], ['public', 'temp_map_run_id_ref']) exec_str = ''' UPDATE temp_analysis_time_series_subset AS ts SET value_diff = ts.value - rf.value_ref -- ,value_ref = rf.value_ref FROM temp_tbrf AS rf WHERE rf.run_id = ts.run_id_ref AND rf.sy = ts.sy AND rf.pp_id = ts.pp_id AND rf.bool_out = ts.bool_out '''.format(**self.format_kw) # print(exec_str) aql.exec_sql(exec_str, time_msg='Add difference column to ts table') aql.init_table('temp_tbst', [ 'run_id', 'sy', ('pp_id_st', 'SMALLINT'), ('bool_out_st', 'BOOLEAN'), ('value_mask', 'DECIMAL') ], ref_schema=self.sc_out, pk=['run_id', 'pp_id_st', 'sy', 'bool_out_st']) exec_str = '''IF EXISTS /* GET CHARGING/DISCHARGING MASK */ INSERT INTO temp_tbst (run_id, sy, pp_id_st, bool_out_st, value_mask) SELECT run_id, sy, pp_id AS pp_id_st, bool_out AS bool_out_st, value AS value_mask FROM temp_analysis_time_series_subset WHERE pp_id IN {lst_pp_id_st} AND run_id IN {run_id_nonzero_st} ORDER BY sy, run_id, pp_id, bool_out; '''.format(**self.format_kw) print(exec_str) aql.exec_sql(exec_str) aql.exec_sql('''ALTER TABLE temp_analysis_time_series_subset ADD PRIMARY KEY(sy, ca_id, pp_id, bool_out, run_id)''', time_msg='Add pk to temp_analysis_time_series_subset') # expand temp_analysis_time_series_subset to (bool_out_st = True/False) # expand temp_tbst to pp_id, bool_out, best on creation/insertion aql.init_table( self.format_kw['out_tb'], [ 'pp_id', ('pp_id_st', 'SMALLINT'), 'bool_out', ('bool_out_st', 'BOOLEAN'), 'run_id', ('run_id_ref', 'SMALLINT'), ('swst_vl_ref', 'VARCHAR(5)'), 'value' ], schema=self.sc_out, ref_schema=self.sc_out, pk=['pp_id', 'pp_id_st', 'bool_out', 'bool_out_st', 'run_id']) exec_str = ''' INSERT INTO {sc_out}.{out_tb} (pp_id, pp_id_st, bool_out, bool_out_st, run_id, run_id_ref, value) WITH tb AS ( SELECT * FROM temp_analysis_time_series_subset AS tb CROSS JOIN (SELECT DISTINCT pp_id_st, bool_out_st FROM temp_tbst) AS exp_bool_out_st WHERE run_id IN {run_id_nonzero_st} ), st AS ( SELECT * FROM temp_tbst CROSS JOIN (SELECT DISTINCT tb.pp_id, bool_out FROM tb) AS exp_pp ) SELECT pp_id, pp_id_st, bool_out, bool_out_st, run_id, run_id_ref, SUM(value_diff) AS value FROM tb NATURAL LEFT JOIN st WHERE value_mask <> 0 GROUP BY pp_id, pp_id_st, bool_out, bool_out_st, run_id, run_id_ref; '''.format(**self.format_kw) print(exec_str) aql.exec_sql(exec_str) # # exec_str = ''' # UPDATE {sc_out}.analysis_agg_filtdiff AS tb # SET swst_vl_ref = dflp.swst_vl # FROM {sc_out}.def_loop AS dflp # WHERE dflp.run_id = tb.run_id_ref # '''.format(**format_kw) # print(exec_str) # aql.exec_sql(exec_str) # # # aql.exec_sql(''' # DROP TABLE IF EXISTS temp_analysis_time_series_subset CASCADE; # DROP TABLE IF EXISTS temp_tbrf CASCADE; # DROP TABLE IF EXISTS temp_tbst CASCADE; # DROP TABLE IF EXISTS temp_map_run_id_ref CASCADE; # ''') else: aql.init_table( self.format_kw['out_tb'], [ 'pp_id', ('pp_id_st', 'SMALLINT'), 'bool_out', ('bool_out_st', 'BOOLEAN'), 'run_id', ('run_id_ref', 'SMALLINT'), ('swst_vl_ref', 'VARCHAR(5)'), 'value' ], schema=self.sc_out, ref_schema=self.sc_out, pk=['pp_id', 'pp_id_st', 'bool_out', 'bool_out_st', 'run_id'], db=self.db) exec_str = ''' /* CREATE MAP BETWEEN run_ids and reference run_ids */ DROP VIEW IF EXISTS temp_map_run_id_ref CASCADE; CREATE VIEW temp_map_run_id_ref AS WITH ref_st AS ( SELECT {sw_not_st}run_id AS run_id_rf FROM {sc_out}.def_loop WHERE swst_vl = '0.00%' ) SELECT dflp.run_id, ref_st.run_id_rf FROM {sc_out}.def_loop AS dflp NATURAL JOIN ref_st ORDER BY run_id; /* FILTER TIME SERIES TABLE */ DROP TABLE IF EXISTS temp_analysis_time_series_subset CASCADE; SELECT sy, ca_id, nd_id, ts.pp_id, bool_out, value, ts.run_id, value_posneg, run_id_rf INTO temp_analysis_time_series_subset FROM {sc_out}.analysis_time_series_view_power AS ts LEFT JOIN {sc_out}.def_plant AS dfpp ON dfpp.pp_id = ts.pp_id LEFT JOIN {sc_out}.def_loop AS dflp ON dflp.run_id = ts.run_id LEFT JOIN {sc_out}.def_pp_type AS dfpt ON dfpt.pt_id = dfpp.pt_id LEFT JOIN temp_map_run_id_ref AS run_id_ref ON run_id_ref.run_id = ts.run_id WHERE dfpp.pp_id IN {list_slct_pp_id}; /* CREATE TABLE WITH NO-STORAGE REFERENCE VALUES */ DROP VIEW IF EXISTS temp_tbrf CASCADE; CREATE VIEW temp_tbrf AS SELECT sy, pp_id, bool_out, run_id, value AS value_ref FROM temp_analysis_time_series_subset WHERE run_id IN {run_id_zero_st}; /* GET CHARGING/DISCHARGING MASK */ DROP VIEW IF EXISTS temp_tbst CASCADE; CREATE VIEW temp_tbst AS SELECT run_id, sy, nd_id AS nd_id_st, pp_id AS pp_id_st, bool_out AS bool_out_st, value AS value_mask FROM temp_analysis_time_series_subset WHERE pp_id IN {lst_pp_id_st} AND run_id IN {run_id_nonzero_st}; DROP TABLE IF EXISTS temp_analysis_time_series_subset_ref CASCADE; SELECT ts.*, ts.value - rf.value_ref AS value_diff INTO temp_analysis_time_series_subset_ref FROM temp_analysis_time_series_subset AS ts LEFT JOIN temp_tbrf AS rf ON rf.run_id = ts.run_id_rf AND rf.sy = ts.sy AND rf.pp_id = ts.pp_id AND rf.bool_out = ts.bool_out; DROP VIEW IF EXISTS temp_analysis_time_series_subset_reffilt CASCADE; CREATE VIEW temp_analysis_time_series_subset_reffilt AS WITH tb AS ( SELECT * FROM temp_analysis_time_series_subset_ref AS tb FULL OUTER JOIN (SELECT DISTINCT pp_id_st, nd_id_st, bool_out_st FROM temp_tbst) AS exp_bool_out_st ON exp_bool_out_st.nd_id_st = tb.nd_id WHERE run_id IN {run_id_nonzero_st} ), st AS ( SELECT * FROM temp_tbst CROSS JOIN (SELECT DISTINCT tb.pp_id, bool_out FROM tb) AS exp_pp ), mt_map AS ( SELECT sy, mt_id FROM {sc_out}.tm_soy ) SELECT pp_id, pp_id_st, bool_out, bool_out_st, run_id, mt_id, run_id_rf, -- SELECT pp_id, pp_id_st, bool_out, bool_out_st, run_id, run_id_rf, SUM(value_diff) AS value FROM tb NATURAL LEFT JOIN st LEFT JOIN mt_map ON mt_map.sy = tb.sy WHERE value_mask <> 0 GROUP BY pp_id, pp_id_st, bool_out, bool_out_st, run_id, mt_id, run_id_rf; -- run_id, run_id_rf; DROP TABLE IF EXISTS {sc_out}.{out_tb} CASCADE; SELECT * INTO {sc_out}.{out_tb} FROM temp_analysis_time_series_subset_reffilt '''.format(**self.format_kw) aql.exec_sql(exec_str, db=self.db) aql.exec_sql(''' DROP VIEW IF EXISTS temp_map_run_id_ref CASCADE; DROP TABLE IF EXISTS temp_analysis_time_series_subset CASCADE; DROP VIEW IF EXISTS temp_tbrf CASCADE; DROP VIEW IF EXISTS temp_tbst CASCADE; DROP TABLE IF EXISTS temp_analysis_time_series_subset_ref CASCADE; ''', db=self.db) aql.exec_sql(''' /* ADD PP_TYPE COLUMN FOR STORAGE FILTER PLANTS */ ALTER TABLE {sc_out}.{out_tb} DROP COLUMN IF EXISTS pt_st, DROP COLUMN IF EXISTS pp_st, ADD COLUMN pp_st VARCHAR(15), ADD COLUMN pt_st VARCHAR(15); UPDATE {sc_out}.{out_tb} AS tb SET pt_st = map_pt.pt, pp_st = map_pt.pp FROM ( SELECT pp_id AS pp_id, pt, pp FROM {sc_out}.def_plant AS dfpp LEFT JOIN {sc_out}.def_pp_type AS dfpt ON dfpp.pt_id = dfpt.pt_id ) AS map_pt WHERE map_pt.pp_id = tb.pp_id_st; '''.format(**self.format_kw), db=self.db) # add loop indices aql.joinon(self.db, self.sw_columns, ['run_id'], [self.sc_out, self.format_kw['out_tb']], [self.sc_out, 'def_loop']) # add pp indices aql.joinon(self.db, ['pt_id', 'fl_id', 'nd_id'], ['pp_id'], [self.sc_out, self.format_kw['out_tb']], [self.sc_out, 'def_plant']) # add pt indices aql.joinon(self.db, ['pt'], ['pt_id'], [self.sc_out, self.format_kw['out_tb']], [self.sc_out, 'def_pp_type']) # add fl indices aql.joinon(self.db, ['fl'], ['fl_id'], [self.sc_out, self.format_kw['out_tb']], [self.sc_out, 'def_fuel']) # add nd indices aql.joinon(self.db, ['nd'], ['nd_id'], [self.sc_out, self.format_kw['out_tb']], [self.sc_out, 'def_node'])
def wrapper(self, *args, **kwargs): f(self, *args, **kwargs) aql.joinon(self.db, ['fl'], ['fl_id'], [self.sc_out, func_name], [self.sc_out, 'def_fuel'])
def wrapper(self, *args, **kwargs): f(self, *args, **kwargs) aql.joinon(self.db, ['pt'], ['pt_id'], [self.sc_out, func_name], [self.sc_out, 'def_pp_type'])
def wrapper(self, *args, **kwargs): f(self, *args, **kwargs) aql.joinon(self.db, ['pt_id', 'fl_id', 'nd_id', 'pp'], ['pp_id'], [self.sc_out, func_name], [self.sc_out, 'def_plant'])