数据业务札记04：最大回撤计算-更新版
在工作中，我们常会接触时间序列（价格序列、指数序列、经济变量序列等），对于时间序列我们常会通过计算其描述性统计指标来初步描述序列的特征。除此之外，我们还会
用最大回撤比率来描述时间序列从局部高点下滑至局部低点的幅度。下面通过一例子来进一步阐述最大回撤的含义：任一高点到其后续最低点的下跌幅度的最大值。说起来很拗
口，举个例子就明白了。下图是一个简化版的策略收益曲线图（纵轴是收益，横轴是时间），它的最大回撤是从C点到F点。
计算上述最大回撤的步骤一般如下：
①找到图中的局部高点：有A、C、E、G、I五个点；
②找到局部高点对应的后续最低点，分别是A→F、C→F、E→F、G→H、I→J。注意A对应的后续最低点是F，而不是B，因为F比B点更低。同样，C点的后续低点也是F，
而不是D点，因为F比D点更低。
③找出局部高点到后续最低点的最大跌幅，比较之后，显然是C→F的跌幅是最大的，按照定义，最大回撤就是C到F的下跌幅度。
那么这个过程我们使用sql要如何实现呢？
结合上述关于最大回撤的定义以及低点的寻找办法，在sql中我们可以用表的自关联实现数据的遍历：给定一个时间点的价格，找到后续价格序列中的各个点，并依次计算
下跌幅度。最后取下跌幅度中最大的一个，即可以得到最大跌幅max(former_price-latter_price)，类似的我们还可以得到最大回撤比率
max((former_price-latter_price)/former_price)
在呈现具体计算脚本之前，我们先建表造数据，数据中包含两个用户的价格序列，具体如下所示：
可以看到，用户aa在局部低点前出现一个极端高点值，用户bb的价格则呈现相对稳定的波动。
具体计算脚本如下：
select NN,round(max(loss),5) max_loss,round(max(loss_rate),5) max_loss_rate from ( select a.nn,a.tddate former_date,a.price former_price ,b.tddate as latter_tddate,b.price as letter_price,(a.price-b.price)/a.price as loss_rate,a.price-b.price as loss from sam_maxloss as a left join ( select NN,tddate,price,rn from (select NN,tddate,price,dense_rank ()over(partition by NN order by price) rn from sam_maxloss) a #where rn=1 ) b on a.NN=B.NN where a.tddate<b.tddate and a.price>b.price ) s group by nn
具体结果如下所示：
在上述代码中，大家不难发现我在子表b中用dense_rank对价格按照用户分组进行了排序，后续却没有用上这个排序结果，看似突兀的操作其实蕴含着楼主在写脚本时趟
过的一些误区。最初版本脚本的计算逻辑是先定位全局低点，在遍历该全局低点前的各个高点，计算各个高点的跌幅。这种情况可以得到用户bb的价格序列的最大回撤，
却无法得到用户aa的。可以看到用户aa的局部低点虽然不是全局低点，但是在该局部低点前出现了一个极端高点，最后得到了最大的跌幅。
我们不能先定位全局低点的根本原因在于，我们关注的重点不在于全局低点，因为即使是非全局低点，只要在该局部低点前出现极端局部高点，也会出现跌幅非常大的
情况，这种情况下的跌幅甚至会比确定全局低点时往前遍历高点的情况还要极端。希望小小的分享可以给大家带来便利。最后抒发一下近期写sql脚本的感受，在工作中
我们一定要透彻理解业务含义，挖掘内在兴趣点然后再去动手写脚本，那样会事半功倍，过程可能会很艰难，就跟儿时写奥数题一般，但解决困难后总会感觉收获满满~
------------------------------------------------------------低调的分割线-------------------------------------------------------------------------
理论上，任一序列都可以使用上述最大回撤比率脚本来计算其最大回撤。但实际上，序列的选取也是有讲究的。上述算法的核心在于遍历前高点，得到各个高点和低点
之间的跌幅，最后返回最大的一组跌幅，用于构成最大回撤。结合下面的公式会更加直观：
max((former_price-latter_price)/former_price)
但以下两种情形则会使得最后的最大回撤结果看上去“异常”，实际所谓的“异常”也是客观现象，根源在于序列的选取。在实务中，我们会偶有发现基于例如收益、盈亏等
存在负值的序列来计算最大回撤比率，会出现该指标显示为infinity，或者大于1的情况。仔细一想，出现上述两种情形无非以下两种情况：
①former_price=0,latter_price<0,这种情况下，最大回撤比率必然为正无穷，因为分母为0。
②0<former_price<1,later_price<0,这种情况下，由于former_price小于1，因此对比值指标会有一个放大效应。
看到这两种“异常”现象，我们先别急着下结论去报告异常，我们应该思考的是这种客观存在的“异常”能有什么含义。最大回撤大于1，或者infinity其实反映的就是计算序列
的特性，以及最大回撤比率确实大的情况。那么对于上述情形，我们能采取的方案有至少以下两大类（欢迎补充）
①对最大回撤比率大于1的情况，如果这种情形属于少数，我们可以直接把这部分数据映射为一个大于1的数值，用于和【0，1】之间的最大回撤形成对比。
这种操作的风险在于无法区分最大回撤比率大于1的样本差异。
②替换计算序列，我们可以考虑使用一些不会小于0且符合业务逻辑的指标序列来计算其最大回撤比率，可以考虑的指标有累计收益率，这个指标可以很大程度上避开上述
最大回撤大于1或者正无穷的情况。原因在于累计收益率=（1+day1收益率）*（1+day2收益率）......（1+dayn收益率），除非出现极端情况，即dayx收益率小于等于-1，
那么累计收益率都是可以保持大于0的。
投递招商银行等公司10个岗位 >
百度公司福利 513人发布