diff options
| -rw-r--r-- | sql/dawn.sql | 56 |
1 files changed, 50 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 8e3020c1..597f37a7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -738,9 +738,21 @@ BEGIN END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function latest_sim(p_date date) RETURNS integer AS $$ +DECLARE + id integer; +BEGIN +SELECT MAX(model_id_sub) into id FROM model_versions + INNER JOIN model_versions_nonagency USING (model_id_sub) + INNER JOIN simulations_nonagency USING (simulation_id) + WHERE (start_time < p_date) AND (description='normal'); +RETURN id; +End; +$$ LANGUAGE plpgsql; -- Not sure how to map enums so use text for now CREATE FOREIGN TABLE priced( + model_id_sub smallint, cusip varchar(9), model_version smallint, normalization text, @@ -770,9 +782,9 @@ CREATE FOREIGN TABLE priced( SERVER mysql_server OPTIONS (dbname 'rmbs_model'); - -- We want to use INHERITS here, but will only be available on 9.5 CREATE FOREIGN TABLE priced_orig_ntl( + model_id_sub smallint, cusip varchar(9), model_version smallint, normalization text, @@ -803,6 +815,7 @@ CREATE FOREIGN TABLE priced_orig_ntl( OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles( + model_id_sub smallint, cusip varchar(9), model_version smallint, percentile float, @@ -814,7 +827,7 @@ CREATE FOREIGN TABLE priced_percentiles( modDur float, modDur_io float, modDur_po float, - wal float, + wal float,s wal_width float, wal_io float, wal_po float) @@ -822,6 +835,7 @@ CREATE FOREIGN TABLE priced_percentiles( OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles_orig_ntl( + model_id_sub smallint, cusip varchar(9), model_version smallint, percentile float, @@ -906,7 +920,36 @@ CREATE FOREIGN TABLE markit_tranche_quotes( index_price float) SERVER postgresql_server; +CREATE FOREIGN TABLE model_versions( + model_id smallint, + asset_class text, + start_time timestamp, + model_id_sub smallint) + SERVER mysql_server + OPTIONS (dbname 'rmbs_model'); + +CREATE FOREIGN TABLE model_versions_nonagency( + model_id_sub smallint, + granularity_agg text, + num_LS_mult smallint, + d_LS_mult smallint, + simulation_id smallint) + SERVER mysql_server + OPTIONS (dbname 'rmbs_model'); +CREATE FOREIGN TABLE simulations_nonagency( + simulation_id smallint, + granularity_simu text, + num_hpi_scens smallint, + num_ir_scens smallint, + macro_timestamp timestamp, + data_source text, + forecast_date_roll date, + forecast_date date, + hamp_stepup_credit float, + description text) + SERVER mysql_server + OPTIONS (dbname 'rmbs_model'); -- #bonds that get written down -- update securities set identifier='073879R75_A' where identifier='073879R75'; @@ -933,9 +976,9 @@ query:= SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, c.delta_hpi, c.delta_RnW, c.delta_mult, a.pv_RnW as v1pv_RnW, a.delta_mult as v1_lsdel, a.delta_hpi as v1_hpidel, a.delta_ir as v1_irdel - FROM (SELECT * FROM temp WHERE model_version=1) a, - (SELECT * FROM temp WHERE model_version=2) b, - (SELECT * FROM temp WHERE model_version=3) c + FROM (SELECT * FROM temp WHERE model_version=1 and model_id_sub=$3) a, + (SELECT * FROM temp WHERE model_version=2 and model_id_sub=$3) b, + (SELECT * FROM temp WHERE model_version=3 and model_id_sub=$3) c WHERE a.cusip = b.cusip AND a.cusip=c.cusip), right_table AS ( WITH temp AS( @@ -943,6 +986,7 @@ query:= FROM %I WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_version=3 + AND model_id_sub=$3 AND percentile in (5, 25, 50, 75, 95)' || opt_constraint ||') SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95 @@ -966,7 +1010,7 @@ ELSE query := format(query, 'priced', 'priced_percentiles'); END IF; -RETURN QUERY EXECUTE query USING p_date, p_cusip; +RETURN QUERY EXECUTE query USING p_date, p_cusip, latest_sim(p_date); END $$ LANGUAGE plpgsql; |