def generate_analysis_time_series_diff(self):
exec_str = ('''
DROP TABLE IF EXISTS analysis_time_series_diff CASCADE;
WITH tb_1 AS (
SELECT * FROM analysis_time_series
WHERE run_id = {run_id_1}
), tb_2 AS (
SELECT
bool_out, ca_id, pp_id, sy,
value_posneg AS value_other
FROM analysis_time_series
WHERE run_id = {run_id_2}
)
SELECT tb_1.*,
tb_1.value_posneg - tb_2.value_other AS value_diff
INTO analysis_time_series_diff{sff}
FROM tb_1
LEFT JOIN tb_2
ON tb_1.bool_out = tb_2.bool_out
AND tb_1.ca_id = tb_2.ca_id
AND tb_1.pp_id = tb_2.pp_id
AND tb_1.sy = tb_2.sy;
''').format(run_id_1=self.run_id[0],
run_id_2=self.run_id[1],
sff=self._suffix)
aql.exec_sql(exec_str, db=self.db)
return exec_str
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 generate_analysis_time_series_chp_comparison(self):
''' Filters production data by CHP plants and appends exogenous CHP
profile for comparison '''
exec_str = ('''
/* Extension to analysis_time_series */
DROP TABLE IF EXISTS analysis_time_series_chp_comparison
CASCADE;
WITH output AS (
SELECT sy, wk_id, how, mt_id, doy, hom,
weight, nd, run_id, pt,
SUM(value) AS value
FROM analysis_time_series
WHERE pp_broad_cat = 'CHP'
GROUP BY pt, sy, wk_id, how, mt_id, doy,
hom, weight, nd, run_id
), profile AS (
SELECT prf.sy, wk_id, how, mt_id, doy, hom,
weight, nd, prf.run_id,
CAST('profile' AS VARCHAR) AS pt,
cap_pwr_tot_chp * value AS value
FROM {sc_out}.par_chpprof AS prf
LEFT JOIN (SELECT sy, wk_id, how, mt_id,
doy, hom, weight
FROM timemap) AS tm
ON prf.sy = tm.sy
LEFT JOIN {sc_out}.def_node AS dfnd
ON prf.nd_id = dfnd.nd_id
LEFT JOIN (
SELECT
run_id, nd_id,
SUM(cap.value) AS cap_pwr_tot_chp
FROM {sc_out}.var_yr_cap_pwr_tot AS cap
LEFT JOIN {sc_out}.def_plant AS dfpp
ON cap.pp_id = dfpp.pp_id
WHERE pp LIKE '%CHP%'
GROUP BY run_id, nd_id
ORDER BY nd_id, run_id
) AS cap
ON prf.nd_id = cap.nd_id AND prf.run_id = cap.run_id
ORDER BY run_id, nd, sy
)
SELECT * INTO analysis_time_series_chp_comparison{sff}
FROM output
UNION ALL
SELECT * FROM profile
''').format(sc_out=self.sc_out, sff=self._suffix)
aql.exec_sql(exec_str, db=self.db)
return exec_str
def merge_location_profiles_sql(self):
exec_str = '''
INSERT INTO {sc}.{tb}("DateTime", pt_id, value,
proftype, hy, year, nd_id, location)
SELECT "DateTime", pt_id, AVG(value) AS value, proftype,
hy, year, nd_id, 'national'::VARCHAR AS location
FROM profiles_raw.ninja
WHERE proftype = 'location' AND NOT location = 'national'
GROUP BY "DateTime", pt_id, proftype, hy, year, nd_id;
DELETE FROM {sc}.{tb}
WHERE proftype = 'location' AND NOT location = 'national';
'''.format(**self.dict_sql)
aql.exec_sql(exec_str, db=self.dict_sql['db'])
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 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_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
exec_str = '''
ALTER TABLE profiles_raw.ninja
ADD COLUMN IF NOT EXISTS scale DOUBLE PRECISION,
ADD COLUMN IF NOT EXISTS value_sc DOUBLE PRECISION;
UPDATE profiles_raw.ninja AS tbnn
SET scale = COALESCE((tbsc.scale_end - tbsc.scale_beg) / (8760 - 1) * hy + tbsc.scale_beg, 1)
FROM temp_add_column AS tbsc
WHERE tbnn.year = tbsc.year
AND tbnn.pt_id = tbsc.pt_id
AND tbnn.nd_id = tbsc.nd_id;
UPDATE profiles_raw.ninja
SET value_sc = scale * value;
'''
aql.exec_sql(exec_str, db=dict_sql['db'])
aql.read_sql(dict_sql['db'], 'profiles_raw', 'ninja', filt=[('year', [2016])])
filt=[('pt_id', ['WIN_OFF']), ('nd_id', ['DE0']), ('year', [2015])]
df = aql.read_sql('storage2', 'profiles_raw', 'ninja', filt=filt)
dfcap = aql.read_sql('storage2', 'public', 'temp_add_column', filt=filt)
exec_strg = '''
SELECT pt_id, proftype, year, nd_id, location, AVG(value_sc), AVG(value)
FROM profiles_raw.ninja
GROUP BY pt_id, proftype, year, nd_id, location
'''
cfnn = pd.DataFrame(aql.exec_sql(exec_strg, db='storage2'),
columns=['pt_id', 'proftype', 'year', 'nd_id', 'location', 'cfsc', 'cf'])
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'])
op = CREMProfileReader(kw_dict)
self = op
fn = self.fn_list[0]
sys.exit()
op.read_all()
exec_strg = '''
ALTER TABLE {sc}.{tb}
ADD PRIMARY KEY (nd_id, hy);
'''.format(**self.dict_sql)
aql.exec_sql(exec_strg, db=self.dict_sql['db'])
# self.append_to_sql(self.df_tot)
# %%
class SwissLocationSolarReader(ProfileReader):
''' '''
dict_sql_default = dict(sc='profiles_raw', tb='swiss_location_solar')
data_dir = os.path.normpath('PV_CANTONS_SYNTHETIC')
tb_cols = [
('"DateTime"', 'TIMESTAMP'),
('value', 'DOUBLE PRECISION'),
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_monthly_comparison(self):
tb_name = 'analysis_monthly_comparison'
cols = [
('fl', 'VARCHAR'),
('mt_id', 'SMALLINT'),
('nd', 'VARCHAR'),
('run_id', 'SMALLINT'),
('erg', 'DOUBLE PRECISION'),
('input', 'VARCHAR'),
('input_simple', 'VARCHAR'),
]
pk = ['fl', 'mt_id', 'nd', 'run_id', 'input']
aql.init_table(tb_name=tb_name,
cols=cols,
schema=self.sc_out,
pk=pk,
db=self.db)
exec_strg = '''
WITH map_input AS (
SELECT input::VARCHAR, input_simple::VARCHAR
FROM (VALUES ('agora_month_sum', 'stats'), ('rte_month_sum', 'stats'),
('econtrol_betriebsstatistik', 'stats'),
('ch_elektrizitaetsstatistik', 'stats'),
('model_erg_max_from_cf', 'model_max'),
('model_var_sy_pwr', 'model'), ('model_tr', 'model'),
('entsoe_cross_border', 'stats'),
('entsoe_commercial_exchange', 'stats'),
('terna', 'stats'))
AS temp (input, input_simple)
/* ), tb_model_erg_max_from_cf AS (
SELECT fl, tbcf.mt_id, nd, tbcf.run_id,
SUM(cap.value * tbcf.value * month_weight) AS erg,
'model_erg_max_from_cf'::VARCHAR AS input
FROM {sc_out}.par_cf_max AS tbcf
NATURAL LEFT JOIN (SELECT pp_id, pp, fl_id, nd_id FROM {sc_out}.def_plant) AS dfpp
NATURAL LEFT JOIN (SELECT fl_id, fl FROM {sc_out}.def_fuel) AS dffl
NATURAL LEFT JOIN (SELECT nd_id, nd FROM {sc_out}.def_node) AS dfnd
LEFT JOIN (SELECT mt_id, month_weight FROM {sc_out}.def_month) AS dfmt ON dfmt.mt_id = tbcf.mt_id
LEFT JOIN (SELECT pp_id, value, run_id FROM {sc_out}.par_cap_pwr_leg) AS cap ON tbcf.run_id = cap.run_id AND tbcf.pp_id = cap.pp_id
GROUP BY fl, tbcf.mt_id, nd, tbcf.run_id
*/ ), tb_model_var_sy_pwr AS (
SELECT fl, mt_id, nd, run_id,
SUM(value * weight) AS erg,
'model_var_sy_pwr'::VARCHAR AS input
FROM {sc_out}.var_sy_pwr AS ergsy
NATURAL LEFT JOIN (SELECT pp_id, pp, fl_id, nd_id FROM {sc_out}.def_plant) AS dfpp
NATURAL LEFT JOIN (SELECT fl_id, fl FROM {sc_out}.def_fuel) AS dffl
NATURAL LEFT JOIN (SELECT nd_id, nd FROM {sc_out}.def_node) AS dfnd
LEFT JOIN (SELECT mt_id, weight, sy FROM {sc_out}.tm_soy) AS tm ON tm.sy = ergsy.sy
GROUP BY fl, mt_id, nd, run_id
), tb_agora_month_sum AS (
SELECT fl_id AS fl, mt_id, nd_id AS nd, 0 AS run_id, SUM(value) * 1000 AS erg, 'agora_month_sum'::VARCHAR AS input
FROM profiles_raw.agora_profiles AS ag
LEFT JOIN (SELECT mt_id, datetime FROM profiles_raw.timestamp_template) AS ts ON ts.datetime = ag."DateTime"
WHERE year = 2015
GROUP BY fl_id, mt_id, nd_id, run_id
), tb_rte_month_sum AS (
SELECT fl_id AS fl, mt_id, nd_id AS nd, 0 AS run_id, SUM(value) AS erg, 'rte_month_sum'::VARCHAR AS input
FROM profiles_raw.rte_production_eco2mix AS rte
WHERE year = 2015
GROUP BY fl_id, mt_id, nd_id, run_id
), tb_entsoe_xborder AS (
SELECT fl, mt_id, nd, 0::SMALLINT AS run_id, SUM(value) AS erg,
'entsoe_cross_border'::VARCHAR AS input
FROM {sc_out}.analysis_time_series
WHERE sta_mod = 'stats_imex_entsoe'
GROUP BY fl, mt_id, nd, run_id
), tb_rte_eco2mix AS (
SELECT fl, mt_id, nd, 0::SMALLINT AS run_id, SUM(value) AS erg,
'rte_eco2mix'::VARCHAR AS input
FROM {sc_out}.analysis_time_series
WHERE sta_mod = 'stats_rte_eco2mix'
GROUP BY fl, mt_id, nd, run_id
), tb_model_tr AS (
SELECT
CASE WHEN bool_out = True THEN 'export_' || nd2 ELSE 'import_' || nd2 END AS fl,
mt_id, nd, run_id,
SUM((CASE WHEN bool_out = True THEN -1 ELSE +1 END) * value * weight) AS erg,
'model_tr'::VARCHAR AS input
FROM {sc_out}.var_tr_trm AS tbtr
LEFT JOIN (SELECT nd_id, nd FROM {sc_out}.def_node) AS dfnd ON dfnd.nd_id = tbtr.nd_id
LEFT JOIN (SELECT nd_id, nd AS nd2 FROM {sc_out}.def_node) AS dfnd2 ON dfnd2.nd_id = tbtr.nd_2_id
LEFT JOIN (SELECT sy, mt_id, weight FROM {sc_out}.tm_soy) AS dftm ON dftm.sy = tbtr.sy
GROUP BY fl, mt_id, nd, run_id
), tb_model_tr_cap_imp AS (
SELECT 'import_' || nd2 AS fl, mt_id, nd, run_id, value AS erg, 'model_cap_tr'
FROM {sc_out}.par_cap_trmi_leg AS tbrv
LEFT JOIN (SELECT nd_id, nd AS nd2 FROM {sc_out}.def_node) AS dfnd2 ON dfnd2.nd_id = tbrv.nd_id
LEFT JOIN (SELECT nd_id, nd AS nd FROM {sc_out}.def_node) AS dfnd ON dfnd.nd_id = tbrv.nd_2_id
ORDER BY nd, nd2
), tb_model_tr_cap_exp AS (
SELECT 'export_' || nd2 AS fl, mt_id, nd, run_id, value AS erg, 'model_cap_tr'
FROM {sc_out}.par_cap_trme_leg AS tbrv
LEFT JOIN (SELECT nd_id, nd AS nd FROM {sc_out}.def_node) AS dfnd2 ON dfnd2.nd_id = tbrv.nd_id
LEFT JOIN (SELECT nd_id, nd AS nd2 FROM {sc_out}.def_node) AS dfnd ON dfnd.nd_id = tbrv.nd_2_id
ORDER BY nd, nd2
), tb_entsoe_comm AS (
WITH tb_raw AS (
SELECT nd_to, nd_from, mt_id, tb.year, SUM(value) AS erg
FROM profiles_raw.entsoe_commercial_exchange AS tb
LEFT JOIN (SELECT mt_id, year, datetime
FROM profiles_raw.timestamp_template) AS ts
ON ts.datetime = tb."DateTime"
GROUP BY nd_to, nd_from, mt_id, tb.year
), tb AS (
SELECT 'export_comm_'::VARCHAR || LOWER(nd_to) AS fl, nd_from AS nd, *
FROM tb_raw WHERE nd_from IN (SELECT nd FROM {sc_out}.def_node)
UNION ALL
SELECT 'import_comm_'::VARCHAR || LOWER(nd_from) AS fl, nd_to AS nd, *
FROM tb_raw WHERE nd_to IN (SELECT nd FROM {sc_out}.def_node)
)
SELECT fl, mt_id, nd, 0::SMALLINT AS run_id,
erg, 'entsoe_commercial_exchange'::VARCHAR AS input
FROM tb
), tb_monthly AS (
SELECT fl, mt_id, nd, 0::SMALLINT AS run_id, erg, input
FROM profiles_raw.monthly_production
WHERE year = 2015
), tb_all AS (
SELECT * FROM tb_agora_month_sum
UNION ALL
SELECT * FROM tb_rte_month_sum
UNION ALL
SELECT * FROM tb_model_var_sy_pwr
UNION ALL
SELECT * FROM tb_entsoe_xborder
UNION ALL
SELECT * FROM tb_model_tr
UNION ALL
SELECT * FROM tb_model_tr_cap_imp
UNION ALL
SELECT * FROM tb_model_tr_cap_exp
UNION ALL
SELECT * FROM tb_monthly
UNION ALL
SELECT * FROM tb_entsoe_comm
UNION ALL
SELECT * FROM tb_rte_eco2mix
)
INSERT INTO {sc_out}.analysis_monthly_comparison (fl, mt_id, nd, run_id,
erg, input, input_simple)
SELECT fl, mt_id, nd, run_id, erg, input, input_simple
FROM tb_all
NATURAL LEFT JOIN map_input;
/*
/* ADD CAPACITY FACTORS CROSS-BORDER TRANSMISSION */
INSERT INTO {sc_out}.analysis_monthly_comparison (
fl, mt_id, nd, run_id, erg, input, input_simple)
SELECT
dfnd2.fl, tbrv.mt_id, dfnd.nd, tbrv.run_id,
erg / (value * month_weight) AS erg,
'model_tr_cf'::VARCHAR AS input, 'model'::VARCHAR AS input_simple
FROM {sc_out}.par_cap_trm_leg AS tbrv
LEFT JOIN (SELECT nd_id, nd AS nd FROM {sc_out}.def_node) AS dfnd
ON dfnd.nd_id = tbrv.nd_2_id
LEFT JOIN (SELECT nd_id, 'import_' || nd AS fl
FROM {sc_out}.def_node) AS dfnd2
ON dfnd2.nd_id = tbrv.nd_id
LEFT JOIN (SELECT mt_id, month_weight FROM {sc_out}.def_month) AS dfmt
ON dfmt.mt_id = tbrv.mt_id
LEFT JOIN (SELECT fl, mt_id, nd, run_id, erg
FROM {sc_out}.analysis_monthly_comparison
WHERE input = 'model_tr' AND fl LIKE 'import_%') AS tban
ON tban.mt_id = tbrv.mt_id AND tban.run_id = tbrv.run_id
AND tban.fl = dfnd2.fl AND tban.nd = dfnd.nd;
INSERT INTO {sc_out}.analysis_monthly_comparison (
fl, mt_id, nd, run_id, erg, input, input_simple)
SELECT
dfnd2.fl, tbrv.mt_id, dfnd.nd, tbrv.run_id,
erg / (value * month_weight) AS erg,
'model_tr_cf'::VARCHAR AS input, 'model'::VARCHAR AS input_simple
FROM {sc_out}.par_cap_trm_leg AS tbrv
LEFT JOIN (SELECT nd_id, nd AS nd FROM {sc_out}.def_node) AS dfnd
ON dfnd.nd_id = tbrv.nd_id
LEFT JOIN (SELECT nd_id, 'export_' || nd AS fl
FROM {sc_out}.def_node) AS dfnd2
ON dfnd2.nd_id = tbrv.nd_2_id
LEFT JOIN (SELECT mt_id, month_weight FROM {sc_out}.def_month) AS dfmt
ON dfmt.mt_id = tbrv.mt_id
LEFT JOIN (SELECT fl, mt_id, nd, run_id, erg
FROM {sc_out}.analysis_monthly_comparison
WHERE input = 'model_tr' AND fl LIKE 'export_%') AS tban
ON tban.mt_id = tbrv.mt_id AND tban.run_id = tbrv.run_id
AND tban.fl = dfnd2.fl AND tban.nd = dfnd.nd;
*/
ALTER TABLE {sc_out}.analysis_monthly_comparison
ADD COLUMN fl2 VARCHAR;
UPDATE {sc_out}.analysis_monthly_comparison
SET fl2 = fl;
'''.format(**self.format_kw)
aql.exec_sql(exec_strg, db=self.db)
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)
# ), tb_price AS (
# SELECT sy, nd_id, value AS price, swyr_vl AS year FROM out_marg_store.par_priceprof
# NATURAL LEFT JOIN (SELECT swyr_vl, run_id FROM out_marg_store.def_loop) AS dflp
# NATURAL LEFT JOIN (SELECT nd_id, nd FROM out_marg_store.def_node) AS dfnd
# WHERE nd = 'DE0' AND swyr_vl = 'yr2016'
# )
# SELECT sy, year, dmnd, price, nd_id FROM tb_dmnd
# NATURAL LEFT JOIN tb_price
# ''', db='storage2'), columns=['hy', 'year', 'value_0', 'value_1', 'nd_id'])
dfcomp = pd.DataFrame(aql.exec_sql('''
WITH tb1 AS (
SELECT hy, year, value AS val1 FROM profiles_raw.rte_production_eco2mix
WHERE year = 2015 AND fl_id = 'photovoltaics'
), tb0 AS (
SELECT hy, year, value AS val0 FROM profiles_raw.entsoe_generation
WHERE fl_id = 'photovoltaics' AND year = 2015 AND nd_id = 'FR0'
)
SELECT * FROM tb1
NATURAL LEFT JOIN tb0
''', db='storage2'), columns=['hy', 'year', 'value_0', 'value_1'])
#
#dfcomp = df_full_all[['slot', 'year', 'dmnd', 'DE_DMND_ag']].rename(columns={'slot': 'hy', 'dmnd': 'value_0', 'DE_DMND_ag': 'value_1'})
#dfcomp = dfcomp.loc[dfcomp.year.isin([2015])]
dfcomp = dfcomp.dropna(axis=0)
val_0 = dfcomp.value_0.values
val_1 = dfcomp.value_1.values
def make_pp_id_col(self):
aql.exec_sql('''
UPDATE {sc}.{tb}
SET pp_id = SUBSTRING(nd_id FROM 1 FOR 2) || '_' || pt_id;
'''.format(**self.dict_sql), db=self.dict_sql['db'])
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 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 init_schema(self):
exec_str = '''
CREATE SCHEMA IF NOT EXISTS {sc};
'''.format(**self.dict_sql)
aql.exec_sql(exec_str, db=self.dict_sql['db'])
sqlc = aql.sql_connector(db)
reload(aql)
def init_table(*args, **kwargs):
return aql.init_table(*args, **kwargs, con_cur=sqlc.get_pg_con_cur())
sys.exit()
# %%
exec_str = ('''
DROP SCHEMA IF EXISTS {sc} CASCADE;
CREATE SCHEMA IF NOT EXISTS {sc};
''').format(sc=sc, )
aql.exec_sql(exec_str, db=db)
def yr_getter(par, data_type=False, rnge=range(2015, 2050 + 1, 5)):
return [par + i if not data_type else (par + i, data_type)
for i in [''] + ['_yr' + str(ii) for ii
in rnge if not ii == 2015]]
# <codecell>
tb_name = 'def_pp_type'
cols = [('pt_id',' SMALLINT'),
('pt',' varchar(20)'),
('pp_broad_cat', 'varchar(100)'),
('color', 'VARCHAR(7)')]
pk = ['pt_id']
unique = ['pt']
init_table(tb_name=tb_name, cols=cols, schema=sc, ref_schema=sc,