diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 83 |
1 files changed, 65 insertions, 18 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 16f6b489..6461d015 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -182,6 +182,16 @@ WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_marks_var(p_date date, VARIADIC p_identifier varchar(12)[]) +RETURNS TABLE(identifier varchar(12), price float) AS $$ +BEGIN + RETURN QUERY SELECT a.identifier, b.price FROM (SELECT unnest(p_identifier) AS identifier) a + LEFT JOIN +(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks +WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float) AS $$ @@ -499,10 +509,10 @@ CREATE FOREIGN TABLE priced( model_version smallint, normalization text, timestamp timestamp, - pv numeric(23,6), - pv_RnW numeric(23,6), - pv_io numeric(23,6), - pv_po numeric(23,6), + pv float, + pv_RnW float, + pv_io float, + pv_po float, modDur float, modDur_io float, modDur_po float, @@ -510,20 +520,21 @@ CREATE FOREIGN TABLE priced( wal_width float, wal_io float, wal_po float, - delta_hpi numeric(23,6), - delta_ir numeric(23,6), - delta_ir_io numeric(23,6), - delta_ir_po numeric(23,6), - delta_mult numeric(23,6), - delta_yield numeric(23,6), - delta_quantile numeric(23,6), - delta_RnW numeric(23,6), - tot_gamma_hpi numeric(23,6), - tot_gamma_ir numeric(23,6), - tot_gamma numeric(23,6)) + 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( cusip varchar(9), @@ -561,9 +572,9 @@ CREATE FOREIGN TABLE priced_percentiles( percentile float, normalization text, timestamp timestamp, - pv numeric(23,6), - pv_io numeric(23,6), - pv_po numeric(23,6), + pv float, + pv_io float, + pv_po float, modDur float, modDur_io float, modDur_po float, @@ -660,3 +671,39 @@ SERVER postgresql_server; -- update securities set identifier='073879R75_A' where identifier='073879R75'; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; -- refresh materialized view factors_history; + +CREATE OR REPLACE function list_subprime_data(p_date date, 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) AS $$ +BEGIN +RETURN QUERY +WITH left_table AS ( + WITH temp AS (SELECT * from priced where date(timestamp)=p_date) + 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 + 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 + FROM priced_percentiles + WHERE timestamp BETWEEN p_date AND p_date + INTERVAL '1 day' + AND model_version=3 + AND percentile in (5, 25, 50, 75, 95) + AND normalization ='current_notional') + 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, + (SELECT cusip, PV FROM temp WHERE percentile=75) d, + (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 +FROM left_table, right_table, (SELECT unnest(p_cusip) AS cusip) l +WHERE left_table.cusip=right_table.cusip AND left_table.cusip=l.cusip; |