diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 148 |
1 files changed, 23 insertions, 125 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 08150f01..3563b344 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2163,112 +2163,10 @@ BEGIN 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, - timestamp timestamp, - pv float, - pv_RnW float, - pv_FB float, - pv_io float, - pv_po float, - modDur float, - modDur_io float, - modDur_po float, - wal float, - wal_width float, - wal_io float, - wal_po float, - delta_hpi float, - delta_ir float, - delta_ir_io float, - delta_ir_po float, - delta_mult float, - delta_yield float, - delta_quantile float, - delta_RnW float, - tot_gamma_hpi float, - tot_gamma_ir float, - tot_gamma float) - 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, - timestamp timestamp, - pv float, - pv_RnW float, - pv_FB float, - pv_io float, - pv_po float, - modDur float, - modDur_io float, - modDur_po float, - wal float, - wal_width float, - wal_io float, - wal_po float, - delta_hpi float, - delta_ir float, - delta_ir_io float, - delta_ir_po float, - delta_mult float, - delta_yield float, - delta_quantile float, - delta_RnW float, - tot_gamma_hpi float, - tot_gamma_ir float, - tot_gamma float) - SERVER mysql_server - OPTIONS (dbname 'rmbs_model'); - -CREATE FOREIGN TABLE priced_percentiles( - model_id_sub smallint, - cusip varchar(9), - model_version smallint, - percentile float, - normalization text, - timestamp timestamp, - pv float, - pv_io float, - pv_po float, - modDur float, - modDur_io float, - modDur_po float, - wal float, - wal_width float, - wal_io float, - wal_po float) - SERVER mysql_server - OPTIONS (dbname 'rmbs_model'); - -CREATE FOREIGN TABLE priced_percentiles_orig_ntl( - model_id_sub smallint, - cusip varchar(9), - model_version smallint, - percentile float, - normalization text, - timestamp timestamp, - pv float, - pv_io float, - pv_po float, - modDur float, - modDur_io float, - modDur_po float, - wal float, - wal_width float, - wal_io float, - wal_po float) - SERVER mysql_server - OPTIONS (dbname 'rmbs_model'); - +IMPORT FOREIGN SCHEMA rmbs_model +LIMIT TO (priced, priced_percentiles, priced_orig_ntl, priced_percentiles_orig_ntl) +FROM SERVER mysql_server INTO public +OPTIONS (import_enum_as_text 'true'); CREATE TYPE INDEXFAMILY AS ENUM('ITRAXX-Asian', 'LCDXNA', 'MCDXNA', 'ITRAXX-SOVX', 'ITRAXX-SDI', 'ITRAXX-L', 'CDX', 'ITRAXX-European'); CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD'); @@ -2331,10 +2229,10 @@ CREATE FOREIGN TABLE bond_types( -- refresh materialized view factors_history; CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIADIC p_cusip varchar(9)[]) -RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, - percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, - io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float, delta_rnw float, delta_mult float, - v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float, pv_FB float) AS $$ +RETURNS TABLE(v1 float4, v2 float4, v3 float4, duration float4, percentile5 float4, percentile25 float4, + percentile50 float4, percentile75 float4, percentile95 float4, yield_delta float4, wal float4, + io_pv float4, po_pv float4, rnw float4, ir_io_delta float4, ir_po_delta float4, hpi_delta float4, delta_rnw float4, delta_mult float4, + v1pv_RnW float4, v1_lsdel float4, v1_hpidel float4, v1_irdel float4, pv_FB float4) AS $$ DECLARE query text; opt_constraint text; @@ -2346,30 +2244,30 @@ ELSE END IF; query:= 'WITH left_table AS ( - WITH temp AS (SELECT cusip, model_version, pv, modDur, delta_yield, - wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po, - delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB + WITH temp AS (SELECT cusip, model_version, pv, "modDur", delta_yield, + wal, pv_io, pv_po, "pv_RnW", delta_ir_io, delta_ir_po, + delta_hpi, "delta_RnW", delta_mult, delta_ir, "pv_FB" FROM %I WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_id_sub=$3 ' || opt_constraint || ') - 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, c.pv_FB + 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, c."pv_FB" 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 WHERE a.cusip = b.cusip AND a.cusip=c.cusip), right_table AS ( WITH temp AS( - SELECT cusip, PV, percentile + SELECT cusip, pv, percentile 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 + AND percentile in (5::float4, 25::float4, 50::float4, 75::float4, 95::float4)' || 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 + SELECT a.cusip, a.pv AS pv5, b.pv AS pv25, c.pv AS pv50, d.pv AS pv75, e.pv AS pv95 FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a, (SELECT cusip, PV FROM temp WHERE percentile=25) b, (SELECT cusip, PV FROM temp WHERE percentile=50) c, @@ -2377,10 +2275,10 @@ query:= (SELECT cusip, PV FROM temp WHERE percentile=95) e WHERE a.cusip=b.cusip AND b.cusip=c.cusip and c.cusip=d.cusip and d.cusip=e.cusip and e.cusip=a.cusip) SELECT left_table.v1, left_table.v2, left_table.v3, - left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, - left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, - left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table.delta_RnW, left_table.delta_mult, - left_table.v1pv_RnW, left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel, left_table.pv_FB + left_table."modDur", right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, + left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table."pv_RnW", + left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table."delta_RnW", left_table.delta_mult, + left_table."v1pv_RnW", left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel, left_table."pv_FB" FROM (SELECT unnest($2) AS cusip) l LEFT JOIN left_table ON left_table.cusip=l.cusip LEFT JOIN right_table ON left_table.cusip=right_table.cusip'; @@ -2390,7 +2288,7 @@ ELSE query := format(query, 'priced', 'priced_percentiles'); END IF; SET enable_nestloop = off; -RETURN QUERY EXECUTE query USING p_date, p_cusip, latest_sim(p_date); +RETURN QUERY EXECUTE query USING p_date::timestamp, p_cusip, latest_sim(p_date::timestamp); SET enable_nestloop = on; END $$ LANGUAGE plpgsql; |