returnCalculator.dos 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435
  1. module fundit::returnCalculator
  2. use fundit::fundCalculator
  3. use fundit::dataPuller
  4. /*
  5. * 通用月收益计算
  6. *
  7. * @param entity_info <TABLE>: COLUMN NEED entity_id, price_date, inception_date, ini_value
  8. * @param nav <TABLE>: COLUMN NEED entity_id, price_date, cumulative_nav
  9. *
  10. */
  11. def cal_monthly_returns_by_nav(entity_info, mutable nav) {
  12. tb_rets = null;
  13. if(nav.isVoid() || nav.size() == 0 || entity_info.isVoid() || entity_info.size() == 0 ) return null;
  14. // 所有月末日期和净值
  15. tb_monthly_nav = SELECT entity_id, price_date.month().last() AS end_date, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
  16. FROM nav.sortBy!(['entity_id', 'price_date'], [1, 1])
  17. GROUP BY entity_id, price_date.month();
  18. // 筛掉成立日之前的净值
  19. tb_monthly_nav = SELECT n.entity_id, end_date, price_date, cumulative_nav
  20. FROM tb_monthly_nav n
  21. INNER JOIN entity_info ei ON n.entity_id = ei.entity_id
  22. WHERE n.price_date >= ei.inception_date
  23. ORDER BY n.entity_id, n.end_date, n.price_date;
  24. if(tb_monthly_nav.isVoid() || tb_monthly_nav.size() == 0) { return tb_rets; }
  25. // 计算月收益
  26. tb_rets = SELECT entity_id, end_date, price_date, cumulative_nav, cumulative_nav.ratios() - 1 AS ret
  27. FROM tb_monthly_nav
  28. CONTEXT BY entity_id;
  29. // the records without return calculated but do have nav are still useful for some calculations (e.g. max drawdown)
  30. return ( SELECT * FROM tb_rets WHERE cumulative_nav > 0 );
  31. }
  32. /*
  33. * 根据最新更新的净值计算收益,并与数据库历史收益合并为完整收益, 数据源是MySQL
  34. *
  35. * @param entity_type <STRING>:
  36. * @param entity_info <TABLE>: COLUMN NEED entity_id, inception_date, benchmark_id, ini_value, price_date
  37. * @param start_date <DATETIME>: 净值(datasource='nav')、收益起始日(datasource='perf')、净值更新的最新日期(datasource='mix')
  38. * @param data_source <STRING>: nav(nav table), perf(xxx_performance table), mix(new updated nav + old performance table ret_1m)
  39. *
  40. *
  41. */
  42. def mix_monthly_returns(entity_type, entity_info) {
  43. ret = null;
  44. very_old_day = 1990.01.01;
  45. end_day = today();
  46. s_json = (SELECT entity_id AS sec_id, price_date FROM entity_info).toStdJson();
  47. // 取基金组合在包括各自的某净值日期的前值及之后的所有净值
  48. tb_nav = get_nav_for_return_calculation(entity_type, 'm', s_json);
  49. tb_nav.rename!('sec_id', 'entity_id');
  50. // 计算某净值日期所在月份及之后的所有月收益
  51. ret = cal_monthly_returns_by_nav(entity_info, tb_nav);
  52. // 筛掉引入的前值,这些记录用来计算第一期收益后就不再有用
  53. // 不知道为什么 delete ret from ej(ret, tb_entities, 'entity_id') where ret.price_date < tb_entities.price_date 会报错
  54. ret = SELECT ret.* FROM ej(ret, entity_info, 'entity_id')
  55. WHERE ret.price_date >= entity_info.price_date;
  56. // 取数据库中的所有历史收益
  57. historical_rets = get_monthly_ret(entity_type, entity_info.entity_id, very_old_day, end_day, true);
  58. // MIX 将新NAV计算的收益和数据库中的历史收益合并,相同月份时用新计算的收益代替
  59. INSERT INTO ret
  60. SELECT entity_id, end_date.temporalParse('yyyy-MM'), price_date, nav, ret
  61. FROM historical_rets h
  62. WHERE NOT EXISTS ( SELECT * FROM ret WHERE entity_id = historical_rets.entity_id AND end_date = historical_rets.end_date.temporalParse('yyyy-MM') );
  63. return SELECT * FROM ret ORDER BY entity_id, end_date, price_date;
  64. }
  65. /*
  66. * 根据基金净值序列计算月收益序列(适合提供给指标运算)
  67. *
  68. * Create: 20240907 Joey
  69. * TODO: missing pulling data from local
  70. * TODO: ONLY support month return now
  71. *
  72. * @param entity_type <STRING>: NAV universe, 'HF','MF','PF','EQ'... defined in get_nav_table_description()
  73. * @param fund_ids <STRING VECTOR>: 基金ID
  74. * @param isFromMySQL <BOOL>: 净值来源 1 - 远程MySQL、 0 - 本地 DolphinDB
  75. *
  76. * Example: cal_fund_monthly_returns('HF', "'HF000004KN','HF000103EU','HF00018WXG'", true);
  77. *
  78. */
  79. def cal_fund_monthly_returns(entity_type, fund_ids, isFromMySQL){
  80. tb_rets = null;
  81. // 暂时只支持公募和私募基金
  82. if(entity_type != 'HF' && entity_type != 'MF' ) return tb_rets;
  83. // 用于保证老基金也能取到所有历史净值
  84. very_old_price_date = 1990.01.01;
  85. // 基金基本信息,包括初始净值
  86. tb_fund_info = get_fund_info(fund_ids);
  87. // 基金净值
  88. tb_nav = SELECT * FROM get_nav_by_price_date(entity_type, fund_ids, very_old_price_date, isFromMySQL);
  89. tb_month_end = table(100:0, ['fund_id', 'price_date'], [STRING, DATE]);
  90. // 填充好各基金有效期内所有月份的最后一天
  91. for( f in tb_fund_info )
  92. {
  93. INSERT INTO tb_month_end SELECT fund_id, price_date FROM table(f.fund_id.take(1) AS fund_id).cj(table(temporalSeq(f.inception_date, today(), 'M') AS price_date)) ;
  94. }
  95. UPDATE tb_month_end SET end_date = price_date.month();
  96. tb_monthly_nav = SELECT fund_id, monthEnd(price_date).month().last() AS end_date, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
  97. FROM tb_nav
  98. GROUP BY fund_id, monthEnd(price_date);
  99. // 完整月末日期的净值序列(包括缺失数据为NULL)
  100. tb_monthly_nav = SELECT me.fund_id, me.end_date, n.price_date, n.cumulative_nav
  101. FROM tb_month_end me
  102. LEFT JOIN tb_monthly_nav n ON me.fund_id = n.fund_id AND me.end_date = n.end_date
  103. ORDER BY me.fund_id, me.end_date;
  104. // 补一下成立日的初始净值
  105. // NOTE: DolphinDB 遇见 EXISTS 语句时,似乎主表的 alias 失效,只好用全名
  106. INSERT INTO tb_monthly_nav
  107. SELECT fund_id, inception_date.month(), inception_date, ifNull(ini_value, 1)
  108. FROM tb_fund_info fi
  109. WHERE NOT EXISTS ( SELECT * FROM tb_monthly_nav n WHERE fund_id = tb_fund_info.fund_id AND n.price_date = tb_fund_info.inception_date);
  110. if(tb_monthly_nav.isVoid() || tb_monthly_nav.size() == 0) { return tb_rets; }
  111. // 算 ratios 之前先把时间顺序排好
  112. tb_monthly_nav.sortBy!(['fund_id', 'end_date', 'price_date'], [1, 1, 1]);
  113. // 计算月收益
  114. tb_rets = SELECT fund_id, end_date, price_date, cumulative_nav, cumulative_nav.ratios() - 1 AS ret
  115. FROM tb_monthly_nav
  116. CONTEXT BY fund_id;
  117. // the records without return calculated but do have nav are still useful for some calculations
  118. return ( SELECT * FROM tb_rets WHERE cumulative_nav > 0 );
  119. }
  120. /*
  121. * 月末 fund_performance 表计算
  122. *
  123. * @param fund_ids <STRING>: 逗号分隔的ID
  124. * @param end_date <STRING>: YYYY-MM
  125. *
  126. * Example: cal_fund_performance("'HF000004KN','HF00018WXG','HF000103EU'", '2024-06', true);
  127. */
  128. def cal_fund_performance(fund_ids, month_end) {
  129. // 获取必要的基金月度净值
  130. tb_nav = get_nav_for_hedge_fund_performance(fund_ids, month_end);
  131. tb_rets = SELECT fund_id, price_date.month() AS end_date, price_date, cumulative_nav,
  132. cumulative_nav \ nav_1m - 1 AS ret_1m,
  133. cumulative_nav \ nav_3m - 1 AS ret_3m,
  134. cumulative_nav \ nav_6m - 1 AS ret_6m,
  135. cumulative_nav \ nav_1y - 1 AS ret_1y,
  136. cumulative_nav \ nav_2y - 1 AS ret_2y,
  137. cumulative_nav \ nav_3y - 1 AS ret_3y,
  138. cumulative_nav \ nav_4y - 1 AS ret_4y,
  139. cumulative_nav \ nav_5y - 1 AS ret_5y,
  140. cumulative_nav \ nav_10y - 1 AS ret_10y,
  141. cumulative_nav \ nav_ytd - 1 AS ret_ytd,
  142. cumulative_nav \ nav_incep - 1 AS ret_incep, inception_date
  143. FROM tb_nav;
  144. // NOTE: this is to keep consistance with MySQL, even it is NOT complied with GIPS standard
  145. UPDATE tb_rets SET ret_1m_a = (1 + ret_1m).pow(12\1) - 1, ret_3m_a = (1 + ret_3m).pow(12\3) - 1, ret_6m_a = (1 + ret_6m).pow(12\6) - 1,
  146. ret_1y_a= ret_1y, ret_2y_a = (1 + ret_2y).pow(12\24) - 1, ret_3y_a = (1 + ret_3y).pow(12\36) - 1,
  147. ret_4y_a = (1 + ret_4y).pow(12\48) - 1, ret_5y_a = (1 + ret_5y).pow(12\60) - 1, ret_10y_a = (1 + ret_10y).pow(12\120) - 1,
  148. ret_ytd_a = (1 + ret_ytd).pow(12\int(temporalFormat(end_date, 'MM')))-1,
  149. ret_incep_a = (1 + ret_incep).pow(12\(end_date - inception_date.month())) - 1,
  150. ret_incep_a_all = (1 + ret_incep).pow(12\(end_date - inception_date.month()))- 1,
  151. ret_incep_a_gips = iif(end_date - inception_date.month() < 12, ret_incep,
  152. (1 + ret_incep).pow(12\(end_date - inception_date.month()))- 1);
  153. return tb_rets;
  154. }
  155. /*
  156. * 批量计算公募历史基金月度收益(fund_performance)
  157. * NOTE: 任何数据频率快于或等于月度的净值数据都可以用此函数一次性计算完整历史记录。双月频、季频甚至更低频率的基金只能按月计算
  158. *
  159. * cal_mutual_fund_performance("'HF000004KN','HF00018WXG','HF000103EU'", true)
  160. *
  161. */
  162. def cal_mutual_fund_performance(fund_ids, isFromMySQL) {
  163. // 计算月收益
  164. tb_tmp = cal_fund_monthly_returns('MF', fund_ids, isFromMySQL);
  165. tb_rets = SELECT fund_id, end_date, ret_1m,
  166. (1 + ret_1m).mprod(3) - 1 AS ret_3m, (1 + ret_1m).mprod(6) - 1 AS ret_6m, (1 + ret_1m).mprod(12) - 1 AS ret_1y,
  167. (1 + ret_1m).mprod(24) - 1 AS ret_2y, (1 + ret_1m).mprod(36) - 1 AS ret_3y, (1 + ret_1m).mprod(48) - 1 AS ret_4y,
  168. (1 + ret_1m).mprod(60) - 1 AS ret_5y, (1 + ret_1m).mprod(120) - 1 AS ret_10y
  169. FROM tb_tmp
  170. CONTEXT BY fund_id;
  171. // NOTE: this is to keep consistance with MySQL, even it is NOT complied with GIPS standard
  172. UPDATE tb_rets SET ret_1m_a = (1 + ret_1m).pow(12) - 1, ret_3m_a = (1 + ret_3m).pow(4) - 1, ret_6m_a = (1 + ret_6m).pow(2) - 1, ret_1y_a= ret_1y,
  173. ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1, ret_4y_a = (1 + ret_4y).pow(1\4) - 1,
  174. ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1;
  175. // ytd 不会用上面的CONTEXT BY语句实现
  176. tb_ret_ytd = SELECT a.fund_id, a.end_date, a.price_date, a.cumulative_nav, -1 + a.cumulative_nav \ b.cumulative_nav AS ret_ytd,
  177. (a.cumulative_nav \ b.cumulative_nav).pow(12\(a.end_date - b.end_date)) - 1 AS ret_ytd_a
  178. FROM tb_rets a
  179. INNER JOIN tb_rets b ON a.fund_Id = b.fund_id
  180. AND b.end_date = a.price_date.yearEnd().datetimeAdd(-1y).month()
  181. // since inception 不会用上面的CONTEXT BY语句实现
  182. tb_ret_incep = SELECT a.fund_id, a.end_date, a.price_date, cumulative_nav, -1 + cumulative_nav \ ini_value AS ret_incep
  183. FROM tb_rets a
  184. INNER JOIN tb_fund_info fi ON a.fund_id = fi.fund_id
  185. UPDATE tb_ret_incep SET ret_incep_a = (1 + ret_incep).pow(12\(end_date - end_date.first())) - 1 CONTEXT BY fund_Id
  186. UPDATE tb_ret_incep SET ret_incep_a_gips = iif( end_date - end_date.first() < 12, ret_incep, ret_incep_a ), ret_incep_a_all = ret_incep_a CONTEXT BY fund_id
  187. // 只选需要更新的记录
  188. tb_fund_performance = SELECT a.fund_id, a.end_date.datetimeFormat("yyyy-MM") AS end_date, c.price_date, c.cumulative_nav,
  189. a.ret_1m, a.ret_1m_a, a.ret_3m, a.ret_3m_a, a.ret_6m, a.ret_6m_a,
  190. a.ret_1y, a.ret_1y_a, a.ret_2y, a.ret_2y_a, a.ret_3y, a.ret_3y_a,
  191. a.ret_4y, a.ret_4y_a, a.ret_5y, a.ret_5y_a, a.ret_10y, a.ret_10y_a,
  192. b.ret_ytd, b.ret_ytd_a, c.ret_incep, c.ret_incep_a, c.ret_incep_a_all, c.ret_incep_a_gips
  193. // , 123 AS creatorid, now() AS createtime, 123 AS updaterid, now() AS updatetime, 1 AS isvalid
  194. FROM tb_rets a
  195. LEFT JOIN tb_ret_ytd b ON a.fund_id = b.fund_id AND a.end_date = b.end_date
  196. LEFT JOIN tb_ret_incep c ON a.fund_id = c.fund_id AND a.end_date = c.end_date
  197. WHERE c.price_date IS NOT NULL
  198. ORDER BY a.fund_id, c.price_date
  199. /*
  200. // 把这些数据写回mySQL数据表
  201. save_table(tb_fund_performance, "mfdb.fund_performance", true)
  202. // 把这些数据写到本地数据表
  203. save_table(tb_fund_performance, "mfdb.fund_performance", false)
  204. */
  205. return tb_fund_performance
  206. }
  207. /*
  208. * 批量计算私募基金周收益
  209. * TODO: 需要用每周最后一个交易日?
  210. *
  211. * cal_hedge_fund_weekly_returns("'HF000004KN','HF00018WXG','HF000103EU'", true)
  212. *
  213. */
  214. def cal_hedge_fund_weekly_returns(fund_ids, isFromMySQL) {
  215. // 用于保证老基金也能取到所有历史净值
  216. very_old_price_date = 1990.01.01
  217. tb_nav = get_nav_by_price_date('HF', fund_ids, very_old_price_date, isFromMySQL)
  218. UPDATE tb_nav SET year_week = price_date.year()$STRING + (price_date.weekOfYear()$STRING).lpad(2, "0")
  219. tb_weekly_nav = SELECT fund_id, year_week, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
  220. FROM tb_nav
  221. GROUP BY fund_id, year_week
  222. ORDER BY fund_id, year_week
  223. // 这里选最简单的计算方式:不补任何净值空洞,净值前值日期不做任何限制
  224. // TODO: 可以考虑将月收益也改为这种方式
  225. tb_rets_1w = SELECT fund_id, year_week, price_date, cumulative_nav, cumulative_nav.ratios()-1 AS ret_1w
  226. FROM tb_weekly_nav
  227. ORDER BY fund_id, year_week
  228. return tb_rets_1w
  229. }
  230. /*
  231. * 批量计算私募基金区间收益
  232. * TODO: mySQL version 向前取4天,向后不做限制。这里的逻辑是向前取4个交易日
  233. *
  234. * get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
  235. *
  236. */
  237. def get_trailing_return(table_last_nav, table_nav, duration, return_column_name) {
  238. tb = SELECT a.fund_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret
  239. FROM table_last_nav a
  240. INNER JOIN table_nav b ON a.fund_id = b.fund_id
  241. WHERE b.price_date <= a.price_date.datetimeAdd(duration)
  242. AND b.price_date >= a.price_date.datetimeAdd(duration).datetimeAdd(-4d).businessDay()
  243. GROUP by a.fund_id
  244. ORDER BY fund_id
  245. tb.rename!("ret", return_column_name)
  246. return tb
  247. }
  248. /*
  249. * 批量计算私募基金最新收益
  250. *
  251. *
  252. * cal_hedge_fund_weekly_returns("'HF000004KN','HF00018WXG','HF000103EU'", true)
  253. *
  254. */
  255. def cal_hedge_fund_latest_returns(fund_ids, isFromMySQL) {
  256. // 用于保证老基金也能取到所有历史净值
  257. very_old_price_date = 1990.01.01
  258. tb_nav = get_nav_by_price_date('HF', fund_ids, very_old_price_date, isFromMySQL)
  259. tb_last_nav = SELECT fund_id, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
  260. FROM tb_nav
  261. GROUP BY fund_id
  262. ORDER BY fund_id
  263. // 近1期收益,对应mySQL fund_latest_nav_performance 中的 net_value_change
  264. // 因为是倒序,所以算出来的 ratios() = n0 / n1, 要把它改换成 n1 / n0 - 1 的收益
  265. tb_last_return = SELECT TOP 2 fund_id, price_date.first() AS price_date, price_date AS pre_preice_date,
  266. cumulative_nav.first() AS cumulative_nav, 1\cumulative_nav.ratios() - 1 AS net_value_change
  267. FROM ( SELECT * FROM tb_nav ORDER BY price_date DESC )
  268. CONTEXT BY fund_id
  269. ORDER BY fund_id
  270. tb_last_return = SELECT * FROM tb_last_return WHERE net_value_change IS NOT NULL
  271. // 近1交易日收益
  272. tb_1d = SELECT a.fund_id, a.price_date, a.cumulative_nav \ b.cumulative_nav - 1 AS ret_1d
  273. FROM tb_last_nav a
  274. INNER JOIN tb_nav b ON a.fund_id = b.fund_id AND b.price_date = a.price_date.datetimeAdd(-1d).businessDay()
  275. ORDER BY fund_id
  276. // 近1周、1/3/6月、1/2/3/4/5/10年收益
  277. tb_1w = get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
  278. tb_1m = get_trailing_return(tb_last_nav, tb_nav, -1M, "ret_1m")
  279. tb_3m = get_trailing_return(tb_last_nav, tb_nav, -3M, "ret_3m")
  280. tb_6m = get_trailing_return(tb_last_nav, tb_nav, -6M, "ret_6m")
  281. tb_1y = get_trailing_return(tb_last_nav, tb_nav, -1y, "ret_1y")
  282. tb_2y = get_trailing_return(tb_last_nav, tb_nav, -2y, "ret_2y")
  283. tb_3y = get_trailing_return(tb_last_nav, tb_nav, -3y, "ret_3y")
  284. tb_4y = get_trailing_return(tb_last_nav, tb_nav, -4y, "ret_4y")
  285. tb_5y = get_trailing_return(tb_last_nav, tb_nav, -5y, "ret_5y")
  286. tb_10y = get_trailing_return(tb_last_nav, tb_nav, -10y, "ret_10y")
  287. // ytd return
  288. tb_ytd = SELECT a.fund_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret_ytd
  289. FROM tb_last_nav a
  290. INNER JOIN tb_nav b ON a.fund_id = b.fund_id
  291. WHERE b.price_date < a.price_date.yearBegin()
  292. AND b.price_date >= a.price_date.yearBegin().datetimeAdd(-4d)
  293. GROUP by a.fund_id
  294. // since inception return
  295. tb_fund_info = get_fund_info(fund_ids)
  296. tb_incep = SELECT a.fund_id, a.price_date, -1 + cumulative_nav \ ini_value AS ret_incep, fi.inception_date
  297. FROM tb_last_nav a
  298. INNER JOIN tb_fund_info fi ON a.fund_id = fi.fund_id
  299. // annulized since reception return
  300. UPDATE tb_incep SET ret_incep_a = (1 + ret_incep).pow(365.25\(price_date-inception_date)) - 1
  301. UPDATE tb_incep SET ret_incep_a_all = ret_incep_a,
  302. ret_incep_a_gips = iif((price_date-inception_date)<365, ret_incep, ret_incep_a)
  303. // 最大回撤
  304. tb_drawdown_1m = SELECT a.fund_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_1m
  305. FROM tb_last_return a
  306. INNER JOIN tb_nav b ON a.fund_id = b.fund_id
  307. WHERE b.price_date >= a.price_date.datetimeAdd(-1M)
  308. GROUP BY a.fund_id
  309. tb_drawdown_3m = SELECT a.fund_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_3m
  310. FROM tb_last_return a
  311. INNER JOIN tb_nav b ON a.fund_id = b.fund_id
  312. WHERE b.price_date >= a.price_date.datetimeAdd(-3M)
  313. GROUP BY a.fund_id
  314. tb_drawdown_incep = SELECT fund_id, max( 1 - cumulative_nav \ cumulative_nav.cummax() ) AS drawdown_incep
  315. FROM tb_nav GROUP BY fund_id
  316. tb_rets = SELECT a.fund_id, a.price_date.datetimeFormat("yyyy-MM") AS end_date, a.price_date, a.pre_preice_date, a.cumulative_nav,
  317. a.net_value_change, d1.ret_1d, w1.ret_1w, m1.ret_1m, m3.ret_3m, m6.ret_6m,
  318. y1.ret_1y, y2.ret_2y, y3.ret_3y, y4.ret_4y, y5.ret_5y, y10.ret_10y,
  319. ytd.ret_ytd, incep.ret_incep, incep.ret_incep_a, incep.ret_incep_a_all, incep.ret_incep_a_gips,
  320. dd_m1.drawdown_1m AS maxdrawdown_1m, dd_m3.drawdown_3m AS maxdrawdown_3m,
  321. dd_incep.drawdown_incep AS maxdrawdown_incep,
  322. iif(dd_incep.drawdown_incep == 0, null,incep.ret_incep_a \ dd_incep.drawdown_incep) AS calmarratio_incep
  323. FROM tb_last_return a
  324. LEFT JOIN tb_1d d1 ON a.fund_id = d1.fund_id
  325. LEFT JOIN tb_1w w1 ON a.fund_id = w1.fund_id
  326. LEFT JOIN tb_1m m1 ON a.fund_id = m1.fund_id
  327. LEFT JOIN tb_3m m3 ON a.fund_id = m3.fund_id
  328. LEFT JOIN tb_6m m6 ON a.fund_id = m6.fund_id
  329. LEFT JOIN tb_1y y1 ON a.fund_id = y1.fund_id
  330. LEFT JOIN tb_2y y2 ON a.fund_id = y2.fund_id
  331. LEFT JOIN tb_3y y3 ON a.fund_id = y3.fund_id
  332. LEFT JOIN tb_4y y4 ON a.fund_id = y4.fund_id
  333. LEFT JOIN tb_5y y5 ON a.fund_id = y5.fund_id
  334. LEFT JOIN tb_10y y10 ON a.fund_id = y10.fund_id
  335. LEFT JOIN tb_ytd ytd ON a.fund_id = ytd.fund_id
  336. LEFT JOIN tb_incep incep ON a.fund_id = incep.fund_id
  337. LEFT JOIN tb_drawdown_1m dd_m1 ON a.fund_id = dd_m1.fund_id
  338. LEFT JOIN tb_drawdown_3m dd_m3 ON a.fund_id = dd_m3.fund_id
  339. LEFT JOIN tb_drawdown_incep dd_incep ON a.fund_id = dd_incep.fund_id
  340. ORDER BY a.fund_id
  341. // 忽略掉非GIPS标准的所有年化收益字段(包括ytd_a)
  342. UPDATE tb_rets SET ret_1y_a = ret_1y, ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1,
  343. ret_4y_a = (1 + ret_4y).pow(1\4) - 1, ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1
  344. return tb_rets
  345. }