需要了解的知识点:
最大回撤率:在选定周期内任一历史时点往后推,产品净值走到最低点时的收益率回撤幅度的最大值。
最大回撤率计算公式:
最大回撤率=max[(Di−Dj)/ Di]
D为某一天的净值,i为某一天,j为i后的某一天,Di为第i天的产品净值,Dj则是Di后面某一天的净值
准备工作:
假设有4条数据(1号-4号),如图:
我把数据绘制成坐标图(微软自带的画图工具画的,勿喷)
思路:
①使用sys_connect_by_path对这4条数据进行排列组合
select sys_connect_by_path(value, '#') combo
from (select *
from tmp
where bizdate <= 20110104
order by bizdate asc) t
where level = 2
connect by prior bizdate < t.bizdate
and level <= 2
得到以下结果:
②截取字符串:
select substr(s.combo, 2, instr(s.combo, '#', -1) - 2) as num1,
substr(s.combo, instr(s.combo, '#', -1) + 1) as num2
from (select sys_connect_by_path(value, '#') combo
from (select *
from tmp
where bizdate <= 20110104
order by bizdate asc) t
where level = 2
connect by prior bizdate < t.bizdate
and level <= 2) s
得到以下结果:
③最终计算出最大回撤幅度:
select max((a.num1 - a.num2) / a.num1)
from (select substr(s.combo, 2, instr(s.combo, '#', -1) - 2) as num1,
substr(s.combo, instr(s.combo, '#', -1) + 1) as num2
from (select sys_connect_by_path(value, '#') combo
from (select *
from tmp
where bizdate <= 20110104
order by bizdate asc) t
where level = 2
connect by prior bizdate < t.bizdate
and level <= 2) s) a
得到最终结果: