diff options
| -rw-r--r-- | sql/dawn.sql | 34 |
1 files changed, 24 insertions, 10 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c80f22d1..1fa580a1 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -136,22 +136,36 @@ END; $$ LANGUAGE plpgsql; CREATE MATERIALIZED VIEW factors_history AS -WITH temp AS -(SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, lead(c.coupon) OVER w AS coupon, - -c.principal-c.principal_bal+lag(c.principal_bal) OVER w AS losses - FROM cashflow_history c - WINDOW w AS (PARTITION BY c.identifier order by c.date asc)) -SELECT date, identifier, principal_bal/face_amount AS factor, principal/face_amount*100 AS principal, -interest/face_amount*100 AS interest, losses/face_amount*100 AS losses, coalesce(securities.coupon, temp.coupon) -FROM temp JOIN securities USING (identifier); -CREATE UNIQUE INDEX factors_history_pkey ON factors_history(date, identifier); + WITH temp AS ( + SELECT c.date, + c.identifier, + c.principal, + c.principal_bal, + c.interest, + lead(c.coupon) OVER w AS coupon, + (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses + FROM cashflow_history c + WINDOW w AS (PARTITION BY c.identifier ORDER BY c.date) + ) + SELECT temp.date AS last_pay_date, + temp.date-securities.pay_delay AS prev_cpn_date, + temp.identifier, + temp.principal_bal / securities.face_amount AS factor, + temp.principal / securities.face_amount * 100::double precision AS principal, + temp.interest / securities.face_amount * 100::double precision AS interest, + temp.losses / securities.face_amount * 100::double precision AS losses, + COALESCE(temp.coupon, securities.coupon) + FROM temp + JOIN securities USING (identifier); + +CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); CREATE OR REPLACE function factors_history(p_date date) RETURNS SETOF factors_history AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (identifier) * FROM factors_history - WHERE date<=p_date ORDER BY identifier, date desc; + WHERE prev_cpn_date<=p_date ORDER BY identifier, prev_cpn_date desc; END; $$ LANGUAGE plpgsql; |