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并同步至正式表 * * */ def save_and_sync(table, source_table_name, target_table_name) { 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 + "');" 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_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_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_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_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_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_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', '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), STRING, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, 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_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 ]); } /* * 根据 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 表结构准备数据记录 * * */ def generate_entity_performance(entity_info, indicators, isToMySQL, mutable entity_performance) { t = null; if(isToMySQL) { if(indicators['PBI-3M'].isVoid() || indicators['PBI-3M'].size() == 0) return; t = SELECT entity_id AS entity_id, end_date, price_date, nav AS cumulative_nav, ret AS ret_1m, ret AS ret_1m_a, trailing_ret AS ret_3m, trailing_ret_a AS ret_3m_a FROM indicators['PBI-3M'] AS ind INNER JOIN entity_info fi ON ind.entity_id = fi.entity_id WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录 UPDATE t SET ret_6m = trailing_ret, ret_6m_a = trailing_ret_a FROM ej(t, indicators['PBI-6M'], ['entity_id', 'end_date']); UPDATE t SET ret_1y = trailing_ret, ret_1y_a = trailing_ret_a FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']); UPDATE t SET ret_2y = trailing_ret, ret_2y_a = trailing_ret_a FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']); UPDATE t SET ret_3y = trailing_ret, ret_3y_a = trailing_ret_a FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']); UPDATE t SET ret_4y = trailing_ret, ret_4y_a = trailing_ret_a FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']); UPDATE t SET ret_5y = trailing_ret, ret_5y_a = trailing_ret_a FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']); UPDATE t SET ret_10y = trailing_ret, ret_10y_a = trailing_ret_a FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']); UPDATE t SET ret_ytd = trailing_ret, ret_ytd_a = trailing_ret_a FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']); // 取消了 ret_incep_a_all （没意义） 和 ret_incep_a_gips (ret_incep_a 与之相等） UPDATE t SET ret_incep = trailing_ret, ret_incep_a = trailing_ret_a FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']); INSERT INTO entity_performance SELECT * FROM t; } else { } } /* * 按照 XXX_risk_stats 表结构准备数据记录 * * */ def generate_entity_risk_stats(entity_info, indicators, isToMySQL, mutable entity_risk_stats) { t = null; if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return; if(isToMySQL) { t = SELECT entity_id, end_date, std_dev_a AS stddev_6m, ds_dev_a AS downsidedev_6m, alpha_a AS alpha_6m, winrate AS winrate_6m, beta AS beta_6m, skewness AS skewness_6m, kurtosis AS kurtosis_6m, wrst_month AS worstmonth_6m, drawdown AS maxdrawdown_6m // mfdb中的真实字段名是 6m_maxdrawdown FROM indicators['PBI-6M'] AS ind INNER JOIN entity_info fi ON ind.entity_id = fi.entity_id WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录 UPDATE t SET stddev_1y = std_dev_a, downsidedev_1y = ds_dev_a, alpha_1y = alpha_a, winrate_1y = winrate, beta_1y = beta, skewness_1y = skewness, kurtosis_1y = kurtosis, worstmonth_1y = wrst_month, maxdrawdown_1y = drawdown FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_2y = std_dev_a, downsidedev_2y = ds_dev_a, alpha_2y = alpha_a, winrate_2y = winrate, beta_2y = beta, skewness_2y = skewness, kurtosis_2y = kurtosis, worstmonth_2y = wrst_month, maxdrawdown_2y = drawdown FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_3y = std_dev_a, downsidedev_3y = ds_dev_a, alpha_3y = alpha_a, winrate_3y = winrate, beta_3y = beta, skewness_3y = skewness, kurtosis_3y = kurtosis, worstmonth_3y = wrst_month, maxdrawdown_3y = drawdown FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_4y = std_dev_a, downsidedev_4y = ds_dev_a, alpha_4y = alpha_a, winrate_4y = winrate, beta_4y = beta, skewness_4y = skewness, kurtosis_4y = kurtosis, worstmonth_4y = wrst_month, maxdrawdown_4y = drawdown FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_5y = std_dev_a, downsidedev_5y = ds_dev_a, alpha_5y = alpha_a, winrate_5y = winrate, beta_5y = beta, skewness_5y = skewness, kurtosis_5y = kurtosis, worstmonth_5y = wrst_month, maxdrawdown_5y = drawdown FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_10y = std_dev_a, downsidedev_10y = ds_dev_a, alpha_10y = alpha_a, winrate_10y = winrate, beta_10y = beta, skewness_10y = skewness, kurtosis_10y = kurtosis, worstmonth_10y = wrst_month, maxdrawdown_10y = drawdown FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']); UPDATE t SET stddev_ytd = std_dev_a, downsidedev_ytd = ds_dev_a, alpha_ytd = alpha_a, winrate_ytd = winrate, beta_ytd = beta, skewness_ytd = skewness, kurtosis_ytd = kurtosis, worstmonth_ytd = wrst_month, maxdrawdown_ytd = drawdown // mfdb中的真实字段名是 ytd_maxdrawdown FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']); UPDATE t SET stddev_incep = std_dev_a, downsidedev_incep = ds_dev_a, alpha_incep = alpha_a, winrate_incep = winrate, beta_incep = beta, skewness_incep = skewness, kurtosis_incep = kurtosis, worstmonth_incep = wrst_month, maxdrawdown_incep = drawdown FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']); 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) { t = null; if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return; if(isToMySQL) { t = SELECT entity_id, end_date, sharpe_a AS sharperatio_6m, sortino_a AS sortinoratio_6m, treynor AS treynorratio_6m, jensen_a AS jensen_6m, calmar AS calmarratio_6m, omega AS omegaratio_6m, kappa AS kapparatio_6m FROM indicators['PBI-6M'] AS ind INNER JOIN entity_info fi ON ind.entity_id = fi.entity_id WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录 UPDATE t SET sharperatio_1y = sharpe_a, sortinoratio_1y = sortino_a, treynorratio_1y = treynor, jensen_1y = jensen_a, calmarratio_1y = calmar, omegaratio_1y = omega, kapparatio_1y = kappa FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_2y = sharpe_a, sortinoratio_2y = sortino_a, treynorratio_2y = treynor, jensen_2y = jensen_a, calmarratio_2y = calmar, omegaratio_2y = omega, kapparatio_2y = kappa FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_3y = sharpe_a, sortinoratio_3y = sortino_a, treynorratio_3y = treynor, jensen_3y = jensen_a, calmarratio_3y = calmar, omegaratio_3y = omega, kapparatio_3y = kappa FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_4y = sharpe_a, sortinoratio_4y = sortino_a, treynorratio_4y = treynor, jensen_4y = jensen_a, calmarratio_4y = calmar, omegaratio_4y = omega, kapparatio_4y = kappa FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_5y = sharpe_a, sortinoratio_5y = sortino_a, treynorratio_5y = treynor, jensen_5y = jensen_a, calmarratio_5y = calmar, omegaratio_5y = omega, kapparatio_5y = kappa FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_10y = sharpe_a, sortinoratio_10y = sortino_a, treynorratio_10y = treynor, jensen_10y = jensen_a, calmarratio_10y = calmar, omegaratio_10y = omega, kapparatio_10y = kappa FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_ytd = sharpe_a, sortinoratio_ytd = sortino_a, treynorratio_ytd = treynor, jensen_ytd = jensen_a, calmarratio_ytd = calmar, omegaratio_ytd = omega, kapparatio_ytd = kappa FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']); UPDATE t SET sharperatio_incep = sharpe_a, sortinoratio_incep = sortino_a, treynorratio_incep = treynor, jensen_incep = jensen_a, calmarratio_incep = calmar, omegaratio_incep = omega, kapparatio_incep = kappa FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']); INSERT INTO entity_riskadjret_stats SELECT * FROM t; } else { } } /* * 按照 XXX_indicator 表结构准备数据记录 * * */ def generate_entity_indicator(entity_info, indicators, isToMySQL, mutable entity_indicator) { t = null; if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return; if(isToMySQL) { t = SELECT entity_id, end_date, info_a AS info_ratio_6m, m2_a AS m2_6m, track_error_a AS tracking_error_6m FROM indicators['PBI-6M'] AS ind INNER JOIN entity_info fi ON ind.entity_id = fi.entity_id WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录 UPDATE t SET info_ratio_1y = info_a, m2_1y = m2_a, tracking_error_1y = track_error_a FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_2y = info_a, m2_2y = m2_a, tracking_error_2y = track_error_a, var_2y = var, cvar_2y = cvar FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_3y = info_a, m2_3y = m2_a, tracking_error_3y = track_error_a, var_3y = var, cvar_3y = cvar FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_4y = info_a, m2_4y = m2_a, tracking_error_4y = track_error_a, var_4y = var, cvar_4y = cvar FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_5y = info_a, m2_5y = m2_a, tracking_error_5y = track_error_a, var_5y = var, cvar_5y = cvar FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_10y = info_a, m2_10y = m2_a, tracking_error_10y = track_error_a, var_10y = var, cvar_10y = cvar FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_ytd = info_a, m2_ytd = m2_a, tracking_error_ytd = track_error_a FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']); UPDATE t SET info_ratio_incep = info_a, m2_incep = m2_a, tracking_error_incep = track_error_a, var_incep = var, cvar_incep = cvar FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']); INSERT INTO entity_indicator SELECT * FROM t; } else { } } /* * 按照 XXX_style_stats 表结构准备数据记录 * * */ def generate_entity_style_stats(entity_info, indicators, isToMySQL, mutable entity_style_stats) { t = null; if(indicators['PBI-6M'].isVoid() || indicators['PBI-6M'].size() == 0) return; if(isToMySQL) { t = SELECT entity_id, end_date, upside_capture_ret AS upsidecapture_ret_6m, downside_capture_ret AS downsidecapture_ret_6m, upside_capture_ratio AS upsidecapture_ratio_6m, downside_capture_ratio AS downsidecapture_ratio_6m FROM indicators['PBI-6M'] AS ind INNER JOIN entity_info fi ON ind.entity_id = fi.entity_id WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录 UPDATE t SET upsidecapture_ret_1y = upside_capture_ret, downsidecapture_ret_1y = downside_capture_ret, upsidecapture_ratio_1y = upside_capture_ratio, downsidecapture_ratio_1y = downside_capture_ratio FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_2y = upside_capture_ret, downsidecapture_ret_2y = downside_capture_ret, upsidecapture_ratio_2y = upside_capture_ratio, downsidecapture_ratio_2y = downside_capture_ratio FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_3y = upside_capture_ret, downsidecapture_ret_3y = downside_capture_ret, upsidecapture_ratio_3y = upside_capture_ratio, downsidecapture_ratio_3y = downside_capture_ratio FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_4y = upside_capture_ret, downsidecapture_ret_4y = downside_capture_ret, upsidecapture_ratio_4y = upside_capture_ratio, downsidecapture_ratio_4y = downside_capture_ratio FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_5y = upside_capture_ret, downsidecapture_ret_5y = downside_capture_ret, upsidecapture_ratio_5y = upside_capture_ratio, downsidecapture_ratio_5y = downside_capture_ratio FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_10y = upside_capture_ret, downsidecapture_ret_10y = downside_capture_ret, upsidecapture_ratio_10y = upside_capture_ratio, downsidecapture_ratio_10y = downside_capture_ratio FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_ytd = upside_capture_ret, downsidecapture_ret_ytd = downside_capture_ret, upsidecapture_ratio_ytd = upside_capture_ratio, downsidecapture_ratio_ytd = downside_capture_ratio FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']); UPDATE t SET upsidecapture_ret_incep = upside_capture_ret, downsidecapture_ret_incep = downside_capture_ret, upsidecapture_ratio_incep = upside_capture_ratio, downsidecapture_ratio_incep = downside_capture_ratio FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']); INSERT INTO entity_style_stats SELECT * FROM t; } else { } } /* * 按照 XXX_bfi_bm_indicator 表结构准备数据记录 * * TODO: why we need isToMySQL here? * 其它的指标恐怕也要按这个改，因为私募可能会有近6月没有数据但近2年之类的周期有数据的情况！ */ def generate_entity_bfi_indicator(entity_info, indicators, isToMySQL, mutable entity_bfi_indicator) { t = null; 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']; // 标准指标中不被当前表覆盖的数据点 if(isToMySQL) { t = lj( lj( lj( lj( lj( lj( lj( lj( lj(entity_info, indicators['BFI-6M'] AS t_6m, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-1Y'] AS t_1y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-2Y'] AS t_2y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-3Y'] AS t_3y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-4Y'] AS t_4y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-5Y'] AS t_5y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-10Y'] AS t_10y, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-YTD'] AS t_ytd, ['entity_id', 'benchmark_id', 'end_date']).dropColumns!(v_cols_useless), indicators['BFI-INCEP'] AS t_incep, ['entity_id', 'benchmark_id', 'end_date']).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'); t.dropColumns!(['inception_date', 'ini_value']).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 { } }