diff options
| -rw-r--r-- | R/serenitasdb.R | 53 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 34 |
2 files changed, 22 insertions, 65 deletions
diff --git a/R/serenitasdb.R b/R/serenitasdb.R index 16b3d764..807bb57f 100644 --- a/R/serenitasdb.R +++ b/R/serenitasdb.R @@ -79,53 +79,12 @@ indexsplit <- function(indexname){ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ ## first try the easy way: - sqlstr <- paste("select * from tranche_quotes", - "where index=$1 and series=$2 and quotedate::date=$3", - "and tenor = $4 order by attach asc") - temp <- indexsplit(indexname) - indextype <- temp$indextype - series <- temp$series - r <- dbGetQuery(serenitasdb, sqlstr, - params = list(indextype, series, date, tenor)) - ## check if set is unique and complete - ## hy9 and hy10 tranche is gone - if(tolower(indexname) %in% c("hy9", "hy10")){ - lower.attach <- 10 - }else{ - lower.attach <- 0 - } - if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(lower.attach, r$detach))){ - return(r) - } - ##else we work harder - ##we get the list of distinct quotes - sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes", - "where index=$1 and series=$2 and quotedate::date=$3", - "and tenor = $4 order by quotedate desc") - distinct.quotes <- dbGetQuery(serenitasdb, sqlstr, - params = list(indextype, series, date, tenor)) - flag <- FALSE - ##we loop through the disctinct quotes until we find a complete set - if(nrow(distinct.quotes)==0){ - return(NULL) - } - for(i in 1:nrow(distinct.quotes)){ - sqlstr <- str_c("select * from tranche_quotes where index=$1 ", - "and series=$2 and tenor = $3 and quotedate=$4 ", - if(indextype == 'HY' && series>=15) "and detach-attach!=5 " else NULL, - "and quotesource=$5 order by attach asc") - r <- dbGetQuery(serenitasdb, sqlstr, - params = list(indextype, series, tenor, - distinct.quotes$quotedate[i], - distinct.quotes$quotesource[i])) - if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete - flag <- TRUE - break - } - } - if(!flag){ - return(NULL) - } + sqlstr <- "SELECT * from get_tranche_quotes($1, $2, $3, $4)" + r <- with(indexsplit(indexname), + suppressWarnings( + dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor, date)) + ) + ) return( r ) } diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 42ff5338..2716cba6 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -401,17 +401,14 @@ CREATE TABLE bbg_index_quotes( PRIMARY KEY(date, ticker, version));
CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series integer, pg_tenor text,
- pg_date date)
- RETURNS SETOF tranche_quotes AS
- $$
+ pg_date date) RETURNS SETOF tranche_quotes AS
+$$
DECLARE r RECORD;
- DECLARE indexname text;
DECLARE lower_attach smallint;
DECLARE flag boolean;
BEGIN
- indexname := pg_index_type || pg_series;
- IF lower(indexname) ='hy9' or lower(indexname) ='hy10'
+ IF lower(pg_index_type) ='hy' AND pg_series in (9, 10)
THEN
lower_attach = 10::smallint;
ELSE
@@ -419,29 +416,30 @@ CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series inte END IF;
flag := FALSE;
FOR r in EXECUTE
- 'SELECT DISTINCT quotesource, quotedate FROM TRANCHE_QUOTES
- WHERE index=$1::index_type AND series=$2 AND quotedate::date=$3
+ 'SELECT DISTINCT quotesource, quotedate FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND date(timezone(''localtime'', quotedate))=$3
AND tenor =$4::tenor ORDER BY quotedate desc'
USING pg_index_type, pg_series, pg_date, pg_tenor LOOP
IF pg_index_type = 'HY' AND pg_series >=15 THEN
EXECUTE
- 'SELECT array_agg(attach ORDER by attach asc)||100::smallint=$6||array_agg(detach) FROM
- tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
- and quotedate=$4 and detach-attach!=5::smallint and quotesource=$5'
+ 'SELECT array_agg(attach ORDER BY attach ASC)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
+ FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND tenor=$3::tenor
+ AND quotedate=$4 AND detach-attach!=5::smallint AND quotesource=$5'
INTO flag
USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
IF flag THEN
RETURN QUERY
SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series= pg_series
AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND detach-attach!=5::smallint AND
- quotesource=r.quotesource ORDER BY attach asc;
- RETURN;
+ quotesource=r.quotesource ORDER BY attach ASC;
+ RETURN;
END IF;
ELSE
EXECUTE
- 'SELECT array_agg(attach ORDER BY attach asc)||100::smallint=$6||array_agg(detach) FROM
- tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
- and quotedate=$4 and quotesource=$5'
+ 'SELECT array_agg(attach ORDER BY attach asc)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
+ FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND tenor=$3::tenor AND quotedate=$4 AND quotesource=$5'
INTO flag
USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
IF flag THEN
@@ -449,12 +447,12 @@ CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series inte SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series=pg_series
AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND quotesource=r.quotesource
ORDER BY attach asc;
- RETURN;
+ RETURN;
END IF;
END IF;
END LOOP;
END;
- $$ language plpgsql;
+$$ language plpgsql;
CREATE TABLE risk_numbers(
date date,
|