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; } /* * 返回标准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']; 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) * * * Example: get_year_week(2024.10.27); * get_year_week(2024.11.01); * */ def get_year_week(date) { // 当12月31日是周四、五、六时，该周为第52周，所以次年前几天有可能是上一年的第52，53周 return iif(date.weekOfYear() >= 52 && date.monthOfYear() == 1, date.year()-1, date.year())$STRING + (date.weekOfYear()$STRING).lpad(2, "0"); }