diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 44 |
1 files changed, 31 insertions, 13 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 5ae5e0dd..7c626867 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -300,7 +300,6 @@ CREATE TYPE cds_issuers AS ( seniority sen,
short_code ShortCode,
cds_curve text[8],
- index_list integer[],
markit_ticker text,
markit_tier tier,
spread integer,
@@ -308,6 +307,21 @@ CREATE TYPE cds_issuers AS ( GRANT ALL ON cds_Issuers to serenitas_user;
+CREATE TYPE cds_issuers_weight AS (
+ Name text,
+ company_id integer,
+ ticker text,
+ currency curr,
+ seniority sen,
+ short_code ShortCode,
+ cds_curve text[8],
+ markit_ticker text,
+ markit_tier tier,
+ spread integer,
+ event_date date,
+ weight float
+);
+
CREATE TABLE bbg_issuers(
Name text,
company_id integer,
@@ -342,6 +356,9 @@ CREATE TABLE basket_constituents( FOREIGN KEY (company_id, seniority) REFERENCES bbg_iisuers,
PRIMARY KEY (company_id, seniority, basket_id));
+CREATE OR REPLACE VIEW basket_constituents_current AS
+SELECT company_id, seniority, basketid, weight/sum(weight) OVER (PARTITION BY basketid) AS curr_weight FROM basket_constituents;
+
CREATE TABLE IF NOT EXISTS cds_quotes(
Date Date,
curve_ticker text,
@@ -407,14 +424,15 @@ CREATE OR REPLACE FUNCTION index_redcode(p_index index_type, p_series smallint, $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
- RETURNS SETOF cds_issuers AS $$
- DECLARE
- basketid integer;
- BEGIN
- SELECT nameToBasketID(index_name, p_date) INTO basketid;
- RETURN QUERY SELECT * FROM historical_cds_issuers(p_date)
- WHERE index_list @> Array[basketid];
- END;
+RETURNS SETOF cds_issuers_weight AS $$
+ DECLARE
+ p_basketid integer;
+ BEGIN
+ SELECT nameToBasketID(index_name, p_date) INTO p_basketid;
+ RETURN QUERY SELECT a.*, b.curr_weight FROM historical_cds_issuers(p_date) a
+ LEFT JOIN basket_constituents_current b USING (company_id, seniority)
+ WHERE b.basketid=p_basketid;
+ END;
$$ LANGUAGE plpgsql;
@@ -444,7 +462,6 @@ RETURNS SETOF cds_issuers AS $$ bbg_issuers.seniority,
bbg_issuers.short_code,
bbg_issuers.cds_curve,
- bbg_issuers.index_list,
map.markit_ticker,
map.markit_tier,
map.spread,
@@ -483,21 +500,22 @@ CREATE OR REPLACE FUNCTION curve_quotes2( date,
float[] DEFAULT '{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[],
bbgsource DEFAULT 'MKIT'::bbgsource) RETURNS
- TABLE(cds_ticker text, date date, spread_curve float[],
+ TABLE(cds_ticker text, weight float, date date, spread_curve float[],
upfront_curve float[], recovery_curve float[], currency curr,
event_date date) AS $$
BEGIN
- RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
+ RETURN QUERY SELECT max(markit_ticker) AS t, max(b.weight), max(a.date),
array_agg((a.runningbid + a.runningask)/2 ORDER BY tenor),
array_agg((a.upfrontbid + a.upfrontask)/2 ORDER BY tenor),
array_agg(a.Recovery ORDER BY tenor),
max(b.currency), max(b.event_date) FROM historical_cds_quotes($2, $4) a
RIGHT JOIN
- (SELECT curve_ticker, markit_ticker, company_id, c.event_date, c.currency, tenor FROM
+ (SELECT curve_ticker, markit_ticker, company_id, c.weight, c.event_date, c.currency, tenor FROM
(SELECT unnest('{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[]) AS tenor,
unnest(cds_curve) AS curve_ticker,
markit_ticker,
company_id,
+ d.weight,
d.currency,
d.event_date
FROM index_members($1, $2) d) c
|