module fundit::dataSaver
use fundit::sqlUtilities
/*
* 存数据表到mySQL或本地dolphindb,原数据表会被TRUNCATE
*
* save_table(tb_fund_performance, "raw_db.fund_performance", false)
*/
def save_table(tb, table_name, isToMySQL) {
if(isToMySQL == true) {
conn = connect_mysql('raw_db');
odbc::execute(conn, "TRUNCATE TABLE " + table_name + "_dolphin");
odbc::append(conn, tb, table_name + "_dolphin", false);
conn.close();
} else {
db = get_local_database("fundit", table_name.split(".")[0]);
//local_table = loadTable(db, table_name.split(".")[1]);
saveTable(db, tb, table_name.split(".")[1]);
}
}
/*
* 【临时】 用于将dolphin table 存到 mysql
*
*/
def save_table2(tb, table_name, isCreateTable) {
tb.addColumn(['creatorid', 'createtime', 'updaterid', 'updatetime'], [INT, DATETIME, INT, DATETIME]);
conn = connect_mysql('raw_db');
odbc::append(conn, tb, table_name , isCreateTable, false);
conn.close()
}
/*
* 存私募基金净值到本地dolphindb
*
* save_hedge_fund_nav_to_local(tb_nav)
*/
def save_hedge_fund_nav_to_local(tb_nav) {
save_table(tb_nav, "mfdb.nav", false)
}
/*
* 将数据存到本地,之后传回MySQL并同步至正式表。同一个ID且不早于源表的数据会被清除
*
* @param table
: 数据表
* @param source_table_name : DolphinDB同步到MySQL的暂存表名
* @param target_table_name : 正式的MySQL数据表名
* @param entity_id_col : 暂存表中主键中的ID字段名
* @param date_col : 暂存表中主键中的日期字段名
*
*/
def save_and_sync(table, source_table_name, target_table_name, entity_id_col, date_col) {
save_table(table, source_table_name, true);
t_table_name = iif(target_table_name.isNothing(), source_table_name, target_table_name);
s_query = "CALL raw_db.sp_sync_table_from_dolphin('" + source_table_name + "_dolphin', '" + t_table_name + "', '" + entity_id_col + "','" + date_col + "');"
conn = connect_mysql('raw_db');
odbc::execute(conn, s_query);
conn.close();
}
/*
* 建表 XXXX_nav
*/
def create_entity_nav(is_id_integer=false) {
return table(1000:0,
['entity_id', 'price_date', 'cumulative_nav'],
[iif(is_id_integer, INT, SYMBOL), DATE, DOUBLE]);
}
/*
* 建公司/经理周净值表 XXXX_nav
*/
def create_mc_nav() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'year_week', 'price_date', 'cumulative_nav', 'ret_1w', 'fund_num'],
[SYMBOL, INT, INT, STRING, DATE, DOUBLE, DOUBLE, INT]);
}
/*
* 建公司/经理净值表 XXXX_fitted_curve
*/
def create_mc_fitted_curve() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date', 'cumulative_nav', 'fund_num'],
[SYMBOL, INT, INT, STRING, DOUBLE, INT]);
}
/*
* 建公司/经理业绩表 xxx_performance
*
*/
def create_mc_performance() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date', 'price_date', 'cumulative_nav',
'ret_1m', 'ret_1m_a', 'ret_3m', 'ret_3m_a', 'ret_6m', 'ret_6m_a',
'ret_1y', 'ret_1y_a', 'ret_2y', 'ret_2y_a', 'ret_3y', 'ret_3y_a', 'ret_4y', 'ret_4y_a',
'ret_5y', 'ret_5y_a', 'ret_10y', 'ret_10y_a', 'ret_ytd', 'ret_ytd_a', 'ret_incep', 'ret_incep_a'],
[SYMBOL, INT, INT, MONTH, DATE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXXX_performance
*/
def create_entity_performance(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'price_date', 'cumulative_nav',
'ret_1m', 'ret_1m_a', 'ret_3m', 'ret_3m_a', 'ret_6m', 'ret_6m_a',
'ret_1y', 'ret_1y_a', 'ret_2y', 'ret_2y_a', 'ret_3y', 'ret_3y_a', 'ret_4y', 'ret_4y_a',
'ret_5y', 'ret_5y_a', 'ret_10y', 'ret_10y_a', 'ret_ytd', 'ret_ytd_a', 'ret_incep', 'ret_incep_a'],
[iif(is_id_integer, INT, SYMBOL), MONTH, DATE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_indicator
*/
def create_entity_indicator(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date',
'info_ratio_6m', 'm2_6m', 'tracking_error_6m',
'info_ratio_1y', 'm2_1y', 'tracking_error_1y',
'info_ratio_2y', 'm2_2y', 'tracking_error_2y', 'var_2y', 'cvar_2y',
'info_ratio_3y', 'm2_3y', 'tracking_error_3y', 'var_3y', 'cvar_3y',
'info_ratio_4y', 'm2_4y', 'tracking_error_4y', 'var_4y', 'cvar_4y',
'info_ratio_5y', 'm2_5y', 'tracking_error_5y', 'var_5y', 'cvar_5y',
'info_ratio_10y', 'm2_10y', 'tracking_error_10y', 'var_10y', 'cvar_10y',
'info_ratio_ytd', 'm2_ytd', 'tracking_error_ytd',
'info_ratio_incep', 'm2_incep','tracking_error_incep', 'var_incep', 'cvar_incep'],
[iif(is_id_integer, INT, SYMBOL), MONTH,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建公司/经理表 XXX_indicator
*/
def create_mc_indicator() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date',
'info_ratio_6m', 'm2_6m', 'tracking_error_6m',
'info_ratio_1y', 'm2_1y', 'tracking_error_1y',
'info_ratio_2y', 'm2_2y', 'tracking_error_2y', 'var_2y', 'cvar_2y',
'info_ratio_3y', 'm2_3y', 'tracking_error_3y', 'var_3y', 'cvar_3y',
'info_ratio_4y', 'm2_4y', 'tracking_error_4y', 'var_4y', 'cvar_4y',
'info_ratio_5y', 'm2_5y', 'tracking_error_5y', 'var_5y', 'cvar_5y',
'info_ratio_10y', 'm2_10y', 'tracking_error_10y', 'var_10y', 'cvar_10y',
'info_ratio_ytd', 'm2_ytd', 'tracking_error_ytd',
'info_ratio_incep', 'm2_incep','tracking_error_incep', 'var_incep', 'cvar_incep'],
[SYMBOL, INT, INT, MONTH,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_risk_stats
*
* NOTE: mfdb.fund_risk_stats 中 maxdrawdown_6m 和 maxdrawdown_ytd 因不明原因分别是 6m_maxdrawdown 和 ytd_maxdrawdown 的虚拟列!
*/
def create_entity_risk_stats(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date',
'stddev_6m', 'downsidedev_6m', 'alpha_6m', 'winrate_6m', 'beta_6m', 'skewness_6m', 'kurtosis_6m', 'worstmonth_6m', 'maxdrawdown_6m',
'stddev_1y', 'downsidedev_1y', 'alpha_1y', 'winrate_1y', 'beta_1y', 'skewness_1y', 'kurtosis_1y', 'worstmonth_1y', 'maxdrawdown_1y',
'stddev_2y', 'downsidedev_2y', 'alpha_2y', 'winrate_2y', 'beta_2y', 'skewness_2y', 'kurtosis_2y', 'worstmonth_2y', 'maxdrawdown_2y',
'stddev_3y', 'downsidedev_3y', 'alpha_3y', 'winrate_3y', 'beta_3y', 'skewness_3y', 'kurtosis_3y', 'worstmonth_3y', 'maxdrawdown_3y',
'stddev_4y', 'downsidedev_4y', 'alpha_4y', 'winrate_4y', 'beta_4y', 'skewness_4y', 'kurtosis_4y', 'worstmonth_4y', 'maxdrawdown_4y',
'stddev_5y', 'downsidedev_5y', 'alpha_5y', 'winrate_5y', 'beta_5y', 'skewness_5y', 'kurtosis_5y', 'worstmonth_5y', 'maxdrawdown_5y',
'stddev_10y', 'downsidedev_10y','alpha_10y', 'winrate_10y', 'beta_10y', 'skewness_10y', 'kurtosis_10y', 'worstmonth_10y', 'maxdrawdown_10y',
'stddev_ytd', 'downsidedev_ytd', 'alpha_ytd', 'winrate_ytd', 'beta_ytd', 'skewness_ytd', 'kurtosis_ytd', 'worstmonth_ytd', 'maxdrawdown_ytd',
'stddev_incep', 'downsidedev_incep', 'alpha_incep', 'winrate_incep', 'beta_incep', 'skewness_incep', 'kurtosis_incep', 'worstmonth_incep', 'maxdrawdown_incep'],
[iif(is_id_integer, INT, SYMBOL), MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建基金经理/公司表 XXX_risk_stats
*
*/
def create_mc_risk_stats() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date',
'stddev_6m', 'downsidedev_6m', 'alpha_6m', 'winrate_6m', 'beta_6m', 'skewness_6m', 'kurtosis_6m', 'worstmonth_6m', 'maxdrawdown_6m',
'stddev_1y', 'downsidedev_1y', 'alpha_1y', 'winrate_1y', 'beta_1y', 'skewness_1y', 'kurtosis_1y', 'worstmonth_1y', 'maxdrawdown_1y',
'stddev_2y', 'downsidedev_2y', 'alpha_2y', 'winrate_2y', 'beta_2y', 'skewness_2y', 'kurtosis_2y', 'worstmonth_2y', 'maxdrawdown_2y',
'stddev_3y', 'downsidedev_3y', 'alpha_3y', 'winrate_3y', 'beta_3y', 'skewness_3y', 'kurtosis_3y', 'worstmonth_3y', 'maxdrawdown_3y',
'stddev_4y', 'downsidedev_4y', 'alpha_4y', 'winrate_4y', 'beta_4y', 'skewness_4y', 'kurtosis_4y', 'worstmonth_4y', 'maxdrawdown_4y',
'stddev_5y', 'downsidedev_5y', 'alpha_5y', 'winrate_5y', 'beta_5y', 'skewness_5y', 'kurtosis_5y', 'worstmonth_5y', 'maxdrawdown_5y',
'stddev_10y', 'downsidedev_10y','alpha_10y', 'winrate_10y', 'beta_10y', 'skewness_10y', 'kurtosis_10y', 'worstmonth_10y', 'maxdrawdown_10y',
'stddev_ytd', 'downsidedev_ytd', 'alpha_ytd', 'winrate_ytd', 'beta_ytd', 'skewness_ytd', 'kurtosis_ytd', 'worstmonth_ytd', 'maxdrawdown_ytd',
'stddev_incep', 'downsidedev_incep', 'alpha_incep', 'winrate_incep', 'beta_incep', 'skewness_incep', 'kurtosis_incep', 'worstmonth_incep', 'maxdrawdown_incep'],
[SYMBOL, INT, INT, MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_riskadjret_stats
*/
def create_entity_riskadjret_stats(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date',
'sharperatio_6m', 'sortinoratio_6m', 'treynorratio_6m', 'jensen_6m', 'calmarratio_6m', 'omegaratio_6m', 'kapparatio_6m',
'sharperatio_1y', 'sortinoratio_1y', 'treynorratio_1y', 'jensen_1y', 'calmarratio_1y', 'omegaratio_1y', 'kapparatio_1y',
'sharperatio_2y', 'sortinoratio_2y', 'treynorratio_2y', 'jensen_2y', 'calmarratio_2y', 'omegaratio_2y', 'kapparatio_2y',
'sharperatio_3y', 'sortinoratio_3y', 'treynorratio_3y', 'jensen_3y', 'calmarratio_3y', 'omegaratio_3y', 'kapparatio_3y',
'sharperatio_4y', 'sortinoratio_4y', 'treynorratio_4y', 'jensen_4y', 'calmarratio_4y', 'omegaratio_4y', 'kapparatio_4y',
'sharperatio_5y', 'sortinoratio_5y', 'treynorratio_5y', 'jensen_5y', 'calmarratio_5y', 'omegaratio_5y', 'kapparatio_5y',
'sharperatio_10y', 'sortinoratio_10y', 'treynorratio_10y', 'jensen_10y', 'calmarratio_10y', 'omegaratio_10y', 'kapparatio_10y',
'sharperatio_ytd', 'sortinoratio_ytd', 'treynorratio_ytd', 'jensen_ytd', 'calmarratio_ytd', 'omegaratio_ytd', 'kapparatio_ytd',
'sharperatio_incep', 'sortinoratio_incep', 'treynorratio_incep', 'jensen_incep', 'calmarratio_incep', 'omegaratio_incep', 'kapparatio_incep'],
[iif(is_id_integer, INT, SYMBOL), MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建基金经理/公司表 XXX_riskadjret_stats
*/
def create_mc_riskadjret_stats() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date',
'sharperatio_6m', 'sortinoratio_6m', 'treynorratio_6m', 'jensen_6m', 'calmarratio_6m', 'omegaratio_6m', 'kapparatio_6m',
'sharperatio_1y', 'sortinoratio_1y', 'treynorratio_1y', 'jensen_1y', 'calmarratio_1y', 'omegaratio_1y', 'kapparatio_1y',
'sharperatio_2y', 'sortinoratio_2y', 'treynorratio_2y', 'jensen_2y', 'calmarratio_2y', 'omegaratio_2y', 'kapparatio_2y',
'sharperatio_3y', 'sortinoratio_3y', 'treynorratio_3y', 'jensen_3y', 'calmarratio_3y', 'omegaratio_3y', 'kapparatio_3y',
'sharperatio_4y', 'sortinoratio_4y', 'treynorratio_4y', 'jensen_4y', 'calmarratio_4y', 'omegaratio_4y', 'kapparatio_4y',
'sharperatio_5y', 'sortinoratio_5y', 'treynorratio_5y', 'jensen_5y', 'calmarratio_5y', 'omegaratio_5y', 'kapparatio_5y',
'sharperatio_10y', 'sortinoratio_10y', 'treynorratio_10y', 'jensen_10y', 'calmarratio_10y', 'omegaratio_10y', 'kapparatio_10y',
'sharperatio_ytd', 'sortinoratio_ytd', 'treynorratio_ytd', 'jensen_ytd', 'calmarratio_ytd', 'omegaratio_ytd', 'kapparatio_ytd',
'sharperatio_incep', 'sortinoratio_incep', 'treynorratio_incep', 'jensen_incep', 'calmarratio_incep', 'omegaratio_incep', 'kapparatio_incep'],
[SYMBOL, INT, INT, MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_style_stats
*/
def create_entity_style_stats(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date',
'upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m',
'upsidecapture_ret_1y', 'downsidecapture_ret_1y', 'upsidecapture_ratio_1y', 'downsidecapture_ratio_1y',
'upsidecapture_ret_2y', 'downsidecapture_ret_2y', 'upsidecapture_ratio_2y', 'downsidecapture_ratio_2y',
'upsidecapture_ret_3y', 'downsidecapture_ret_3y', 'upsidecapture_ratio_3y', 'downsidecapture_ratio_3y',
'upsidecapture_ret_4y', 'downsidecapture_ret_4y', 'upsidecapture_ratio_4y', 'downsidecapture_ratio_4y',
'upsidecapture_ret_5y', 'downsidecapture_ret_5y', 'upsidecapture_ratio_5y', 'downsidecapture_ratio_5y',
'upsidecapture_ret_10y', 'downsidecapture_ret_10y', 'upsidecapture_ratio_10y', 'downsidecapture_ratio_10y',
'upsidecapture_ret_ytd', 'downsidecapture_ret_ytd', 'upsidecapture_ratio_ytd', 'downsidecapture_ratio_ytd',
'upsidecapture_ret_incep', 'downsidecapture_ret_incep', 'upsidecapture_ratio_incep', 'downsidecapture_ratio_incep'],
[iif(is_id_integer, INT, SYMBOL), MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_ms_stats
*/
def create_entity_ms_stats(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date',
'ms_return_3y', 'ms_rar_3y', 'ms_risk_3y',
'ms_return_5y', 'ms_rar_5y', 'ms_risk_5y',
'ms_return_10y', 'ms_rar_10y', 'ms_risk_10y'],
[iif(is_id_integer, INT, SYMBOL), MONTH,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建基金经理/公司表 XXX_style_stats
*/
def create_mc_style_stats() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date',
'upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m',
'upsidecapture_ret_1y', 'downsidecapture_ret_1y', 'upsidecapture_ratio_1y', 'downsidecapture_ratio_1y',
'upsidecapture_ret_2y', 'downsidecapture_ret_2y', 'upsidecapture_ratio_2y', 'downsidecapture_ratio_2y',
'upsidecapture_ret_3y', 'downsidecapture_ret_3y', 'upsidecapture_ratio_3y', 'downsidecapture_ratio_3y',
'upsidecapture_ret_4y', 'downsidecapture_ret_4y', 'upsidecapture_ratio_4y', 'downsidecapture_ratio_4y',
'upsidecapture_ret_5y', 'downsidecapture_ret_5y', 'upsidecapture_ratio_5y', 'downsidecapture_ratio_5y',
'upsidecapture_ret_10y', 'downsidecapture_ret_10y', 'upsidecapture_ratio_10y', 'downsidecapture_ratio_10y',
'upsidecapture_ret_ytd', 'downsidecapture_ret_ytd', 'upsidecapture_ratio_ytd', 'downsidecapture_ratio_ytd',
'upsidecapture_ret_incep', 'downsidecapture_ret_incep', 'upsidecapture_ratio_incep', 'downsidecapture_ratio_incep'],
[SYMBOL, INT, INT, MONTH,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 XXX_bfi_bm_indicator
*/
def create_entity_bfi_indicator(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'factor_id',
'upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m',
'alpha_6m', 'winrate_6m', 'beta_6m', 'info_ratio_6m', 'tracking_error_6m', 'jensen_6m',
'upsidecapture_ret_1y', 'downsidecapture_ret_1y', 'upsidecapture_ratio_1y', 'downsidecapture_ratio_1y',
'alpha_1y', 'winrate_1y', 'beta_1y', 'info_ratio_1y', 'tracking_error_1y', 'jensen_1y',
'upsidecapture_ret_2y', 'downsidecapture_ret_2y', 'upsidecapture_ratio_2y', 'downsidecapture_ratio_2y',
'alpha_2y', 'winrate_2y', 'beta_2y', 'info_ratio_2y', 'tracking_error_2y', 'jensen_2y',
'upsidecapture_ret_3y', 'downsidecapture_ret_3y', 'upsidecapture_ratio_3y', 'downsidecapture_ratio_3y',
'alpha_3y', 'winrate_3y', 'beta_3y', 'info_ratio_3y', 'tracking_error_3y', 'jensen_3y',
'upsidecapture_ret_4y', 'downsidecapture_ret_4y', 'upsidecapture_ratio_4y', 'downsidecapture_ratio_4y',
'alpha_4y', 'winrate_4y', 'beta_4y', 'info_ratio_4y', 'tracking_error_4y', 'jensen_4y',
'upsidecapture_ret_5y', 'downsidecapture_ret_5y', 'upsidecapture_ratio_5y', 'downsidecapture_ratio_5y',
'alpha_5y', 'winrate_5y', 'beta_5y', 'info_ratio_5y', 'tracking_error_5y', 'jensen_5y',
'upsidecapture_ret_10y', 'downsidecapture_ret_10y', 'upsidecapture_ratio_10y', 'downsidecapture_ratio_10y',
'alpha_10y', 'winrate_10y', 'beta_10y', 'info_ratio_10y', 'tracking_error_10y', 'jensen_10y',
'upsidecapture_ret_ytd', 'downsidecapture_ret_ytd', 'upsidecapture_ratio_ytd', 'downsidecapture_ratio_ytd',
'alpha_ytd', 'winrate_ytd', 'beta_ytd', 'info_ratio_ytd', 'tracking_error_ytd', 'jensen_ytd',
'upsidecapture_ret_incep', 'downsidecapture_ret_incep', 'upsidecapture_ratio_incep', 'downsidecapture_ratio_incep',
'alpha_incep', 'winrate_incep', 'beta_incep', 'info_ratio_incep', 'tracking_error_incep', 'jensen_incep'],
[iif(is_id_integer, INT, SYMBOL), MONTH, SYMBOL,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE] );
}
/*
* 建基金经理/公司表 XXX_bfi_bm_indicator
*/
def create_mc_bfi_indicator() {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date', 'factor_id',
'upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m',
'alpha_6m', 'winrate_6m', 'beta_6m', 'info_ratio_6m', 'tracking_error_6m', 'jensen_6m',
'upsidecapture_ret_1y', 'downsidecapture_ret_1y', 'upsidecapture_ratio_1y', 'downsidecapture_ratio_1y',
'alpha_1y', 'winrate_1y', 'beta_1y', 'info_ratio_1y', 'tracking_error_1y', 'jensen_1y',
'upsidecapture_ret_2y', 'downsidecapture_ret_2y', 'upsidecapture_ratio_2y', 'downsidecapture_ratio_2y',
'alpha_2y', 'winrate_2y', 'beta_2y', 'info_ratio_2y', 'tracking_error_2y', 'jensen_2y',
'upsidecapture_ret_3y', 'downsidecapture_ret_3y', 'upsidecapture_ratio_3y', 'downsidecapture_ratio_3y',
'alpha_3y', 'winrate_3y', 'beta_3y', 'info_ratio_3y', 'tracking_error_3y', 'jensen_3y',
'upsidecapture_ret_4y', 'downsidecapture_ret_4y', 'upsidecapture_ratio_4y', 'downsidecapture_ratio_4y',
'alpha_4y', 'winrate_4y', 'beta_4y', 'info_ratio_4y', 'tracking_error_4y', 'jensen_4y',
'upsidecapture_ret_5y', 'downsidecapture_ret_5y', 'upsidecapture_ratio_5y', 'downsidecapture_ratio_5y',
'alpha_5y', 'winrate_5y', 'beta_5y', 'info_ratio_5y', 'tracking_error_5y', 'jensen_5y',
'upsidecapture_ret_10y', 'downsidecapture_ret_10y', 'upsidecapture_ratio_10y', 'downsidecapture_ratio_10y',
'alpha_10y', 'winrate_10y', 'beta_10y', 'info_ratio_10y', 'tracking_error_10y', 'jensen_10y',
'upsidecapture_ret_ytd', 'downsidecapture_ret_ytd', 'upsidecapture_ratio_ytd', 'downsidecapture_ratio_ytd',
'alpha_ytd', 'winrate_ytd', 'beta_ytd', 'info_ratio_ytd', 'tracking_error_ytd', 'jensen_ytd',
'upsidecapture_ret_incep', 'downsidecapture_ret_incep', 'upsidecapture_ratio_incep', 'downsidecapture_ratio_incep',
'alpha_incep', 'winrate_incep', 'beta_incep', 'info_ratio_incep', 'tracking_error_incep', 'jensen_incep'],
[SYMBOL, INT, INT, MONTH, SYMBOL,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE] );
}
/*
* 建表 xxx_performance_weekly
*/
def create_entity_performance_weekly(is_id_integer=false) {
return table(1000:0,
['entity_id', 'year_week', 'end_year', 'week_of_year', 'price_date', 'cumulative_nav', 'ret_1w'],
[iif(is_id_integer, INT, SYMBOL), STRING, STRING, SHORT, DATE, DOUBLE, DOUBLE]);
}
/*
* 建表 xxx_latest_performance
*/
def create_entity_latest_performance(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'price_date', 'pre_price_date', 'nav', 'cumulative_nav',
'net_value_change', 'ret_1d', 'ret_1w', 'ret_1m', 'ret_3m', 'ret_6m',
'ret_1y', 'ret_2y', 'ret_3y', 'ret_4y', 'ret_5y', 'ret_10y', 'ret_ytd', 'ret_incep', 'ret_incep_a', 'ret_incep_a_all', 'ret_incep_a_gips',
'maxdrawdown_1m', 'maxdrawdown_3m', 'maxdrawdown_1y', 'maxdrawdown_incep', 'calmarratio_incep',
'ret_1y_a', 'ret_2y_a', 'ret_3y_a', 'ret_4y_a', 'ret_5y_a', 'ret_10y_a'],
[iif(is_id_integer, INT, SYMBOL), STRING, DATE, DATE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 xxx_index_coe
*
*/
def create_entity_index_coe(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'index_id',
'coe_1y', 'coe_3y', 'coe_5y', //'info_ratio_1y', 'info_ratio_3y', 'info_ratio_5y',
't_value_1y', 't_value_3y', 't_value_5y', 'beta_1y', 'beta_3y', 'beta_5y'],
[iif(is_id_integer, INT, SYMBOL), MONTH, SYMBOL,
DOUBLE, DOUBLE, DOUBLE, //DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 xxx_index_coe
*
*/
def create_mc_index_coe(is_id_integer=false) {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date', 'index_id',
'coe_1y', 'coe_3y', 'coe_5y', //'info_ratio_1y', 'info_ratio_3y', 'info_ratio_5y',
't_value_1y', 't_value_3y', 't_value_5y', 'beta_1y', 'beta_3y', 'beta_5y'],
[SYMBOL, INT, INT, MONTH, SYMBOL,
DOUBLE, DOUBLE, DOUBLE, //DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}
/*
* 建表 xxx_factor_bfi_max_r2
*
*/
def create_entity_bfi_max_r2(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'factor_id', 'coe', 'r2', 'performance_flag', 'rz_portrait'],
[iif(is_id_integer, INT, SYMBOL), MONTH, SYMBOL, DOUBLE, DOUBLE, STRING, STRING]);
}
/*
* 建表 xxx_factor_bfi
*
*/
def create_entity_factor_bfi(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'factor_id',
'coe', 'r2', 'performance_flag', 't_value_1y', 'beta_1y'],
[iif(is_id_integer, INT, SYMBOL), MONTH, SYMBOL,
DOUBLE, DOUBLE, STRING, DOUBLE, DOUBLE]);
}
/*
* 建基金经理/公司 bfi连接表 xxx_factor_bfi
*
*/
def create_mc_factor_bfi(is_id_integer=false) {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'end_date', 'factor_id',
'coe', 'r2', 'performance_flag', 't_value_1y', 'beta_1y'],
[SYMBOL, INT, INT, MONTH, SYMBOL,
DOUBLE, DOUBLE, STRING, DOUBLE, DOUBLE]);
}
/*
* 建表 XXXX_indicator_ranking
*/
def create_entity_indicator_ranking(is_id_integer=false) {
return table(1000:0,
['entity_id', 'end_date', 'category_id', 'indicator_id',
'indicator_1m', 'absrank_1m', 'perrank_1m', 'indicator_3m', 'absrank_3m', 'perrank_3m',
'indicator_6m', 'absrank_6m', 'perrank_6m', 'indicator_1y', 'absrank_1y', 'perrank_1y',
'indicator_2y', 'absrank_2y', 'perrank_2y', 'indicator_3y', 'absrank_3y', 'perrank_3y',
'indicator_5y', 'absrank_5y', 'perrank_5y',
'indicator_10y', 'absrank_10y', 'perrank_10y', 'indicator_ytd', 'absrank_ytd', 'perrank_ytd'],
[iif(is_id_integer, INT, SYMBOL), STRING, SYMBOL, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT]);
}
/*
* 建基金经理/公司表 XXXX_indicator_ranking
*/
def create_mc_indicator_ranking(is_id_integer=false) {
return table(1000:0,
['entity_id', 'curve_type', 'strategy', 'category_id', 'end_date', 'indicator_id',
'indicator_1m', 'absrank_1m', 'perrank_1m', 'indicator_3m', 'absrank_3m', 'perrank_3m',
'indicator_6m', 'absrank_6m', 'perrank_6m', 'indicator_1y', 'absrank_1y', 'perrank_1y',
'indicator_2y', 'absrank_2y', 'perrank_2y', 'indicator_3y', 'absrank_3y', 'perrank_3y',
'indicator_5y', 'absrank_5y', 'perrank_5y',
'indicator_10y', 'absrank_10y', 'perrank_10y', 'indicator_ytd', 'absrank_ytd', 'perrank_ytd'],
[SYMBOL, INT, INT, SYMBOL, STRING, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT,
DOUBLE, INT, INT,
DOUBLE, INT, INT, DOUBLE, INT, INT]);
}
/*
* 建表 XXXX_indicator_ranking_num, raise_type 没有用
*/
def create_entity_indicator_ranking_num() {
return table(1000:0,
['end_date', 'category_id', 'raise_type', 'indicator_id',
'avg_1m', 'avg_1m_cnt', 'perrank_percent_5_1m', 'perrank_percent_10_1m', 'perrank_percent_25_1m', 'perrank_percent_50_1m',
'perrank_percent_75_1m', 'perrank_percent_90_1m', 'perrank_percent_95_1m', 'best_1m', 'worst_1m',
'avg_3m', 'avg_3m_cnt', 'perrank_percent_5_3m', 'perrank_percent_10_3m', 'perrank_percent_25_3m', 'perrank_percent_50_3m',
'perrank_percent_75_3m', 'perrank_percent_90_3m', 'perrank_percent_95_3m', 'best_3m', 'worst_3m',
'avg_6m', 'avg_6m_cnt', 'perrank_percent_5_6m', 'perrank_percent_10_6m', 'perrank_percent_25_6m', 'perrank_percent_50_6m',
'perrank_percent_75_6m', 'perrank_percent_90_6m', 'perrank_percent_95_6m', 'best_6m', 'worst_6m',
'avg_1y', 'avg_1y_cnt', 'perrank_percent_5_1y', 'perrank_percent_10_1y', 'perrank_percent_25_1y', 'perrank_percent_50_1y',
'perrank_percent_75_1y', 'perrank_percent_90_1y', 'perrank_percent_95_1y', 'best_1y', 'worst_1y',
'avg_2y', 'avg_2y_cnt', 'perrank_percent_5_2y', 'perrank_percent_10_2y', 'perrank_percent_25_2y', 'perrank_percent_50_2y',
'perrank_percent_75_2y', 'perrank_percent_90_2y', 'perrank_percent_95_2y', 'best_2y', 'worst_2y',
'avg_3y', 'avg_3y_cnt', 'perrank_percent_5_3y', 'perrank_percent_10_3y', 'perrank_percent_25_3y', 'perrank_percent_50_3y',
'perrank_percent_75_3y', 'perrank_percent_90_3y', 'perrank_percent_95_3y', 'best_3y', 'worst_3y',
'avg_5y', 'avg_5y_cnt', 'perrank_percent_5_5y', 'perrank_percent_10_5y', 'perrank_percent_25_5y', 'perrank_percent_50_5y',
'perrank_percent_75_5y', 'perrank_percent_90_5y', 'perrank_percent_95_5y', 'best_5y', 'worst_5y',
'avg_10y', 'avg_10y_cnt', 'perrank_percent_5_10y', 'perrank_percent_10_10y', 'perrank_percent_25_10y', 'perrank_percent_50_10y',
'perrank_percent_75_10y', 'perrank_percent_90_10y', 'perrank_percent_95_10y', 'best_10y', 'worst_10y',
'avg_ytd', 'avg_ytd_cnt', 'perrank_percent_5_ytd', 'perrank_percent_10_ytd', 'perrank_percent_25_ytd', 'perrank_percent_50_ytd',
'perrank_percent_75_ytd', 'perrank_percent_90_ytd', 'perrank_percent_95_ytd', 'best_ytd', 'worst_ytd'],
[STRING, SYMBOL, INT, INT,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE ]);
}
/*
* 建基金经理/公司表 XXXX_indicator_ranking_num, raise_type 没有用
*/
def create_mc_indicator_ranking_num() {
return table(1000:0,
['curve_type', 'strategy', 'category_id', 'end_date', 'indicator_id',
'avg_1m', 'avg_1m_cnt', 'perrank_percent_5_1m', 'perrank_percent_10_1m', 'perrank_percent_25_1m', 'perrank_percent_50_1m',
'perrank_percent_75_1m', 'perrank_percent_90_1m', 'perrank_percent_95_1m', 'best_1m', 'worst_1m',
'avg_3m', 'avg_3m_cnt', 'perrank_percent_5_3m', 'perrank_percent_10_3m', 'perrank_percent_25_3m', 'perrank_percent_50_3m',
'perrank_percent_75_3m', 'perrank_percent_90_3m', 'perrank_percent_95_3m', 'best_3m', 'worst_3m',
'avg_6m', 'avg_6m_cnt', 'perrank_percent_5_6m', 'perrank_percent_10_6m', 'perrank_percent_25_6m', 'perrank_percent_50_6m',
'perrank_percent_75_6m', 'perrank_percent_90_6m', 'perrank_percent_95_6m', 'best_6m', 'worst_6m',
'avg_1y', 'avg_1y_cnt', 'perrank_percent_5_1y', 'perrank_percent_10_1y', 'perrank_percent_25_1y', 'perrank_percent_50_1y',
'perrank_percent_75_1y', 'perrank_percent_90_1y', 'perrank_percent_95_1y', 'best_1y', 'worst_1y',
'avg_2y', 'avg_2y_cnt', 'perrank_percent_5_2y', 'perrank_percent_10_2y', 'perrank_percent_25_2y', 'perrank_percent_50_2y',
'perrank_percent_75_2y', 'perrank_percent_90_2y', 'perrank_percent_95_2y', 'best_2y', 'worst_2y',
'avg_3y', 'avg_3y_cnt', 'perrank_percent_5_3y', 'perrank_percent_10_3y', 'perrank_percent_25_3y', 'perrank_percent_50_3y',
'perrank_percent_75_3y', 'perrank_percent_90_3y', 'perrank_percent_95_3y', 'best_3y', 'worst_3y',
'avg_5y', 'avg_5y_cnt', 'perrank_percent_5_5y', 'perrank_percent_10_5y', 'perrank_percent_25_5y', 'perrank_percent_50_5y',
'perrank_percent_75_5y', 'perrank_percent_90_5y', 'perrank_percent_95_5y', 'best_5y', 'worst_5y',
'avg_10y', 'avg_10y_cnt', 'perrank_percent_5_10y', 'perrank_percent_10_10y', 'perrank_percent_25_10y', 'perrank_percent_50_10y',
'perrank_percent_75_10y', 'perrank_percent_90_10y', 'perrank_percent_95_10y', 'best_10y', 'worst_10y',
'avg_ytd', 'avg_ytd_cnt', 'perrank_percent_5_ytd', 'perrank_percent_10_ytd', 'perrank_percent_25_ytd', 'perrank_percent_50_ytd',
'perrank_percent_75_ytd', 'perrank_percent_90_ytd', 'perrank_percent_95_ytd', 'best_ytd', 'worst_ytd'],
[INT, INT, SYMBOL, STRING, INT,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE ]);
}
/*
* 根据 mysql 表改动某些字段
*/
def chg_columns_for_mysql(mutable tb_mysql, id_col_name) {
tb_mysql.rename!('entity_id', id_col_name);
// 将 dolphinDB 的 MONTH 换成 MySQL 的 YYYY-MM 格式
v_end_date = EXEC end_date.temporalFormat('yyyy-MM') FROM tb_mysql;
tb_mysql.replaceColumn!('end_date', v_end_date);
}
/*
* 按照 XXX_performance 表结构准备数据记录
*
* @param entity_info : [COLUMNS] entity_id, end_date (用于筛掉不必要的老记录), [curve_type, strategy]
* @param indicators : 指标类型-区间:数据表
* @param entity_performance : 被更新的数据表
* @param extra_keys : 只有基金经理和公司会用到,对应 [curve_type, strategy]
*
* TODO: price_date is NULL for some records
*/
def generate_entity_performance(entity_info, indicators, isToMySQL, mutable entity_performance, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(isToMySQL) {
if(indicators['PBI-3M'].isVoid() || indicators['PBI-3M'].size() == 0) return;
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol('price_date'),
sqlCol(['nav', 'ret', 'ret', 'trailing_ret', 'trailing_ret_a'], , ['cumulative_nav', 'ret_1m', 'ret_1m_a', 'ret_3m', 'ret_3m_a'])
),
from = ej(indicators['PBI-3M'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录, 这里用系统字段的别名 fi_price_date 有点脏,但目前没办法
).eval();
v_trailing = ['6m', '1y', '2y', '3y', '4y', '5y', '10y', 'ytd', 'incep'];
for(tr in v_trailing) {
col_name = 'ret_' + tr;
col_a_name = 'ret_' + tr + '_a';
t.addColumn([col_name, col_a_name], [DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = [, ],
from =
).eval();
}
}
INSERT INTO entity_performance SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_risk_stats 表结构准备数据记录
*
*
*/
def generate_entity_risk_stats(entity_info, indicators, isToMySQL, mutable entity_risk_stats, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return;
if(isToMySQL) {
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol(['std_dev_a', 'ds_dev_a', 'alpha_a', 'winrate', 'beta', 'skewness', 'kurtosis', 'wrst_month', 'drawdown'],
, ['stddev_6m', 'downsidedev_6m', 'alpha_6m', 'winrate_6m', 'beta_6m', 'skewness_6m', 'kurtosis_6m', 'worstmonth_6m', 'maxdrawdown_6m'])
),
from = ej(indicators['PBI-6M'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录
).eval();
v_trailing = ['1y', '2y', '3y', '4y', '5y', '10y', 'ytd', 'incep'];
for(tr in v_trailing) {
col_std_dev = 'stddev_' + tr;
col_ds_dev = 'downsidedev_' + tr;
col_alpha = 'alpha_' + tr;
col_winrate = 'winrate_' + tr;
col_beta = 'beta_' + tr;
col_skewness = 'skewness_' + tr;
col_kurtosis = 'kurtosis_' + tr;
col_wrst_month = 'worstmonth_' + tr;
col_drawdown = 'maxdrawdown_' + tr;
t.addColumn([col_std_dev, col_ds_dev, col_alpha, col_winrate, col_beta, col_skewness, col_kurtosis, col_wrst_month, col_drawdown],
[DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = [, , ,
, , ,
, , ],
from =
).eval();
}
}
INSERT INTO entity_risk_stats SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_riskadjret_stats 表结构准备数据记录
*
*
*/
def generate_entity_riskadjret_stats(entity_info, indicators, isToMySQL, mutable entity_riskadjret_stats, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return;
if(isToMySQL) {
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol(['sharpe_a', 'sortino_a', 'treynor', 'jensen_a', 'calmar', 'omega', 'kappa'],
, ['sharperatio_6m', 'sortinoratio_6m', 'treynorratio_6m', 'jensen_6m', 'calmarratio_6m', 'omegaratio_6m', 'kapparatio_6m'])
),
from = ej(indicators['PBI-6M'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录
).eval();
v_trailing = ['1y', '2y', '3y', '4y', '5y', '10y', 'ytd', 'incep'];
for(tr in v_trailing) {
col_sharpe = 'sharperatio_' + tr;
col_sortino = 'sortinoratio_' + tr;
col_treynor = 'treynorratio_' + tr;
col_jensen = 'jensen_' + tr;
col_calmar = 'calmarratio_' + tr;
col_omega = 'omegaratio_' + tr;
col_kappa = 'kapparatio_' + tr;
t.addColumn([col_sharpe, col_sortino, col_treynor, col_jensen, col_calmar, col_omega, col_kappa],
[DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = [, , ,
, , ,
],
from =
).eval();
}
}
INSERT INTO entity_riskadjret_stats SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_indicator 表结构准备数据记录
*
*
*/
def generate_entity_indicator(entity_info, indicators, isToMySQL, mutable entity_indicator, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return;
if(isToMySQL) {
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol(['info_a', 'm2_a', 'track_error_a'],
, ['info_ratio_6m', 'm2_6m', 'tracking_error_6m'])
),
from = ej(indicators['PBI-6M'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录
).eval();
v_trailing = ['1y', '2y', '3y', '4y', '5y', '10y', 'ytd', 'incep'];
for(tr in v_trailing) {
col_info = 'info_ratio_' + tr;
col_m2 = 'm2_' + tr;
col_track_error = 'tracking_error_' + tr;
col_var = 'var_' + tr;
col_cvar = 'cvar_' + tr;
t.addColumn([col_info, col_m2, col_track_error],
[DOUBLE, DOUBLE, DOUBLE]);
if(tr != 'ytd') // YTD 没有 VAR, CVAR
t.addColumn([col_var, col_cvar], [DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = iif(tr != 'ytd', [, , ,
, ], [, , ]),
from =
).eval();
}
}
// var, cvar 只有2年及更长时间才计算
t.dropColumns!(['var_1y', 'cvar_1y']);
INSERT INTO entity_indicator SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_style_stats 表结构准备数据记录
*
*
*/
def generate_entity_style_stats(entity_info, indicators, isToMySQL, mutable entity_style_stats, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return;
if(isToMySQL) {
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol(['upside_capture_ret', 'downside_capture_ret', 'upside_capture_ratio', 'downside_capture_ratio'],
, ['upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m'])
),
from = ej(indicators['PBI-6M'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录
).eval();
v_trailing = ['1y', '2y', '3y', '4y', '5y', '10y', 'ytd', 'incep'];
for(tr in v_trailing) {
col_upside_capture_ret = 'upsidecapture_ret_' + tr;
col_downside_capture_ret = 'downsidecapture_ret_' + tr;
col_upside_capture_ratio = 'upsidecapture_ratio_' + tr;
col_downside_capture_ratio = 'downsidecapture_ratio_' + tr;
t.addColumn([col_upside_capture_ret, col_downside_capture_ret, col_upside_capture_ratio, col_downside_capture_ratio],
[DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = [, ,
, ],
from =
).eval();
}
}
INSERT INTO entity_style_stats SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_ms_stats 表结构准备数据记录
*
*
*/
def generate_entity_ms_stats(entity_info, indicators, isToMySQL, mutable entity_ms_stats, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
if(indicators['PBI-3Y'].isVoid() || indicators['PBI-3Y'].size() == 0) return;
if(isToMySQL) {
t = sql(select =(sqlCol('entity_id'),
sqlCol(v_extra_keys.join('end_date')),
sqlCol(['ms_ret_a', 'ms_rar_a', 'ms_risk_a'],
, ['ms_return_3y', 'ms_rar_3y', 'ms_risk_3y'])
),
from = ej(indicators['PBI-3Y'] AS a, entity_info AS fi, ['entity_id'].join(v_extra_keys)),
where = < end_date >= fi_price_date.month()> // 过滤掉不必更新的旧记录
).eval();
v_trailing = ['5y', '10y'];
for(tr in v_trailing) {
col_ms_ret = 'ms_return_' + tr;
col_ms_rar = 'ms_rar_' + tr;
col_ms_risk = 'ms_risk_' + tr;
t.addColumn([col_ms_ret, col_ms_rar, col_ms_risk],
[DOUBLE, DOUBLE, DOUBLE]);
if(!indicators['PBI-'+tr.upper()].isVoid()) {
sqlUpdate(table = t,
updates = [, , ],
from =
).eval();
}
}
INSERT INTO entity_ms_stats SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_bfi_bm_indicator 表结构准备数据记录
*
* @param entity_info : [COLUMNS] entity_id, end_date, benchmark_id, inception_date, ini_value [,curve_type, strategy]
* @param indicators
*
* TODO: why we need isToMySQL here?
* 其它的指标恐怕也要按这个改,因为私募可能会有近6月没有数据但近2年之类的周期有数据的情况!
*/
def generate_entity_bfi_indicator(entity_info, indicators, isToMySQL, mutable entity_bfi_indicator, extra_keys=[]) {
t = null;
if(extra_keys.isNothing() || extra_keys.isVoid() || extra_keys.size() == 0) v_extra_keys = array(STRING);
else v_extra_keys = extra_keys;
v_cols_from = ['upside_capture_ret', 'downside_capture_ret', 'upside_capture_ratio', 'downside_capture_ratio', 'alpha_a', 'winrate', 'beta', 'info_a', 'track_error_a', 'jensen_a'];
v_cols_to = ['upsidecapture_ret', 'downsidecapture_ret', 'upsidecapture_ratio', 'downsidecapture_ratio', 'alpha', 'winrate', 'beta', 'info_ratio', 'tracking_error', 'jensen'];
v_cols_useless = ['track_error', 'info', 'alpha', 'treynor', 'jensen', 'm2', 'm2_a']; // 标准指标中不被当前表覆盖的数据点
v_join_key = ['entity_id', 'benchmark_id', 'end_date'].join(v_extra_keys);
if(isToMySQL) {
t = lj(
lj(
lj(
lj(
lj(
lj(
lj(
lj(
lj(entity_info,
indicators['BFI-6M'] AS t_6m, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-1Y'] AS t_1y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-2Y'] AS t_2y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-3Y'] AS t_3y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-4Y'] AS t_4y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-5Y'] AS t_5y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-10Y'] AS t_10y, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-YTD'] AS t_ytd, v_join_key).dropColumns!(v_cols_useless),
indicators['BFI-INCEP'] AS t_incep, v_join_key).dropColumns!(v_cols_useless);
t.rename!(v_cols_from, v_cols_to + '_6m');
t.rename!('t_1y_' + v_cols_from, v_cols_to + '_1y');
t.rename!('t_2y_' + v_cols_from, v_cols_to + '_2y');
t.rename!('t_3y_' + v_cols_from, v_cols_to + '_3y');
t.rename!('t_4y_' + v_cols_from, v_cols_to + '_4y');
t.rename!('t_5y_' + v_cols_from, v_cols_to + '_5y');
t.rename!('t_10y_' + v_cols_from, v_cols_to + '_10y');
t.rename!('t_ytd_' + v_cols_from, v_cols_to + '_ytd');
t.rename!('t_incep_' + v_cols_from, v_cols_to + '_incep');
if(t.columnNames().find('inception_date') >= 0) t.dropColumns!('inception_date');
if(t.columnNames().find('ini_value') >= 0) t.dropColumns!('ini_value');
t.rename!('benchmark_id', 'factor_id');
entity_bfi_indicator.tableInsert(t.reorderColumns!(entity_bfi_indicator.colNames()));
} else {
}
}
/*
* 按照 XXX_performance_weekly 表结构准备数据记录
*
*
*/
def generate_entity_performance_weekly(entity_info, ret_w, isToMySQL, mutable entity_performance_weekly) {
t = null;
if(ret_w.isVoid() || ret_w.size() == 0) return;
if(isToMySQL) {
t = SELECT entity_id, year_week, year_week.left(4)$INT AS end_year, year_week.right(2)$INT AS week_of_year, price_date,
cumulative_nav, ret_1w
FROM ret_w r
INNER JOIN entity_info fi ON r.entity_id = fi.entity_id
WHERE r.price_date >= fi.price_date; // 过滤掉不必更新的旧记录
INSERT INTO entity_performance_weekly SELECT * FROM t;
} else {
}
}
/*
* 按照 XXX_latest_performance 表结构准备数据记录
*
*
*/
def generate_entity_latest_performance(entity_info, perf_latest, isToMySQL, mutable entity_latest_performance) {
t = null;
if(perf_latest.isVoid() || perf_latest.size() == 0) return;
if(isToMySQL) {
t = SELECT r.*
FROM perf_latest r
INNER JOIN entity_info fi ON r.entity_id = fi.entity_id
WHERE r.price_date >= fi.price_date; // 过滤掉不必更新的旧记录
INSERT INTO entity_latest_performance SELECT * FROM t;
} else {
}
}