DolphinDB/modules/rankingCalculator.dos

module fundit::rankingCalculator

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

/*
 *   汇集所有参与排名的指标信息
 * 
 */
def get_indicator_info() {

    ids = [1,
           2, 6, 9, 10, 11, 12, 21, 50, 52, 59,
           14, 15, 16, 17, 18, 19, 40, 58,
           37, 38, 41, 42, 43, 44, 45, 46, 47, 48, 49,
           33, 34, 35, 36,
           66, 53, 54, 55, 56, 57
          ];

    names = ['ret',
             'maxdrawdown', 'kurtosis', 'skewness', 'stddev', 'alpha', 'beta', 'downsidedev', 'maxdrawdown_months', 'maxdrawdown_recoverymonths', 'winrate',
             'kapparatio', 'treynorratio', 'jensen', 'omegaratio', 'sharperatio', 'sortinoratio_MAR', 'calmarratio', 'sortinoratio',
             'per_con', 'info_ratio', 'var', 'cvar', 'smddvar', 'smddcvar', 'smdd_lpm1', 'smdd_lpm2', 'smdd_downside_dev', 'tracking_error', 'm2',
             'upsidecapture_ret', 'downsidecapture_ret', 'upsidecapture_ratio', 'downsidecapture_ratio',
             'stability', 'jc_stddev', 'gzstyle_stddev', 'gzstrategy_stddev', 'zz_stddev', 'zx_stddev'
            ];

    is_ASCs = [false,
               true, true, false, true, false, false, true, true, true, false,
               false, false, false, false, false, false, false, false,
               false, false, true, true, true, true, true, true, true, true, false,
               false, false, false, true,
               true, true, true, true, true, true
              ];

    return table(names AS name, ids AS id, is_ASCs AS is_ASC);

}

/*
 *   计算收益率排名
 * 
 *   TODO: 整合入 gen_ranking_sql
 */
def cal_ret_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	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_id', 'catavg'
 *   
 *   TODO: portfolio, cf, manager, company, 
 *   TODO: bfi & category
 * 
 */
def gen_ranking_sql0(data_table, indicator_table, ranking_by) {

	for(indicator in indicator_table) {

	    // 近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));
	
	    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{, indicator.is_ASC}, 'absrank_6m'),
	                              sqlCol(indicator.name + '_6m', perRank{, indicator.is_ASC}, 'perrank_6m'),
	                              sqlCol(indicator.name + '_1y',,'indicator_1y'),
	                              sqlCol(indicator.name + '_1y', rank{, indicator.is_ASC}, 'absrank_1y'),
	                              sqlCol(indicator.name + '_1y', perRank{, indicator.is_ASC}, 'perrank_1y'),
	                              sqlCol(indicator.name + '_2y',,'indicator_2y'),
	                              sqlCol(indicator.name + '_2y', rank{, indicator.is_ASC}, 'absrank_2y'),
	                              sqlCol(indicator.name + '_2y', perRank{, indicator.is_ASC}, 'perrank_2y'),
	                              sqlCol(indicator.name + '_3y',,'indicator_3y'),
	                              sqlCol(indicator.name + '_3y', rank{, indicator.is_ASC}, 'absrank_3y'),
	                              sqlCol(indicator.name + '_3y', perRank{, indicator.is_ASC}, 'perrank_3y'),
	                              sqlCol(indicator.name + '_5y',,'indicator_5y'),
	                              sqlCol(indicator.name + '_5y', rank{, indicator.is_ASC}, 'absrank_5y'),
	                              sqlCol(indicator.name + '_5y', perRank{, indicator.is_ASC}, 'perrank_5y'),
	                              sqlCol(indicator.name + '_10y',,'indicator_10y'),
	                              sqlCol(indicator.name + '_10y', rank{, indicator.is_ASC}, 'absrank_10y'),
	                              sqlCol(indicator.name + '_10y', perRank{, indicator.is_ASC}, 'perrank_10y'),
	                              sqlCol(indicator.name + '_ytd',,'indicator_ytd'),
	                              sqlCol(indicator.name + '_ytd', rank{, indicator.is_ASC}, 'absrank_ytd'),
	                              sqlCol(indicator.name + '_ytd', perRank{, indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_6m'),
	                                  sqlCol(indicator.name + '_6m', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_6m'),
	                                  sqlCol(indicator.name + '_6m', iif(indicator.is_ASC, min, max), 'best_6m'),
	                                  sqlCol(indicator.name + '_6m', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_1y'),
	                                  sqlCol(indicator.name + '_1y', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_1y'),
	                                  sqlCol(indicator.name + '_1y', iif(indicator.is_ASC, min, max), 'best_1y'),
	                                  sqlCol(indicator.name + '_1y', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_2y'),
	                                  sqlCol(indicator.name + '_2y', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_2y'),
	                                  sqlCol(indicator.name + '_2y', iif(indicator.is_ASC, min, max), 'best_2y'),
	                                  sqlCol(indicator.name + '_2y', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_3y'),
	                                  sqlCol(indicator.name + '_3y', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_3y'),
	                                  sqlCol(indicator.name + '_3y', iif(indicator.is_ASC, min, max), 'best_3y'),
	                                  sqlCol(indicator.name + '_3y', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_5y'),
	                                  sqlCol(indicator.name + '_5y', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_5y'),
	                                  sqlCol(indicator.name + '_5y', iif(indicator.is_ASC, min, max), 'best_5y'),
	                                  sqlCol(indicator.name + '_5y', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_10y'),
	                                  sqlCol(indicator.name + '_10y', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_10y'),
	                                  sqlCol(indicator.name + '_10y', iif(indicator.is_ASC, min, max), 'best_10y'),
	                                  sqlCol(indicator.name + '_10y', iif(indicator.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(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_ytd'),
	                                  sqlCol(indicator.name + '_ytd', percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_ytd'),
	                                  sqlCol(indicator.name + '_ytd', iif(indicator.is_ASC, min, max), 'best_ytd'),
	                                  sqlCol(indicator.name + '_ytd', iif(indicator.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脚本
 * 
 *   @param indicator_name <STRING>: 指标字段名
 *   @param indicator_id <INT>：指标ID
 *   @param is_ASC <BOOL>: 是否排正序
 *   @param ranking_by <STRING>: 'strategy', 'substrategy', 'factor_id', 'catavg'
 *   
 *   TODO: portfolio, cf, manager, company, 
 *   TODO: bfi & category
 * 
 */
def gen_ranking_sql(data_table, indicator_table, ranking_by) {

	for(indicator in indicator_table) {

        // 与 MySQL 不同，这里统一把近4年和成立以来的排名去掉
		if(indicator.id == 1)
			v_trailing = ['1m', '3m', '6m', '1y', '2y', '3y', '5y', '10y', 'ytd'];
		else {
			v_trailing = ['6m', '1y', '2y', '3y', '5y', '10y', 'ytd'];
			v_missing_trailing = ['1m', '3m'];
		}

        // 绝对排名和百分位排名
	    t_ranking = sql(select = (sqlCol(['entity_id', 'end_date', 'category_id']), <indicator.id as indicator_id>,
	                              sqlCol(indicator.name + '_' + v_trailing,, 'indicator_' + v_trailing),
	                              sqlCol(indicator.name + '_' + v_trailing, rank{, indicator.is_ASC}, 'absrank_' + v_trailing),
	                              sqlCol(indicator.name + '_' + v_trailing, perRank{, indicator.is_ASC}, 'perrank_' + v_trailing)
                                 ), 
	                    from = data_table,
	                    where = < category_id IS NOT NULL>,
	                    groupBy = sqlCol(['category_id', 'end_date']),
	                    groupFlag = 0 ).eval(); // context by

	    // 为了满足表结构的要求, 非收益的指标要补上1m和3m的字段，虽然都是NULL
	    if(indicator.id != 1) {

	    	t_tmp = table(1000:0,
	    	              ['indicator_' + v_missing_trailing, 'absrank_' + v_missing_trailing, 'perrank_' + v_missing_trailing].flatten(),
	    	              [take(DOUBLE, v_missing_trailing.size()), take(INT, v_missing_trailing.size()), take(INT, v_missing_trailing.size())].flatten()
	    	             );
	    	
	    	t_ranking = SELECT * FROM cj(t_ranking, t_tmp);
	    }

	    // 平均值、集合数量、各分位的阈值
        t_ranking_num = sql(select =(sqlCol(['end_date', 'category_id']),
                                     sqlCol('raise_type', mean, 'raise_type'), <indicator.id as indicator_id>,
                                     sqlCol(indicator.name + '_' + v_trailing, mean, 'avg_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, count, 'avg_' + v_trailing + '_cnt'),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 5, 95)}, 'perrank_percent_5_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 10, 90)}, 'perrank_percent_10_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 25, 75)}, 'perrank_percent_25_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 50, 50)}, 'perrank_percent_50_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 75, 25)}, 'perrank_percent_75_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 90, 10)}, 'perrank_percent_90_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, percentile{, iif(indicator.is_ASC, 95, 5)}, 'perrank_percent_95_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, iif(indicator.is_ASC, min, max), 'best_' + v_trailing),
                                     sqlCol(indicator.name + '_' + v_trailing, iif(indicator.is_ASC, max, min), 'worst_' + v_trailing)
                                    ),
        	                 from = data_table,
        	                 where = < category_id IS NOT NULL>,
        	                 groupBy = sqlCol(['category_id', 'end_date']),
        	                 groupFlag = 1).eval(); // group by

	    // 为了满足表结构的要求, 非收益的指标要补上1m和3m的字段，虽然都是NULL
	    if(indicator.id != 1) {

	    	t_tmp = table(1000:0,
	    	              ['avg_' + v_missing_trailing, 'avg_' + v_missing_trailing + '_cnt', 'perrank_percent_5_' + v_missing_trailing,
	    	               'perrank_percent_10_' + v_missing_trailing, 'perrank_percent_25_' + v_missing_trailing,
	    	               'perrank_percent_50_' + v_missing_trailing, 'perrank_percent_75_' + v_missing_trailing,
	    	               'perrank_percent_90_' + v_missing_trailing, 'perrank_percent_95_' + v_missing_trailing,
	    	               'best_' + v_missing_trailing, 'worst_' + v_missing_trailing ].flatten(),
	    	              [take(DOUBLE, v_missing_trailing.size()), take(INT, v_missing_trailing.size()), take(DOUBLE, v_missing_trailing.size()),
	    	               take(DOUBLE, v_missing_trailing.size()), take(DOUBLE, v_missing_trailing.size()),
	    	               take(DOUBLE, v_missing_trailing.size()), take(DOUBLE, v_missing_trailing.size()),
	    	               take(DOUBLE, v_missing_trailing.size()), take(DOUBLE, v_missing_trailing.size()),
	    	               take(DOUBLE, v_missing_trailing.size()),take(DOUBLE, v_missing_trailing.size())].flatten()
	    	             );
	    	
	    	t_ranking = SELECT * FROM cj(t_ranking, t_tmp);
	    }
	}

    return t_ranking, t_ranking_num;
}

/*
 *  运行排名SQL脚本
 * 
 *  NOTE: 没有用 parseExpr 来生成动态脚本的原因是数据表无法传入
 */
def run_ranking_sql(data_table, indicator_table, mutable v_tables) {

    tb_strategy_ranking = gen_ranking_sql(data_table, indicator_table, 'strategy')[0];
    v_tables[0].tableInsert(tb_strategy_ranking);

    tb_strategy_ranking_num = gen_ranking_sql(data_table, indicator_table, 'strategy')[1];
    v_tables[1].tableInsert(tb_strategy_ranking_num);

    tb_substrategy_ranking = gen_ranking_sql(data_table, indicator_table, 'substrategy')[0];
    v_tables[2].tableInsert(tb_substrategy_ranking);

    tb_substrategy_ranking_num = gen_ranking_sql(data_table, indicator_table, 'substrategy')[1];
    v_tables[3].tableInsert(tb_substrategy_ranking_num);

}

/*
 *   计算风险指标排名
 * 
 * 
 */
def cal_risk_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	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];

    // 50, 52 dolphin 未计算
    indicator_table = SELECT * FROM get_indicator_info() WHERE id in [2, 6, 9, 10, 11, 12, 21, 59];

    run_ranking_sql(t, indicator_table, v_ranking_tables);

    return v_ranking_tables;
}


/*
 *   计算风险调整收益指标排名
 * 
 * 
 */
def cal_risk_adj_return_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	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];

    // 19 (MAR Sortino ratio) dolphin 未计算
    indicator_table = SELECT * FROM get_indicator_info() WHERE id in [14, 15, 16, 17, 18, 40, 58];

    run_ranking_sql(t, indicator_table, v_ranking_tables);

    return v_ranking_tables;
}


/*
 *   计算杂项指标排名
 * 
 * 
 */
def cal_other_indicator_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	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];

    // 37 (per_con), 43, 44, 45, 46, 47 (smdd模型) dolphin 未计算
    indicator_table = SELECT * FROM get_indicator_info() WHERE id in [38, 41, 42, 48, 49];

    run_ranking_sql(t, indicator_table, v_ranking_tables);

    return v_ranking_tables;
}


/*
 *   计算上下行指标排名
 * 
 * 
 */
def cal_capture_style_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	table_desc = get_capture_style_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];

    indicator_table = SELECT * FROM get_indicator_info() WHERE id in [33, 34, 35, 36];

    run_ranking_sql(t, indicator_table, v_ranking_tables);

    return v_ranking_tables;
}


/*
 *   计算BFI指标排名
 * 
 *   TODO: return
 */
def cal_bfi_indicator_ranking(entity_type, entity_info, end_date, isFromMySQL) {

    // 当前只对基金做排名， 其它类型参考基金排名做相对排名
    if(!(entity_type in ['MF', 'HF'])) return null;

	table_desc = get_bfi_indicator_table_description(entity_type);
	sec_id_col = table_desc.sec_id_col[0];
	tb_data_bfi_indicator = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL);
	tb_data_bfi_indicator.rename!(sec_id_col, 'entity_id');

	table_desc = get_risk_stats_table_description(entity_type);
	sec_id_col = table_desc.sec_id_col[0];
	tb_data_risk_stats = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL);
	tb_data_risk_stats.rename!(sec_id_col, 'entity_id');

	table_desc = get_riskadjret_stats_table_description(entity_type);
	sec_id_col = table_desc.sec_id_col[0];
	tb_data_riskadjret_stats = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL);
	tb_data_riskadjret_stats.rename!(sec_id_col, 'entity_id');
	
	table_desc = get_indicator_table_description(entity_type);
	sec_id_col = table_desc.sec_id_col[0];
	tb_data_indicator_stats = get_monthly_indicator_data(table_desc.table_name[0], end_date, isFromMySQL);
	tb_data_indicator_stats.rename!(sec_id_col, 'entity_id');

	t = SELECT *
        FROM entity_info en 
        INNER JOIN tb_data_bfi_indicator d2 ON en.entity_id = d2.entity_id
        INNER JOIN tb_data_risk_stats d3 ON en.entity_id = d3.entity_id
        INNER JOIN tb_data_riskadjret_stats d4 ON en.entity_id = d4.entity_id
        INNER JOIN tb_data_indicator_stats d5 ON en.entity_id = d5.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];

    // 取消 39, 53, 54, 55, 57, 57
    v_indicator_id = [11, 12, 16, 33, 34, 35, 36, 38, 48, 59,
                      2, 6, 9, 10, 21, 50, 52,
                      14, 15, 16, 17, 18, 19, 58, 21, 40,
                      37, 41, 42, 43, 44, 45, 46, 47, 49
                     ];
    indicator_table = SELECT * FROM get_indicator_info() WHERE id in v_indicator_id;

    run_ranking_sql(t, indicator_table, v_ranking_tables);

    return v_ranking_tables;
}

/*
 *   将源指标表横表变竖表，以方便排名计算
 * 
 * 
 */
def transform_data_for_ranking(entity_type, data_table, ranking_by, indicator_info) {

    // 只有收益需要1m, 3m
    if(indicator_info.size() == 1 && indicator_info[0].id == 1 )
		v_trailing = ['1m', '3m', '6m', '1y', '2y', '3y', '5y', '10y', 'ytd'];
	else
	    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', 'end_date', 'category_id']), <indicator.id as indicator_id>,
                          sqlCol(indicator.name + '_' + v_trailing,, 'indicator_' + v_trailing)
                         ),
                from = data_table
        	   ).eval();

        if(indicator_info.size() > 1 || indicator_info[0].id <> 1 )
        {
	        t.join!(table(take(double(NULL), t.size()) AS indicator_1m,
                          take(double(NULL), t.size()) AS indicator_3m) );
        }

        t.join!(table( take(int(NULL), t.size()) AS absrank_1m,
                       take(int(NULL), t.size()) AS perrank_1m,
                       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;
}


/*
 *   将源风险指标表横表变竖表，以方便排名计算
 * 
 *   
 */
def transform_return_for_ranking (entity_type, entity_info, end_date, ranking_by, isFromMySQL=true) {

	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');

	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排名的指标还包含39：年化收益，与收益没有什么差别所以被取消
    v_indicator_name = ['ret'];
    v_indicator_id = [1];
    v_is_ASC = [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;
}


/*
 *   将源风险指标表横表变竖表，以方便排名计算
 * 
 *   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;
}


/*
 *   将源杂项风险指标表横表变竖表，以方便排名计算
 * 
 * 
 */
def transform_capture_style_for_ranking (entity_type, entity_info, end_date, ranking_by, isFromMySQL=true) {

	table_desc = get_capture_style_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 = ['upsidecapture_ret', 'downsidecapture_ret', 'upsidecapture_ratio', 'downsidecapture_ratio'];
    v_indicator_id = [33, 34, 35, 36];
    v_is_ASC = [false, false, false, true];
    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 <TABLE>: 被参考的排名表，如公募混合基金
 *   @param entity_ranking <TABLE>: 被计算的指标表，排名被填充在原表中
 *   @param isFromMySQL <BOOL>
 *   
 * 
 *   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 = [<max(_$absrank_col) as 'absrank_max'>, <max(_$perrank_col) as 'perrank_max'>],
                                        matchingCols = ['end_date', 'category_id', 'indicator_id', indicator_val_col + '_int'])
        	                ).eval();

        // 计算的结果填入排名表
        sqlUpdate(table = entity_ranking,
        	      updates = [<absrank_max as _$absrank_col>, <perrank_max as _$perrank_col>],
        	      from = <ej(entity_ranking, tb_tmp_ranking, ['entity_id', 'end_date', 'category_id','indicator_id'])>
        	     ).eval();

    }

}


/*
 *  排名数据入库
 * 
 *  @param ranking_tables <VECTOR>: 包含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');
    
    source_table = source_table.strReplace('_ranking', '_substrategy_ranking');
    target_table = target_table.strReplace('_ranking', '_substrategy_ranking');

    ranking_tables[2].rename!('entity_id', entity_id_col);
    save_and_sync(ranking_tables[2], source_table, target_table);

    save_and_sync(ranking_tables[3], source_table + '_num', target_table + '_num');
	
}

/*
 *  参考排名数据入库AND a.indicator_id NOT IN (50, 52, 59, 46)

 * 
 *  @param ranking_tables <TABLE>: 
 */
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);
	
}