A:
可封装打分函数例如:getScorebyRank(t,FileName,r,s,sortdir)
实现指定列按指定顺序排序后,按排序的百分位进行分组,进行打分。
function getScorebyRank(t,FileName,r,s,sortdir);
begin
{t 数组
FileName 字符串,打分的列
r 百分位分组序列,前开后闭,例如array((0,20),(20,40),(40,60),(60,80),(80,100));
s 打分序列 与r对应,例如array(5,4,3,2,1)
sortdir为排序方向:1 正序,0 逆序
}
lent:=length(T);
if sortdir then
t1:= select *,thisorder as FileName$'-排名'
from t order by [FileName] end;
else
t1:= select *,thisorder as FileName$'-排名'
from t order by [FileName] desc end;
t1:= select *,[FileName$'-排名']/lent*100 as '分组' from t1 end;
for i:=0 to length(r)-1 do
update t1 set [FileName$'-得分']=s[i]
where ['分组']>r[i,0] and ['分组']<=r[i,1] end;
reindex(t1,nil,array('分组':Nil));
return t1;
end;
调用案例:实现对指标夏普,詹森、特雷诺、信息比、标准差按指定规则进行排名打分。
其中,夏普,詹森、特雷诺、信息比按从大到小分成均匀五组,第一组得5分,第二组得4分,依此类推;
标准差按从小到大分成不均匀五组,打分同上,最后加总得到总得分。
t:= select ['StockID'],['截止日'],['夏普(%)'],['詹森(%)'],["特雷诺(%)"],
["信息比(%)"],['标准差(%)']
from infotable 622 of getbk('ETF')
where ['截止日']=20200731 and ['数据类型']='最近1年' end;
//-百分位排名分组
r1:=array((0,20),(20,40),(40,60),(60,80),(80,100)); //前开后闭
//-分组对应分数
s1:=array(5,4,3,2,1);
//不同的指标,可按不同的排序方向,以及给不同的排名方式进行打分。给不同的r,s即可。
t:= getScorebyRank(t,'夏普(%)',r1,s1,0);
t:= getScorebyRank(t,'詹森(%)',r1,s1,0);
t:= getScorebyRank(t,'特雷诺(%)',r1,s1,0);
t:= getScorebyRank(t,'信息比(%)',r1,s1,0);
r2:=array((0,10),(10,20),(20,30),(30,60),(60,100)); //前开后闭
s2:=array(5,4,3,2,1);
t:= getScorebyRank(t,'标准差(%)',r2,s2,1);
t:= select *,
['夏普(%)-得分']+['詹森(%)-得分']+['特雷诺(%)-得分']
+['信息比(%)-得分']+['标准差(%)-得分'] as '总得分'
from t end;
return select * from t order by ['总得分'] desc end;
//返回结果:
StockID | 截止日 | 夏普(%) | 詹森(%) | 特雷诺(%) | 信息比(%) | 标准差(%) | 夏普(%)-排名 | 夏普(%)-得分 | 詹森(%)-排名 | 詹森(%)-得分 | 特雷诺(%)-排名 | 特雷诺(%)-得分 | 信息比(%)-排名 | 信息比(%)-得分 | 标准差(%)-排名 | 标准差(%)-得分 | 总得分
|
---|
OF510150 | 20200731 | 36.666700 | 0.830200 | 1.217200 | 51.351800 | 2.795400 | 12 | 5 | 28 | 5 | 20 | 5 | 24 | 5 | 57 | 3 | 23
|
OF159973 | 20200731 | 32.602000 | 0.729200 | 0.985000 | 56.278100 | 2.923100 | 21 | 5 | 31 | 5 | 35 | 5 | 11 | 5 | 86 | 2 | 22
|
OF510660 | 20200731 | 41.808000 | 1.018800 | 1.841400 | 46.353700 | 2.786900 | 4 | 5 | 8 | 5 | 6 | 5 | 43 | 4 | 56 | 3 | 22
|
OF518880 | 20200731 | 25.669800 | 0.533300 | 3.916500 | 24.543400 | 2.208100 | 38 | 5 | 49 | 4 | 1 | 5 | 103 | 3 | 15 | 5 | 22
|
OF159937 | 20200731 | 25.612100 | 0.531600 | 3.892800 | 24.481800 | 2.206800 | 39 | 5 | 51 | 4 | 4 | 5 | 104 | 3 | 14 | 5 | 22
|
OF159934 | 20200731 | 25.689600 | 0.533000 | 3.913000 | 24.562200 | 2.205300 | 37 | 5 | 50 | 4 | 3 | 5 | 102 | 3 | 12 | 5 | 22
|
OF159949 | 20200731 | 37.164100 | 1.154800 | 1.177400 | 57.548900 | 3.867100 | 10 | 5 | 5 | 5 | 22 | 5 | 9 | 5 | 176 | 1 | 21
|
OF159948 | 20200731 | 32.166800 | 0.894700 | 1.006600 | 49.450900 | 3.611300 | 25 | 5 | 21 | 5 | 31 | 5 | 33 | 5 | 168 | 1 | 21
|
OF159915 | 20200731 | 32.292700 | 0.897800 | 1.010600 | 49.688500 | 3.605500 | 22 | 5 | 19 | 5 | 28 | 5 | 31 | 5 | 166 | 1 | 21
|
OF159952 | 20200731 | 32.007100 | 0.882700 | 0.999000 | 49.478300 | 3.588800 | 28 | 5 | 25 | 5 | 33 | 5 | 32 | 5 | 165 | 1 | 21
|
OF159958 | 20200731 | 32.219600 | 0.887100 | 1.006900 | 49.724800 | 3.574600 | 23 | 5 | 23 | 5 | 29 | 5 | 30 | 5 | 164 | 1 | 21
|
OF159908 | 20200731 | 32.055200 | 0.880100 | 1.003200 | 49.215300 | 3.568600 | 27 | 5 | 26 | 5 | 32 | 5 | 35 | 5 | 163 | 1 | 21
|
...... | | | | | | | | | | | | | | | | |
|