module fundit::rankingCalculator use fundit::sqlUtilities use fundit::dataPuller use fundit::dataSaver /* * 计算收益率排名 * * TODO: 整合入 gen_ranking_sql */ def cal_ret_ranking(entity_type, entity_info, end_date, isFromMySQL) { table_desc = get_performance_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); tb_strategy = get_strategy_list(); tb_substrategy = get_substrategy_list(); t = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL AND (en.entity_id LIKE 'MF%' OR en.entity_id LIKE 'HF%') // 按照 MySQL 字段建表 t_s = create_entity_indicator_ranking(false); t_s_num = create_entity_indicator_ranking_num(false); t_ss = create_entity_indicator_substrategy_ranking(false); t_ss_num = create_entity_indicator_substrategy_ranking_num(false); v_tables = [t_s, t_s_num, t_ss, t_ss_num]; v_tables[0] = SELECT entity_id, end_date, strategy, 1 AS indicator_id, ret_1m AS indicator_1m, ret_1m.rank(false) AS absrank_1m, (ret_1m.rank(false, percent=true)*100).round(0) AS perrank_1m, ret_3m AS indicator_3m, ret_3m.rank(false) AS absrank_3m, (ret_3m.rank(false, percent=true)*100).round(0) AS perrank_3m, ret_6m AS indicator_6m, ret_6m.rank(false) AS absrank_6m, (ret_6m.rank(false, percent=true)*100).round(0) AS perrank_6m, ret_1y AS indicator_1y, ret_1y.rank(false) AS absrank_1y, (ret_1y.rank(false, percent=true)*100).round(0) AS perrank_1y, ret_2y AS indicator_2y, ret_2y.rank(false) AS absrank_2y, (ret_2y.rank(false, percent=true)*100).round(0) AS perrank_2y, ret_3y AS indicator_3y, ret_3y.rank(false) AS absrank_3y, (ret_3y.rank(false, percent=true)*100).round(0) AS perrank_3y, ret_5y AS indicator_5y, ret_5y.rank(false) AS absrank_5y, (ret_5y.rank(false, percent=true)*100).round(0) AS perrank_5y, ret_10y AS indicator_10y, ret_10y.rank(false) AS absrank_10y, (ret_10y.rank(false, percent=true)*100).round(0) AS perrank_10y, ret_ytd AS indicator_ytd, ret_ytd.rank(false) AS absrank_ytd, (ret_ytd.rank(false, percent=true)*100).round(0) AS perrank_ytd FROM t CONTEXT BY strategy, end_date; v_tables[1] = SELECT t.end_date, t.strategy, s.raise_type[0], 1 AS indicator_id, ret_1m.mean() AS avg_1m, ret_1m.count() AS avg_1m_cnt, ret_1m.percentile(95) AS perrank_percent_5_1m, ret_1m.percentile(90) AS perrank_percent_10_1m, ret_1m.percentile(75) AS perrank_percent_25_1m, ret_1m.percentile(50) AS perrank_percent_50_1m, ret_1m.percentile(25) AS perrank_percent_75_1m, ret_1m.percentile(10) AS perrank_percent_90_1m, ret_1m.percentile(5) AS perrank_percent_95_1m, ret_1m.max() AS best_1m, ret_1m.min() AS worst_1m, ret_3m.mean() AS avg_3m, ret_3m.count() AS avg_3m_cnt, ret_3m.percentile(95) AS perrank_percent_5_3m, ret_3m.percentile(90) AS perrank_percent_10_3m, ret_3m.percentile(75) AS perrank_percent_25_3m, ret_3m.percentile(50) AS perrank_percent_50_3m, ret_3m.percentile(25) AS perrank_percent_75_3m, ret_3m.percentile(10) AS perrank_percent_90_3m, ret_3m.percentile(5) AS perrank_percent_95_3m, ret_3m.max() AS best_3m, ret_3m.min() AS worst_3m, ret_6m.mean() AS avg_6m, ret_6m.count() AS avg_6m_cnt, ret_6m.percentile(95) AS perrank_percent_5_6m, ret_6m.percentile(90) AS perrank_percent_10_6m, ret_6m.percentile(75) AS perrank_percent_25_6m, ret_6m.percentile(50) AS perrank_percent_50_6m, ret_6m.percentile(25) AS perrank_percent_75_6m, ret_6m.percentile(10) AS perrank_percent_90_6m, ret_6m.percentile(5) AS perrank_percent_95_6m, ret_6m.max() AS best_6m, ret_6m.min() AS worst_6m, ret_1y.mean() AS avg_1y, ret_1y.count() AS avg_1y_cnt, ret_1y.percentile(95) AS perrank_percent_5_1y, ret_1y.percentile(90) AS perrank_percent_10_1y, ret_1y.percentile(75) AS perrank_percent_25_1y, ret_1y.percentile(50) AS perrank_percent_50_1y, ret_1y.percentile(25) AS perrank_percent_75_1y, ret_1y.percentile(10) AS perrank_percent_90_1y, ret_1y.percentile(5) AS perrank_percent_95_1y, ret_1y.max() AS best_1y, ret_1y.min() AS worst_1y, ret_2y.mean() AS avg_2y, ret_2y.count() AS avg_2y_cnt, ret_2y.percentile(95) AS perrank_percent_5_2y, ret_2y.percentile(90) AS perrank_percent_10_2y, ret_2y.percentile(75) AS perrank_percent_25_2y, ret_2y.percentile(50) AS perrank_percent_50_2y, ret_2y.percentile(25) AS perrank_percent_75_2y, ret_2y.percentile(10) AS perrank_percent_90_2y, ret_2y.percentile(5) AS perrank_percent_95_2y, ret_2y.max() AS best_2y, ret_2y.min() AS worst_2y, ret_3y.mean() AS avg_3y, ret_3y.count() AS avg_3y_cnt, ret_3y.percentile(95) AS perrank_percent_5_3y, ret_3y.percentile(90) AS perrank_percent_10_3y, ret_3y.percentile(75) AS perrank_percent_25_3y, ret_3y.percentile(50) AS perrank_percent_50_3y, ret_3y.percentile(25) AS perrank_percent_75_3y, ret_3y.percentile(10) AS perrank_percent_90_3y, ret_3y.percentile(5) AS perrank_percent_95_3y, ret_3y.max() AS best_3y, ret_3y.min() AS worst_3y, ret_5y.mean() AS avg_5y, ret_5y.count() AS avg_5y_cnt, ret_5y.percentile(95) AS perrank_percent_5_5y, ret_5y.percentile(90) AS perrank_percent_10_5y, ret_5y.percentile(75) AS perrank_percent_25_5y, ret_5y.percentile(50) AS perrank_percent_50_5y, ret_5y.percentile(25) AS perrank_percent_75_5y, ret_5y.percentile(10) AS perrank_percent_90_5y, ret_5y.percentile(5) AS perrank_percent_95_5y, ret_5y.max() AS best_5y, ret_5y.min() AS worst_5y, ret_10y.mean() AS avg_10y, ret_10y.count() AS avg_10y_cnt, ret_10y.percentile(95) AS perrank_percent_5_10y, ret_10y.percentile(90) AS perrank_percent_10_10y, ret_10y.percentile(75) AS perrank_percent_25_10y, ret_10y.percentile(50) AS perrank_percent_50_10y, ret_10y.percentile(25) AS perrank_percent_75_10y, ret_10y.percentile(10) AS perrank_percent_90_10y, ret_10y.percentile(5) AS perrank_percent_95_10y, ret_10y.max() AS best_10y, ret_10y.min() AS worst_10y, ret_ytd.mean() AS avg_ytd, ret_ytd.count() AS avg_ytd_cnt, ret_ytd.percentile(95) AS perrank_percent_5_ytd, ret_ytd.percentile(90) AS perrank_percent_10_ytd, ret_ytd.percentile(75) AS perrank_percent_25_ytd, ret_ytd.percentile(50) AS perrank_percent_50_ytd, ret_ytd.percentile(25) AS perrank_percent_75_ytd, ret_ytd.percentile(10) AS perrank_percent_90_ytd, ret_ytd.percentile(5) AS perrank_percent_95_ytd, ret_ytd.max() AS best_ytd, ret_ytd.min() AS worst_ytd FROM t INNER JOIN tb_strategy s ON t.strategy = s.strategy_id GROUP BY t.strategy, t.end_date; v_tables[2] = SELECT entity_id, end_date, substrategy, 1 AS indicator_id, ret_1m AS indicator_1m, ret_1m.rank(false) AS absrank_1m, (ret_1m.rank(false, percent=true)*100).round(0) AS perrank_1m, ret_3m AS indicator_3m, ret_3m.rank(false) AS absrank_3m, (ret_3m.rank(false, percent=true)*100).round(0) AS perrank_3m, ret_6m AS indicator_6m, ret_6m.rank(false) AS absrank_6m, (ret_6m.rank(false, percent=true)*100).round(0) AS perrank_6m, ret_1y AS indicator_1y, ret_1y.rank(false) AS absrank_1y, (ret_1y.rank(false, percent=true)*100).round(0) AS perrank_1y, ret_2y AS indicator_2y, ret_2y.rank(false) AS absrank_2y, (ret_2y.rank(false, percent=true)*100).round(0) AS perrank_2y, ret_3y AS indicator_3y, ret_3y.rank(false) AS absrank_3y, (ret_3y.rank(false, percent=true)*100).round(0) AS perrank_3y, ret_5y AS indicator_5y, ret_5y.rank(false) AS absrank_5y, (ret_5y.rank(false, percent=true)*100).round(0) AS perrank_5y, ret_10y AS indicator_10y, ret_10y.rank(false) AS absrank_10y, (ret_10y.rank(false, percent=true)*100).round(0) AS perrank_10y, ret_ytd AS indicator_ytd, ret_ytd.rank(false) AS absrank_ytd, (ret_ytd.rank(false, percent=true)*100).round(0) AS perrank_ytd FROM t CONTEXT BY substrategy, end_date; v_tables[3] = SELECT t.end_date, t.substrategy, s.raise_type[0], 1 AS indicator_id, ret_1m.mean() AS avg_1m, ret_1m.count() AS avg_1m_cnt, ret_1m.percentile(95) AS perrank_percent_5_1m, ret_1m.percentile(90) AS perrank_percent_10_1m, ret_1m.percentile(75) AS perrank_percent_25_1m, ret_1m.percentile(50) AS perrank_percent_50_1m, ret_1m.percentile(25) AS perrank_percent_75_1m, ret_1m.percentile(10) AS perrank_percent_90_1m, ret_1m.percentile(5) AS perrank_percent_95_1m, ret_1m.max() AS best_1m, ret_1m.min() AS worst_1m, ret_3m.mean() AS avg_3m, ret_3m.count() AS avg_3m_cnt, ret_3m.percentile(95) AS perrank_percent_5_3m, ret_3m.percentile(90) AS perrank_percent_10_3m, ret_3m.percentile(75) AS perrank_percent_25_3m, ret_3m.percentile(50) AS perrank_percent_50_3m, ret_3m.percentile(25) AS perrank_percent_75_3m, ret_3m.percentile(10) AS perrank_percent_90_3m, ret_3m.percentile(5) AS perrank_percent_95_3m, ret_3m.max() AS best_3m, ret_3m.min() AS worst_3m, ret_6m.mean() AS avg_6m, ret_6m.count() AS avg_6m_cnt, ret_6m.percentile(95) AS perrank_percent_5_6m, ret_6m.percentile(90) AS perrank_percent_10_6m, ret_6m.percentile(75) AS perrank_percent_25_6m, ret_6m.percentile(50) AS perrank_percent_50_6m, ret_6m.percentile(25) AS perrank_percent_75_6m, ret_6m.percentile(10) AS perrank_percent_90_6m, ret_6m.percentile(5) AS perrank_percent_95_6m, ret_6m.max() AS best_6m, ret_6m.min() AS worst_6m, ret_1y.mean() AS avg_1y, ret_1y.count() AS avg_1y_cnt, ret_1y.percentile(95) AS perrank_percent_5_1y, ret_1y.percentile(90) AS perrank_percent_10_1y, ret_1y.percentile(75) AS perrank_percent_25_1y, ret_1y.percentile(50) AS perrank_percent_50_1y, ret_1y.percentile(25) AS perrank_percent_75_1y, ret_1y.percentile(10) AS perrank_percent_90_1y, ret_1y.percentile(5) AS perrank_percent_95_1y, ret_1y.max() AS best_1y, ret_1y.min() AS worst_1y, ret_2y.mean() AS avg_2y, ret_2y.count() AS avg_2y_cnt, ret_2y.percentile(95) AS perrank_percent_5_2y, ret_2y.percentile(90) AS perrank_percent_10_2y, ret_2y.percentile(75) AS perrank_percent_25_2y, ret_2y.percentile(50) AS perrank_percent_50_2y, ret_2y.percentile(25) AS perrank_percent_75_2y, ret_2y.percentile(10) AS perrank_percent_90_2y, ret_2y.percentile(5) AS perrank_percent_95_2y, ret_2y.max() AS best_2y, ret_2y.min() AS worst_2y, ret_3y.mean() AS avg_3y, ret_3y.count() AS avg_3y_cnt, ret_3y.percentile(95) AS perrank_percent_5_3y, ret_3y.percentile(90) AS perrank_percent_10_3y, ret_3y.percentile(75) AS perrank_percent_25_3y, ret_3y.percentile(50) AS perrank_percent_50_3y, ret_3y.percentile(25) AS perrank_percent_75_3y, ret_3y.percentile(10) AS perrank_percent_90_3y, ret_3y.percentile(5) AS perrank_percent_95_3y, ret_3y.max() AS best_3y, ret_3y.min() AS worst_3y, ret_5y.mean() AS avg_5y, ret_5y.count() AS avg_5y_cnt, ret_5y.percentile(95) AS perrank_percent_5_5y, ret_5y.percentile(90) AS perrank_percent_10_5y, ret_5y.percentile(75) AS perrank_percent_25_5y, ret_5y.percentile(50) AS perrank_percent_50_5y, ret_5y.percentile(25) AS perrank_percent_75_5y, ret_5y.percentile(10) AS perrank_percent_90_5y, ret_5y.percentile(5) AS perrank_percent_95_5y, ret_5y.max() AS best_5y, ret_5y.min() AS worst_5y, ret_10y.mean() AS avg_10y, ret_10y.count() AS avg_10y_cnt, ret_10y.percentile(95) AS perrank_percent_5_10y, ret_10y.percentile(90) AS perrank_percent_10_10y, ret_10y.percentile(75) AS perrank_percent_25_10y, ret_10y.percentile(50) AS perrank_percent_50_10y, ret_10y.percentile(25) AS perrank_percent_75_10y, ret_10y.percentile(10) AS perrank_percent_90_10y, ret_10y.percentile(5) AS perrank_percent_95_10y, ret_10y.max() AS best_10y, ret_10y.min() AS worst_10y, ret_ytd.mean() AS avg_ytd, ret_ytd.count() AS avg_ytd_cnt, ret_ytd.percentile(95) AS perrank_percent_5_ytd, ret_ytd.percentile(90) AS perrank_percent_10_ytd, ret_ytd.percentile(75) AS perrank_percent_25_ytd, ret_ytd.percentile(50) AS perrank_percent_50_ytd, ret_ytd.percentile(25) AS perrank_percent_75_ytd, ret_ytd.percentile(10) AS perrank_percent_90_ytd, ret_ytd.percentile(5) AS perrank_percent_95_ytd, ret_ytd.max() AS best_ytd, ret_ytd.min() AS worst_ytd FROM t INNER JOIN tb_substrategy s ON t.substrategy = s.substrategy_id GROUP BY t.substrategy, t.end_date; return v_tables; } /* * 自定义百分位计算 * */ defg perRank(x, is_ASC) { return (100 * x.rank(ascending=is_ASC, percent=true)).round(0); } /* * 动态生成用于排序的SQL脚本 * * @param indicator_name : 指标字段名 * @param indicator_id ：指标ID * @param is_ASC : 是否排正序 * @param ranking_by : 'strategy', 'substrategy', 'factor', 'catavg' * * TODO: bfi & category * */ def gen_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, ranking_by) { // 近1月和近3月排名仅对收益有效，为了满足表结构的要求，需要建立几个”假”字段，并用NULL赋值 t_tmp = table(1000:0, ['indicator_id', 'indicator_1m', 'absrank_1m', 'perrank_1m', 'indicator_3m', 'absrank_3m', 'perrank_3m'], [INT, DOUBLE, INT, INT, DOUBLE, INT, INT]); INSERT INTO t_tmp VALUES (indicator_id, double(NULL), int(NULL), int(NULL), double(NULL), int(NULL), int(NULL)); // 因为 parseExpr 没法将表 data_table 传入，所以用 sql() t_ranking = sql(select = (sqlCol('entity_id'), sqlCol('end_date'), sqlCol(ranking_by), sqlCol('indicator_id'), sqlCol('indicator_1m'), sqlCol('absrank_1m'), sqlCol('perrank_1m'), sqlCol('indicator_3m'), sqlCol('absrank_3m'), sqlCol('perrank_3m'), // 与 MySQL 不同，这里统一把近4年和成立以来的排名去掉 sqlCol(indicator_name + '_6m',,'indicator_6m'), sqlCol(indicator_name + '_6m', rank{, is_ASC}, 'absrank_6m'), sqlCol(indicator_name + '_6m', perRank{, is_ASC}, 'perrank_6m'), sqlCol(indicator_name + '_1y',,'indicator_1y'), sqlCol(indicator_name + '_1y', rank{, is_ASC}, 'absrank_1y'), sqlCol(indicator_name + '_1y', perRank{, is_ASC}, 'perrank_1y'), sqlCol(indicator_name + '_2y',,'indicator_2y'), sqlCol(indicator_name + '_2y', rank{, is_ASC}, 'absrank_2y'), sqlCol(indicator_name + '_2y', perRank{, is_ASC}, 'perrank_2y'), sqlCol(indicator_name + '_3y',,'indicator_3y'), sqlCol(indicator_name + '_3y', rank{, is_ASC}, 'absrank_3y'), sqlCol(indicator_name + '_3y', perRank{, is_ASC}, 'perrank_3y'), sqlCol(indicator_name + '_5y',,'indicator_5y'), sqlCol(indicator_name + '_5y', rank{, is_ASC}, 'absrank_5y'), sqlCol(indicator_name + '_5y', perRank{, is_ASC}, 'perrank_5y'), sqlCol(indicator_name + '_10y',,'indicator_10y'), sqlCol(indicator_name + '_10y', rank{, is_ASC}, 'absrank_10y'), sqlCol(indicator_name + '_10y', perRank{, is_ASC}, 'perrank_10y'), sqlCol(indicator_name + '_ytd',,'indicator_ytd'), sqlCol(indicator_name + '_ytd', rank{, is_ASC}, 'absrank_ytd'), sqlCol(indicator_name + '_ytd', perRank{, is_ASC}, 'perrank_ytd') ), from = cj(data_table, t_tmp), where = <_$ranking_by IS NOT NULL>, groupBy = (sqlCol(ranking_by), sqlCol('end_date')), groupFlag = 0 ).eval(); // context by // 近1月和近3月排名仅对收益有效，为了满足表结构的要求，需要建立几个”假”字段，并用NULL赋值 t_tmp = table(1000:0, ['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'], [INT, DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, INT, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]); INSERT INTO t_tmp VALUES (indicator_id, double(NULL), int(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), int(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL), double(NULL)); t_ranking_num = sql(select = (sqlCol('end_date'), sqlCol(ranking_by), sqlCol('raise_type', mean, 'raise_type'), sqlCol('indicator_id', mean,'indicator_id'), sqlCol('avg_1m', mean, 'avg_1m'), sqlCol('avg_1m_cnt', mean, 'avg_1m_cnt'), sqlCol('perrank_percent_5_1m', mean, 'perrank_percent_5_1m'), sqlCol('perrank_percent_10_1m', mean, 'perrank_percent_10_1m'), sqlCol('perrank_percent_25_1m', mean, 'perrank_percent_25_1m'), sqlCol('perrank_percent_50_1m', mean, 'perrank_percent_50_1m'), sqlCol('perrank_percent_75_1m', mean, 'perrank_percent_75_1m'), sqlCol('perrank_percent_90_1m', mean, 'perrank_percent_90_1m'), sqlCol('perrank_percent_95_1m', mean, 'perrank_percent_95_1m'), sqlCol('best_1m', mean, 'best_1m'), sqlCol('worst_1m', mean, 'worst_1m'), sqlCol('avg_3m', mean, 'avg_3m'), sqlCol('avg_3m_cnt', mean, 'avg_3m_cnt'), sqlCol('perrank_percent_5_3m', mean, 'perrank_percent_5_3m'), sqlCol('perrank_percent_10_3m', mean, 'perrank_percent_10_3m'), sqlCol('perrank_percent_25_3m', mean, 'perrank_percent_25_3m'), sqlCol('perrank_percent_50_3m', mean, 'perrank_percent_50_3m'), sqlCol('perrank_percent_75_3m', mean, 'perrank_percent_75_3m'), sqlCol('perrank_percent_90_3m', mean, 'perrank_percent_90_3m'), sqlCol('perrank_percent_95_3m', mean, 'perrank_percent_95_3m'), sqlCol('best_3m', mean, 'best_3m'), sqlCol('worst_3m', mean, 'worst_3m'), // 与 MySQL 不同，这里统一把近4年和成立以来的排名去掉 sqlCol(indicator_name + '_6m', mean, 'avg_6m'), sqlCol(indicator_name + '_6m', count, 'avg_6m_cnt'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_6m'), sqlCol(indicator_name + '_6m', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_6m'), sqlCol(indicator_name + '_6m', iif(is_ASC, min, max), 'best_6m'), sqlCol(indicator_name + '_6m', iif(is_ASC, max, min), 'worst_6m'), sqlCol(indicator_name + '_1y', mean, 'avg_1y'), sqlCol(indicator_name + '_1y', count, 'avg_1y_cnt'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_1y'), sqlCol(indicator_name + '_1y', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_1y'), sqlCol(indicator_name + '_1y', iif(is_ASC, min, max), 'best_1y'), sqlCol(indicator_name + '_1y', iif(is_ASC, max, min), 'worst_1y'), sqlCol(indicator_name + '_2y', mean, 'avg_2y'), sqlCol(indicator_name + '_2y', count, 'avg_2y_cnt'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_2y'), sqlCol(indicator_name + '_2y', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_2y'), sqlCol(indicator_name + '_2y', iif(is_ASC, min, max), 'best_2y'), sqlCol(indicator_name + '_2y', iif(is_ASC, max, min), 'worst_2y'), sqlCol(indicator_name + '_3y', mean, 'avg_3y'), sqlCol(indicator_name + '_3y', count, 'avg_3y_cnt'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_3y'), sqlCol(indicator_name + '_3y', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_3y'), sqlCol(indicator_name + '_3y', iif(is_ASC, min, max), 'best_3y'), sqlCol(indicator_name + '_3y', iif(is_ASC, max, min), 'worst_3y'), sqlCol(indicator_name + '_5y', mean, 'avg_5y'), sqlCol(indicator_name + '_5y', count, 'avg_5y_cnt'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_5y'), sqlCol(indicator_name + '_5y', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_5y'), sqlCol(indicator_name + '_5y', iif(is_ASC, min, max), 'best_5y'), sqlCol(indicator_name + '_5y', iif(is_ASC, max, min), 'worst_5y'), sqlCol(indicator_name + '_10y', mean, 'avg_10y'), sqlCol(indicator_name + '_10y', count, 'avg_10y_cnt'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_10y'), sqlCol(indicator_name + '_10y', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_10y'), sqlCol(indicator_name + '_10y', iif(is_ASC, min, max), 'best_10y'), sqlCol(indicator_name + '_10y', iif(is_ASC, max, min), 'worst_10y'), sqlCol(indicator_name + '_ytd', mean, 'avg_ytd'), sqlCol(indicator_name + '_ytd', count, 'avg_ytd_cnt'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 5, 95)}, 'perrank_percent_5_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 10, 90)}, 'perrank_percent_10_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 25, 75)}, 'perrank_percent_25_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 50, 50)}, 'perrank_percent_50_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 75, 25)}, 'perrank_percent_75_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 90, 10)}, 'perrank_percent_90_ytd'), sqlCol(indicator_name + '_ytd', percentile{, iif(is_ASC, 95, 5)}, 'perrank_percent_95_ytd'), sqlCol(indicator_name + '_ytd', iif(is_ASC, min, max), 'best_ytd'), sqlCol(indicator_name + '_ytd', iif(is_ASC, max, min), 'worst_ytd') ), from = cj(data_table, t_tmp), where = <_$ranking_by IS NOT NULL>, groupBy = (sqlCol(ranking_by), sqlCol('end_date')), groupFlag = 1).eval(); // group by return t_ranking, t_ranking_num; } /* * 运行排名SQL脚本 * * NOTE: 没有用 parseExpr 来生成动态脚本的原因是数据表无法传入 */ def run_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, mutable v_tables) { tb_strategy_ranking = gen_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, 'strategy')[0]; v_tables[0].tableInsert(tb_strategy_ranking); tb_strategy_ranking_num = gen_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, 'strategy')[1]; v_tables[1].tableInsert(tb_strategy_ranking_num); tb_substrategy_ranking = gen_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, 'substrategy')[0]; v_tables[2].tableInsert(tb_substrategy_ranking); tb_substrategy_ranking_num = gen_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, 'substrategy')[1]; v_tables[3].tableInsert(tb_substrategy_ranking_num); } /* * 计算风险指标排名 * * */ def cal_risk_ranking(entity_type, entity_info, end_date, isFromMySQL) { table_desc = get_risk_stats_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); t = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL; // 按照 MySQL 字段建表 t_s = create_entity_indicator_ranking(false); t_s_num = create_entity_indicator_ranking_num(false); t_ss = create_entity_indicator_substrategy_ranking(false); t_ss_num = create_entity_indicator_substrategy_ranking_num(false); v_ranking_tables = [t_s, t_s_num, t_ss, t_ss_num]; // 最大回撤 run_ranking_sql(t, 'maxdrawdown', 2, true, v_ranking_tables); // 峰度 run_ranking_sql(t, 'kurtosis', 6, true, v_ranking_tables); // 偏度 run_ranking_sql(t, 'skewness', 9, false, v_ranking_tables); // 标准差 run_ranking_sql(t, 'stddev', 10, true, v_ranking_tables); // Alpha run_ranking_sql(t, 'alpha', 11, false, v_ranking_tables); // Beta run_ranking_sql(t, 'beta', 12, false, v_ranking_tables); // 下行标准差 run_ranking_sql(t, 'downsidedev', 21, true, v_ranking_tables); // 月最大回撤 dolphin 未计算 // run_ranking_sql(t, 'maxdrawdown_months', 50, true, v_ranking_tables); // 最大回撤修复月份数 dolphin 未计算 //run_ranking_sql(t, 'maxdrawdown_recoverymonths', 52, true, v_ranking_tables); // 胜率 run_ranking_sql(t, 'winrate', 59, false, v_ranking_tables); return v_ranking_tables; } /* * 计算风险调整收益指标排名 * * */ def cal_risk_adj_return_ranking(entity_type, entity_info, end_date, isFromMySQL) { table_desc = get_riskadjret_stats_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); t = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL; // 按照 MySQL 字段建表 t_s = create_entity_indicator_ranking(false); t_s_num = create_entity_indicator_ranking_num(false); t_ss = create_entity_indicator_substrategy_ranking(false); t_ss_num = create_entity_indicator_substrategy_ranking_num(false); v_ranking_tables = [t_s, t_s_num, t_ss, t_ss_num]; // Kappa ratio run_ranking_sql(t, 'kapparatio', 14, false, v_ranking_tables); // Treynor ratio run_ranking_sql(t, 'treynorratio', 15, false, v_ranking_tables); // Jensen run_ranking_sql(t, 'jensen', 16, false, v_ranking_tables); // Omega ratio run_ranking_sql(t, 'omegaratio', 17, false, v_ranking_tables); // Sharpe ratio run_ranking_sql(t, 'sharperatio', 18, false, v_ranking_tables); // MAR Sortino ratio dolphin 未计算 //run_ranking_sql(t, 'sortinoratio_MAR', 19, false, v_ranking_tables); // Calmar ratio run_ranking_sql(t, 'calmarratio', 40, false, v_ranking_tables); // Sortino ratio run_ranking_sql(t, 'sortinoratio', 58, false, v_ranking_tables); return v_ranking_tables; } /* * 计算杂项指标排名 * * */ def cal_other_indicator_ranking(entity_type, entity_info, end_date, isFromMySQL) { table_desc = get_indicator_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); t = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL; // 按照 MySQL 字段建表 t_s = create_entity_indicator_ranking(false); t_s_num = create_entity_indicator_ranking_num(false); t_ss = create_entity_indicator_substrategy_ranking(false); t_ss_num = create_entity_indicator_substrategy_ranking_num(false); v_ranking_tables = [t_s, t_s_num, t_ss, t_ss_num]; // 风格一致性 dolphin 未计算 //run_ranking_sql(t, 'per_con', 37, false, v_ranking_tables); // Information ratio run_ranking_sql(t, 'info_ratio', 38, false, v_ranking_tables); // Value at Risk run_ranking_sql(t, 'var', 41, true, v_ranking_tables); // Conditional Value at Risk run_ranking_sql(t, 'cvar', 42, true, v_ranking_tables); // SMDD 模型下的 VaR dolphin 未计算 //run_ranking_sql(t, 'smddvar', 43, true, v_ranking_tables); // SMDD 模型下的 CVaR dolphin 未计算 //run_ranking_sql(t, 'smddcvar', 44, true, v_ranking_tables); // SMDD 模型下的 LPM1 dolphin 未计算 //run_ranking_sql(t, 'smdd_lpm1', 45, true, v_ranking_tables); // SMDD 模型下的 LPM2 dolphin 未计算 //run_ranking_sql(t, 'smdd_lpm2', 46, true, v_ranking_tables); // SMDD 模型下的下行风险 dolphin 未计算 //run_ranking_sql(t, 'smdd_downside_dev', 47, true, v_ranking_tables); // 跟踪误差 run_ranking_sql(t, 'tracking_error', 48, true, v_ranking_tables); // M2 run_ranking_sql(t, 'm2', 49, false, v_ranking_tables); return v_ranking_tables; } /* * 将源指标表横表变竖表，以方便排名计算 * * */ def transform_data_for_ranking(entity_type, data_table, ranking_by, indicator_info) { v_trailing = ['6m', '1y', '2y', '3y', '5y', '10y', 'ytd']; // 只有 portfolio_id 是整型，其它的都是字符串 is_id_integer = false; if(entity_type == 'PF') is_id_integer = true; if(ranking_by == 'strategy') tb_ranking = create_entity_indicator_ranking(is_id_integer).rename!(ranking_by, 'category_id'); else if(ranking_by == 'substrategy') tb_ranking = create_entity_indicator_substrategy_ranking(is_id_integer).rename!(ranking_by, 'category_id'); else if(ranking_by == 'factor_id') tb_ranking = NULL; for(indicator in indicator_info) { t = sql(select = (sqlCol('entity_id'), sqlCol('end_date'), sqlCol('category_id'), sqlCol(indicator.name + '_' + v_trailing,, 'indicator_' + v_trailing) ), from = data_table ).eval(); t.join!(table(take(indicator.id, t.size()) AS indicator_id, take(double(NULL), t.size()) AS indicator_1m, take(int(NULL), t.size()) AS absrank_1m, take(int(NULL), t.size()) AS perrank_1m, take(double(NULL), t.size()) AS indicator_3m, take(int(NULL), t.size()) AS absrank_3m, take(int(NULL), t.size()) AS perrank_3m, take(int(NULL), t.size()) AS absrank_6m, take(int(NULL), t.size()) AS perrank_6m, take(int(NULL), t.size()) AS absrank_1y, take(int(NULL), t.size()) AS perrank_1y, take(int(NULL), t.size()) AS absrank_2y, take(int(NULL), t.size()) AS perrank_2y, take(int(NULL), t.size()) AS absrank_3y, take(int(NULL), t.size()) AS perrank_3y, take(int(NULL), t.size()) AS absrank_5y, take(int(NULL), t.size()) AS perrank_5y, take(int(NULL), t.size()) AS absrank_10y, take(int(NULL), t.size()) AS perrank_10y, take(int(NULL), t.size()) AS absrank_ytd, take(int(NULL), t.size()) AS perrank_ytd) ); INSERT INTO tb_ranking SELECT * FROM (sql(select = sqlCol(tb_ranking.colNames()), from = t).eval()); } return tb_ranking; } /* * 将源风险指标表横表变竖表，以方便排名计算 * * TODO: 一直缺 portfolio bfi indicator 计算！mysql 里的 pf_fund_bfi_bm_indicator_ranking 是错的... */ def transform_risk_stats_for_ranking (entity_type, entity_info, end_date, ranking_by, isFromMySQL=true) { table_desc = get_risk_stats_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); data_table = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL data_table.dropColumns!('id'); data_table.rename!(ranking_by, 'category_id'); // 目前SQL排名的指标 v_indicator_name = ['maxdrawdown', 'kurtosis', 'skewness', 'stddev', 'alpha', 'beta', 'downsidedev', 'maxdrawdown_months', 'maxdrawdown_recoverymonths', 'winrate']; v_indicator_id = [2, 6, 9, 10, 11, 12, 21, 50, 52, 59]; v_is_ASC = [true, true, false, true, false, false, true, true, true, false]; t_indicator = table(v_indicator_name AS name, v_indicator_id AS id, v_is_ASC AS is_ASC); tb_ranking = transform_data_for_ranking(entity_type, data_table, ranking_by, t_indicator).rename!('category_id', ranking_by); return tb_ranking; } /* * 将源风险调整指标表横表变竖表，以方便排名计算 * * */ def transform_risk_adj_ret_stats_for_ranking (entity_type, entity_info, end_date, ranking_by, isFromMySQL=true) { table_desc = get_riskadjret_stats_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); data_table = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL data_table.dropColumns!('id'); data_table.rename!(ranking_by, 'category_id'); // 目前SQL排名的指标 v_indicator_name = ['kapparatio', 'treynorratio', 'jensen', 'omegaratio', 'sharperatio', 'sortinoratio_MAR', 'calmarratio', 'sortinoratio']; v_indicator_id = [14, 15, 16, 17, 18, 19, 40, 58]; v_is_ASC = [false, false, false, false, false, false, false, false]; t_indicator = table(v_indicator_name AS name, v_indicator_id AS id, v_is_ASC AS is_ASC); tb_ranking = transform_data_for_ranking(entity_type, data_table, ranking_by, t_indicator).rename!('category_id', ranking_by); return tb_ranking; } /* * 将源杂项风险指标表横表变竖表，以方便排名计算 * * */ def transform_other_indicator_for_ranking (entity_type, entity_info, end_date, ranking_by, isFromMySQL=true) { table_desc = get_indicator_table_description(entity_type); tb_data = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL); sec_id_col = table_desc.sec_id_col[0]; tb_data.rename!(sec_id_col, 'entity_id'); data_table = SELECT * FROM entity_info en INNER JOIN tb_data d ON en.entity_id = d.entity_id WHERE en.strategy IS NOT NULL data_table.dropColumns!('id'); data_table.rename!(ranking_by, 'category_id'); // 目前SQL排名的指标 v_indicator_name = ['per_con', 'info_ratio', 'var', 'cvar', 'smddvar', 'smddcvar', 'smdd_lpm1', 'smdd_lpm2', 'smdd_downside_dev', 'tracking_error', 'm2']; v_indicator_id = [37, 38, 41, 42, 43, 44, 45, 46, 47, 48, 49]; v_is_ASC = [false, false, true, true, true, true, true, true, true, true, false]; t_indicator = table(v_indicator_name AS name, v_indicator_id AS id, v_is_ASC AS is_ASC); tb_ranking = transform_data_for_ranking(entity_type, data_table, ranking_by, t_indicator).rename!('category_id', ranking_by); return tb_ranking; } /* * * 参考某指定类排名，计算相对排名 * * @param benchmark_ranking : 被参考的排名表，如公募混合基金 * @param entity_ranking
: 被计算的指标表，排名被填充在原表中 * @param isFromMySQL * * * Example: cal_relative_ranking(get_fund_indicator_ranking(NULL, 2024.09M, 102, true), * transform_risk_stats_for_ranking('PF', get_entity_info('PF', NULL), 2024.09M, true), * true); */ def cal_relative_ranking(benchmark_ranking, mutable entity_ranking, isFromMySQL=true) { v_trailing = ['1m', '3m', '6m', '1y', '2y', '3y', '5y', '10y', 'ytd']; for(tr in v_trailing) { indicator_val_col = 'indicator_' + tr; // 乘上100,000 是为了满足 window join 的字段必须是INT或DURATION tb_tmp = sql(select = (sqlCol(['entity_id', 'end_date', 'category_id', 'indicator_id']), sqlColAlias(makeCall(round, binaryExpr(sqlCol(indicator_val_col), 1000000, *), 0), indicator_val_col + '_int')), from = entity_ranking, where = < _$indicator_val_col is not null >, orderBy = sqlCol(['end_date', 'category_id', 'indicator_id', indicator_val_col]) ).eval(); tb_tmp2 = sql(select = (sqlCol(['end_date', 'category_id', 'indicator_id']), sqlColAlias(makeCall(round, binaryExpr(sqlCol(indicator_val_col), 1000000, *), 0), indicator_val_col + '_int'), sqlCol('absrank_' + tr), sqlCol('perrank_' + tr) ), from = benchmark_ranking, where = < _$indicator_val_col is not null >, orderBy = sqlCol(['end_date', 'category_id', 'indicator_id', indicator_val_col]) ).eval(); absrank_col = 'absrank_' + tr; perrank_col = 'perrank_' + tr; // 用 pwj 来找最接近的排名 tb_tmp_ranking = sql(select = (sqlCol(['entity_id', 'end_date', 'category_id', 'indicator_id']), sqlCol(indicator_val_col + '_int'), sqlCol(['absrank_max', 'perrank_max'])), from = pwj(tb_tmp, tb_tmp2, window = 0:1, aggs = [, ], matchingCols = ['end_date', 'category_id', 'indicator_id', indicator_val_col + '_int']) ).eval(); // 计算的结果填入排名表 sqlUpdate(table = entity_ranking, updates = [, ], from = ).eval(); } } /* * 排名数据入库 * * @param ranking_tables : 包含4个数据表的向量，分别是一级策略排名，一级策略排名阈值，二级策略排名，二级策略排名阈值 */ def save_ranking_tables(entity_type, ranking_tables) { if(ranking_tables.isVoid()) return; source_table = ''; target_table = ''; if(entity_type IN ['MF', 'HF']) { entity_id_col = 'fund_id'; source_table = 'raw_db.pf_fund_indicator_ranking'; target_table = 'raw_db.pf_fund_indicator_ranking' } ranking_tables[0].rename!('entity_id', entity_id_col); save_and_sync(ranking_tables[0], source_table, target_table); save_and_sync(ranking_tables[1], source_table + '_num', target_table + '_num'); ranking_tables[2].rename!('entity_id', entity_id_col); save_and_sync(ranking_tables[2], source_table.replace!('_ranking', '_substrategy_ranking'), target_table.replace!('_ranking', '_substrategy_ranking')); save_and_sync(ranking_tables[3], source_table + '_num', target_table + '_num'); } /* * 参考排名数据入库 * * @param ranking_tables
: */ def save_relative_ranking_table(entity_type, ranking_table, ranking_by) { if(ranking_table.isVoid()) return; source_table = ''; target_table = ''; if(entity_type == 'PF') { entity_id_col = 'portfolio_id'; if(ranking_by == 'strategy') { source_table = 'raw_db.pf_portfolio_indicator_ranking'; target_table = 'raw_db.pf_portfolio_indicator_ranking'; } else if(ranking_by == 'substrategy') { source_table = 'raw_db.pf_portfolio_indicator_substrategy_ranking'; target_table = 'raw_db.pf_portfolio_indicator_substrategy_ranking'; } else if(ranking_by == 'factor_id') { source_table = 'raw_db.pf_portfolio_bfi_bm_indicator_ranking'; target_table = 'raw_db.pf_portfolio_bfi_bm_indicator_ranking'; } } else if(entity_type == 'CF') { entity_id_col = 'fund_id'; source_table = 'raw_db.pf_cus_fund_indicator_ranking'; target_table = 'raw_db.pf_cus_fund_indicator_ranking' } save_and_sync(ranking_table, source_table, target_table); }