DolphinDB/modules/rankingCalculator.dos

module fundit::rankingCalculator

use fundit::sqlUtilities
use fundit::dataPuller
use fundit::dataSaver



def cal_ranking() {
	
}

/*
 *   计算收益率排名
 * 
 * 
 */
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 <STRING>: 指标字段名
 *   @param indicator_id <INT>：指标ID
 *   @param is_ASC <BOOL>: 是否排正序
 *   @param ranking_by <STRING>: 'strategy', 'substrategy', 'factor', 'catavg'
 * 
 */
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脚本
 * 
 * 
 */
def run_ranking_sql(data_table, indicator_name, indicator_id, is_ASC, mutable v_tables) {
/*
    entity_type = 'MF'
    end_date = 2024.09M
    isFromMySQL = true;
    indicator_name = 'alpha'
    indicator_id = 11
    is_ASC = false;
    groupby_field = 'strategy';
    data_table = t;
    v_tables = v_ranking_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;
}

/*
 *  排名数据入库
 * 
 *  @param ranking_tables <VECTOR>: 包含4个数据表的向量，分别是一级策略排名，一级策略排名阈值，二级策略排名，二级策略排名阈值
 */
def save_ranking_tables(ranking_tables) {

    if(ranking_tables.isVoid()) return;

    ranking_tables[0].rename!('entity_id', 'fund_id');
    save_and_sync(ranking_tables[0], 'raw_db.pf_fund_indicator_ranking', 'raw_db.pf_fund_indicator_ranking');

    save_and_sync(ranking_tables[1], 'raw_db.pf_fund_indicator_ranking_num', 'raw_db.pf_fund_indicator_ranking_num');
    
    ranking_tables[2].rename!('entity_id', 'fund_id');
    save_and_sync(ranking_tables[2], 'raw_db.pf_fund_indicator_substrategy_ranking', 'raw_db.pf_fund_indicator_substrategy_ranking');

    save_and_sync(ranking_tables[3], 'raw_db.pf_fund_indicator_substrategy_ranking_num', 'raw_db.pf_fund_indicator_substrategy_ranking_num');
	
}