module fundit::dataSaver use fundit::sqlUtilities /* * 存数据表到mySQL或本地dolphindb，原数据会被替代！ * * 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]) saveTable(db, tb, table_name.split(".")[1]) } } /* * 存私募基金净值到本地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); s_query = "CALL raw_db.sp_sync_table_from_dolphin('" + source_table_name + "_dolphin', '" + target_table_name + "');" conn = connect_mysql('raw_db'); odbc::execute(conn, s_query); conn.close(); } /* * 建表 XXXX_performance */ def create_entity_performance() { 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'], [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() { 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'], [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() { 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'], [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() { 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'], [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() { 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'], [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_performance_weekly */ def create_entity_performance_weekly() { return table(1000:0, ['entity_id', 'year_week', 'end_year', 'week_of_year', 'price_date', 'cumulative_nav', 'ret_1w'], [SYMBOL, STRING, STRING, SHORT, DATE, DOUBLE, DOUBLE]); } /* * 建表 xxx_latest_performance */ def create_entity_latest_performance() { 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'], [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]); } /* * 根据 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); }