diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 9 |
1 files changed, 4 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index b459c82d..c5e12571 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -145,16 +145,15 @@ BEGIN END; $$ LANGUAGE plpgsql; - CREATE MATERIALIZED VIEW factors_history AS WITH temp AS -(SELECT c.date, c.identifier, first_value(c.principal_bal) OVER w AS orig_bal, - c.principal, c.principal_bal, c.interest, c.coupon, +(SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, c.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/orig_bal AS factor, principal/orig_bal AS principal, -interest/orig_bal AS interest, losses/orig_bal AS losses, coupon FROM temp; +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, temp.coupon +FROM temp JOIN securities USING (identifier); CREATE UNIQUE INDEX factors_history_pkey ON factors_history(date, identifier); CREATE OR REPLACE function factors_history(p_date date) |