DolphinDB/modules/sqlUtilities.dos

module fundit::sqlUtilities

 
/*
 *  MySQL dev server 连接，使用前应确保 loadPlugin("ODBC")已经被运行过
 *
 *  Create  20240711  使用ODBC连接MySQL数据库                                     Joey
 *  
 */
def connect_mysql(user='pf_user') {

    // 阿里云的mysql被魔改过，当前DolphinDB无法支持MySQL插件，只能用ODBC
    // loadPlugin("ODBC")

    // conn = odbc::connect("Driver={MySQL ODBC 9.0 UNICODE Driver};Server=funditdb-dev.mysql.rds.aliyuncs.com;Database=mfdb;User=pf_user;Password=MzBlMDA0OG", "MySQL")

    // 使用Windows的ODBC数据源事先设置号的连接
    // conn = odbc::connect("Dsn=FunditDB-mfdb")
    s = "Dsn=FunditDB-dev-" + user;
    conn = odbc::connect(s);

//    t = odbc::query(conn, "SELECT * FROM pfdb.pf_portfolio_nav LIMIT 100")

    return conn;
}

/*
 *  取本地数据库
 *
 *  get_local_database("fundit", "mfdb")
 */
def get_local_database(server_name, db_name) {

    db = database(directory="dfs://" + server_name + "/" + db_name + "/");
    
    return db;
}

/*
 * 读本地dolphindb数据表
 *
 * load_table_from_local("fundit", mfdb.fund_performance")
 */
def load_table_from_local(server_name, table_name) {

    db = get_local_database(server_name, table_name.split(".")[0])
    
    return loadTable(db, table_name.split(".")[1])

}

/*
 *   未知形态的id转为MySQL需要的的逗号分隔字符串
 * 
 *   Example: ids_to_string("'a','b','c'");
 *            ids_to_string(['a', NULL, 'c']);
 *            ids_to_string([1,2,3]);
 *            ids_to_string(12);
 *            ids_to_string('').isNull();
 */
def ids_to_string(ids) {

    s_ids = '';

    if(ids.isVoid()) return s_ids;

    // 输入的 ids 是字符串标量
    if (ids.form() == 0) {
        s_ids = (ids$STRING).trim();
    // 输入的 ids 是字符串向量
    } else if(ids.form() == 1) {
      if(ids.type() == 4) // INTEGER
        s_ids = ids.concat(",").trim();
      else // STRING
        s_ids = "'" + ids.concat("','").trim() + "'";
    // 缺省返回空
    } else {
    	s_ids = NULL;
    }

    return s_ids;
}

/*
 *  【初始化数据专用】 返回初始化数据时的各类常数
 * 
 *   
 */
def get_ini_data_const() {

    d = dict(STRING, ANY);

    d['date'] = 1989.01.01;

	return d;
}

/*
 *   返回ID的规律(前缀)
 * 
 */
def get_entity_id_info() {

	tmp = table(10:0, ['entity_type', 'prefix'], [STRING, STRING]);

    // 公募，私募，私有基金，股票，市场指数，图译指数，图译因子，人物，公司
	INSERT INTO tmp VALUES (`MF`HF`CF`EQ`MI`FI`CI`FA`PL`CO, `MF`HF`CF`EQ`IN`IN`CI`FA`PL`CO);

	return tmp;
}

/*
 *   【RETIRED】返回标准RBSA的几组指数集
 *   
 */
def get_rbsa_index() {

	d = dict(STRING, ANY);

	d['AS0000005Q'] = ['IN00000008', 'IN00000077','IN0000007G', 'IN0000009M'];
	d['BondType'] = ['IN0000007A', 'IN0000007G','IN0000008J', 'IN000002CM'];
	d['Cap3Style'] = ['FA00000WKG', 'FA00000WKH','IN0000007G'];
	d['CNI7Style'] = ['IN0000000S', 'IN0000000T','IN0000000U', 'IN0000000V', 'IN0000000W', 'IN0000000X', 'IN0000007G'];
	d['CSI11'] = ['IN0000000Y', 'IN0000000Z','IN00000010', 'IN00000011', 'IN00000012', 'IN00000013', 'IN00000014', 'IN00000015', 'IN00000016', 'IN00000017', 'IN0000007G'];
	d['CSI5'] = ['FA00000VML', 'FA00000VMM','FA00000VMN', 'FA00000VMO', 'IN0000007G'];
	d['Large4Assets'] = ['IN00000008', 'IN00000077','IN0000007G', 'IN0000009M'];
	d['GBA7Asset'] = ['IN00000008', 'IN000002EZ', 'IN00000077', 'IN000002F4', 'IN0000009M', 'IN0000007G', 'IN000002HO'];
	d['GBA4Style'] = ['IN00000008', 'IN0000008O', 'IN000002L2', 'IN000002L5', 'IN000002HO'];
	d['GBA4Sector'] = ['FA00000WKI', 'FA00000WKN', 'FA00000WKK', 'FA00000WKL', 'IN000002HO'];

	return d;
}

/*
 *  根据不同类型的主体返回其净值表的表名、字段名和ID前两位特征字符
 */
def get_nav_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'nav_col', 'prefix', 'price_date_col'],
                         [STRING, STRING, STRING, STRING, STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子，组合, 基金经理, 基金公司
    INSERT INTO tmp_universe VALUES (
        ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.nav', 'mfdb.public_nav', 'pfdb.pf_cus_fund_nav', 'mfdb.stock_price', 'mfdb.market_indexes', 'mfdb.indexes_ty_index', 'pfdb.cm_udf_index_nav', 'pfdb.cm_factor_value', 'pfdb.pf_portfolio_nav', 'mfdb.fund_manager_fitted_curve', 'mfdb.company_fitted_curve'],
        ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'index_id', 'index_id', 'index_id', 'factor_id', 'portfolio_id', 'fund_manager_id', 'company_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav'],
        ['nav', 'nav', 'nav', 'nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav'],
        ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '', 'PL', 'CO'],
        ['price_date', 'price_date', 'price_date', 'price_date', 'price_date', 'price_date', 'price_date', 'price_date', 'price_date', 'end_date', 'end_date']);

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}

/*
 *  根据不同类型的主体返回其业绩表的表名、字段名和ID前两位特征字符
 */
def get_performance_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'ret_col', 'prefix'],
                         [STRING, STRING, STRING, STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子，组合
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_performance', 'mfdb.fund_performance', 'pfdb.pf_cus_fund_performance', 'mfdb.stock_performance', 'mfdb.fund_performance', 'mfdb.fund_performance', 'pfdb.cm_udf_index_performance', 'pfdb.cm_factor_performance', 'pfdb.pf_portfolio_performance', 'mfdb.manager_performance', 'mfdb.company_performance'],
        ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'factor_value', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav'],
        ['ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m', 'ret_1m'],
        ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '', 'PL', 'CO']);

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}

/*
 *  根据不同类型的主体返回其业绩表的表名、字段名和ID前两位特征字符
 */
def get_performance_weekly_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'ret_col', 'prefix'],
                         [STRING, STRING, STRING, STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子，组合
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF'],
        ['mfdb.fund_performance_weekly', 'mfdb.fund_performance_weekly', 'pfdb.pf_cus_fund_performance_weekly', 'mfdb.stock_performance_weekly', 'mfdb.fund_performance_weekly', 'mfdb.fund_performance_weekly', 'pfdb.cm_udf_index_performance_weekly', 'pfdb.cm_factor_performance_weekly', 'pfdb.pf_portfolio_performance_weekly'],
        ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
        ['ret_1w', 'ret_1w', 'ret_1w', 'ret_1w', 'ret_1w', 'ret_1w', 'ret_1w', 'ret_1w', 'ret_1w'],
        ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '']);

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}


/*
 *  根据不同类型的主体返回其业绩表的表名、字段名和ID前两位特征字符
 */
def get_latest_performance_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'prefix'],
                         [STRING, STRING, STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子，组合
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF'],
        ['mfdb.fund_latest_nav_performance', 'mfdb.fund_latest_nav_performance', 'pfdb.pf_cus_fund_latest_nav_performance', 'mfdb.stock_latest_nav_performance', 'mfdb.fund_latest_nav_performance', 'mfdb.fund_latest_nav_performance', 'pfdb.cm_udf_index_latest_nav_performance', 'pfdb.pf_factor_latest_nav_performance', 'pfdb.pf_portfolio_latest_nav_performance'],
        ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
        ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '']);

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}

/*
 *  根据不同类型的主体返回其杂项指标的表名、字段名
 * 
 *  Example: get_indicator_table_description('HF');
 */
def get_indicator_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合，基金经理，基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_indicator', 'mfdb.fund_indicator', 'pfdb.pf_cus_fund_indicator', 'mfdb.fund_indicator', 'mfdb.fund_indicator', 'pfdb.cm_udf_index_indicator', 'pfdb.cm_factor_indicator', 'pfdb.pf_portfolio_indicator', 'mfdb.manager_indicator', 'mfdb.company_indicator'],
        ['fund_id', 'fund_id', 'fund_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}

/*
 *  根据不同类型的主体返回其风险指标的表名、字段名
 * 
 *  Example: get_risk_stats_table_description('HF');
 */
def get_risk_stats_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合，基金经理，基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_risk_stats', 'mfdb.fund_risk_stats', 'pfdb.pf_cus_fund_risk_stats', 'mfdb.fund_risk_stats', 'mfdb.fund_risk_stats', 'pfdb.cm_udf_index_risk_stats', 'pfdb.cm_factor_risk_stats', 'pfdb.pf_portfolio_risk_stats', 'mfdb.manager_risk_stats', 'mfdb.company_risk_stats'],
        ['fund_id', 'fund_id', 'fund_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}

/*
 *  根据不同类型的主体返回其风险调整收益指标的表名、字段名
 * 
 *  Example: get_riskadjret_stats_table_description('HF');
 */
def get_riskadjret_stats_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合，基金经理，基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_riskadjret_stats', 'mfdb.fund_riskadjret_stats', 'pfdb.pf_cus_fund_riskadjret_stats', 'mfdb.fund_riskadjret_stats', 'mfdb.fund_riskadjret_stats', 'pfdb.cm_udf_index_riskadjret_stats', 'pfdb.cm_factor_riskadjret_stats', 'pfdb.pf_portfolio_riskadjret_stats', 'mfdb.manager_riskadjret_stats', 'mfdb.company_riskadjret_stats'],
        ['fund_id', 'fund_id', 'fund_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}


/*
 *  根据不同类型的主体返回其杂项指标的表名、字段名
 * 
 *  Example: get_capture_style_table_description('MF');
 */
def get_capture_style_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合，基金经理，基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_style_stats', 'mfdb.fund_style_stats', 'pfdb.pf_cus_fund_style_stats', 'mfdb.fund_style_stats', 'mfdb.fund_style_stats', 'pfdb.cm_udf_index_style_stats', 'pfdb.cm_factor_style_stats', 'pfdb.pf_portfolio_style_stats', 'mfdb.manager_style_stats', 'mfdb.company_style_stats'],
        ['fund_id', 'fund_id', 'fund_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}


/*
 *  根据不同类型的主体返回其晨星指标的表名、字段名
 * 
 *  Example: get_ms_stats_table_description('MF');
 */
def get_ms_stats_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合，基金经理，基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL', 'CO'],
        ['mfdb.fund_ms_stats', 'mfdb.fund_ms_stats', 'pfdb.pf_cus_fund_ms_stats', 'mfdb.fund_ms_stats', 'mfdb.fund_ms_stats', 'pfdb.cm_udf_index_ms_stats', 'pfdb.cm_factor_ms_stats', 'pfdb.pf_portfolio_ms_stats', 'mfdb.manager_ms_stats', 'mfdb.company_ms_stats'],
        ['fund_id', 'fund_id', 'fund_id', 'fund_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}

/*
 *  根据不同类型的主体返回其BFI指标的表名、字段名
 * 
 *  Example: get_bfi_indicator_table_description('PL');
 */
def get_bfi_indicator_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，组合，基金经理
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'PF', 'PL'],
        ['mfdb.fund_ty_bfi_bm_indicator', 'mfdb.fund_ty_bfi_bm_indicator', 'pfdb.pf_portfolio_ty_bfi_bm_indicator', 'mfdb.manager_ty_bfi_bm_indicator'],
        ['fund_id', 'fund_id', 'portfolio_id', 'manager_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);

}

/*
 *  根据不同类型的主体返回其有效BFI因子的表名、字段名
 * 
 *  Example: get_bfi_by_category_group_table_description('HF');
 */

def get_bfi_by_category_group_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，市场指数，图译指数，私有指数，图译因子，组合, ，基金经理
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'MI', 'FI', 'CI', 'FA', 'PF', 'PL'],
        ['pfdb.pf_fund_factor_bfi_by_category_group', 'pfdb.pf_fund_factor_bfi_by_category_group', NULL, NULL, NULL, NULL, NULL, 'pfdb.pf_portfolio_factor_bfi_by_category_group', 'pfdb.pf_manager_factor_bfi_by_category_group'],
        ['fund_id', 'fund_id', NULL, NULL, NULL, NULL, NULL, 'portfolio_id', 'manager_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
}


/*
 *  根据不同类型的主体返回其主策略排名表名、字段名
 * 
 *  Example: get_indicator_ranking_table_description('HF');
 */

def get_indicator_ranking_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，组合, 基金经理, 基金公司
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'CF', 'PF', 'PL', 'CO'],
        ['pfdb.pf_fund_indicator_ranking', 'pfdb.pf_fund_indicator_ranking', 'pf_cus_fund_indicator_ranking', 'pfdb.pf_portfolio_indicator_ranking', 'pfdb.pf_manager_indicator_ranking', 'pfdb.pf_company_indicator_ranking'],
        ['fund_id', 'fund_id', 'fund_id', 'portfolio_id', 'manager_id', 'company_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
}

/*
 *  根据不同类型的主体返回其BFI排名表名、字段名
 * 
 *  Example: get_bfi_bm_indicator_ranking_table_description('HF');
 */

def get_bfi_bm_indicator_ranking_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col'],
                         [STRING, STRING, STRING]);

    // 分别对应：私募，公募，组合, 基金经理
    INSERT INTO tmp_universe VALUES ( 
        ['HF', 'MF', 'PF', 'PL'],
        ['pfdb.pf_fund_bfi_bm_indicator_ranking', 'pfdb.pf_fund_bfi_bm_indicator_ranking', 'pfdb.pf_portfolio_bfi_bm_indicator_ranking', 'pfdb.pf_manager_bfi_bm_indicator_ranking'],
        ['fund_id', 'fund_id', 'portfolio_id', 'manager_id'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
}



/*
 *  Annulized multiple
 */
def get_annulization_multiple(freq) {

  ret = 1;
  
  if (freq == 'd') {
    ret = 252; // We have differences here between Java and DolphinDB, Java uses 365.25 days
  } else if (freq == 'w') {
    ret = 52;
  } else if (freq == 'm') {
    ret = 12;
  } else if (freq == 'q') {
    ret = 4;
  } else if (freq == 's') {
    ret = 2;
  } else if (freq == 'a') {
    ret = 1;
  }
  
  return ret;
}

/*
 *   计算MySQL中常用的 year_week, 周一为一个星期的第一天，相当于 YEARWEEK(date, 1)
 *   
 *   @param date<DATE|VECTOR>
 *   
 *   Example: get_year_week(2023.01.01);
 *            get_year_week([2008.12.31, 2023.01.01]);
 *   
 */
def get_year_week(date) {

	year = array(INT, 0, date.size());

	for(d in date) {
		
		y = d.year();

		// 当12月31日是周四、五、六时，该周为第52周，所以次年前几天有可能是上一年的第52，53周
		if(d.weekOfYear() >= 52 && d.monthOfYear() == 1) {
			y = d.year() - 1;
		// 否则该周为次年的第1周
		} else if(d.weekOfYear() == 1 && d.monthOfYear() == 12) {
			y = d.year() + 1;
		}

		year = year.join(y);
	}
	
	return year$STRING + (date.weekOfYear()$STRING).lpad(2, "0");
}