经常要给业务部门提数据,其中要统计一个管理费,按照客户经理、基金帐号的顺序进行汇总。要关联好几个表tasharecurrents(基金份额流水),t_cus_manager_associate(客户经理与客户对应关系表),tafundtype(基金基本信息),taaccoinfo(基金帐号信息),tafundday_add(基金365日每日净值)。在t_cus_manager_associate.cusmanager_type字段上有2个枚举值('D'-开发人,'M'-维护人),在编写SQL语句时,由于疏忽,掉了个过滤条件cusmanager_type = 'M' ,结果在group by聚合运算后,部分管理费被放大了1倍(即正确结果的2倍),错误比较严重。
这段代码是从同事移交给我的,要求每个月出一次数据(月度统计)。当时没有质疑输出结果的正确性,连着3个月给业务部门出数据,上周发现了这个错误,修正了。在出6月份数据后,业务部门同事将6月份数据和5月份数据进行比较,发现6月份管理费减少了50多万(5月份150多万)。事实上,6月份的管理费应该会多于5月份的(由于基金份额增多)。简单解释了一下问题原因,但业务部门还是很不爽,说对我们现在出的数据持非常怀疑。
看来,对业务知识还要更深入的了解一番,并且在出数据时要进行一些简单的校验(总量的核对、单笔明细的抽查等手段),尽可能减少错误的产生。下边附上SQL语句如下(可读性比较查,子查询的嵌套,略微有点复杂)
--阶段管理费
select /*+ index(w2 PK_TAACCOINFO) */
w4.cusmanager_name 客户经理,
w2.taaccountid 基金帐号,
w2.investorname 投资人,
decode(w2.individualorinstitution,'1','个人','机构') 客户类型,
w3.fundname 基金名称,
round(sum(ma_fee), 2) 管理费总额
from (select w1.LASTSHARES *
(select sum(t3.nav * t3.cur_manfee_rate / 365)
from tafundday_add t3
where t3.alternationdate >= w1.startdate
and t3.alternationdate <= w1.enddate
and w1.fundcode = t3.fundcode) ma_fee,
w1.lastshares * (w1.enddate - w1.startdate + 1) shares,
w1.*
from (select /*+ index(t1 IDX_TASHARECURRENTS_ACCOUNTID) */
case
when t1.TRANSCFMDATE <= to_date('20070601', 'yyyymmdd') - 1 then --参数:前日期
to_date('20070601', 'yyyymmdd') - 1 --参数:前日期
else t1.TRANSCFMDATE
end startdate,
case
when t1.SHAREVALIDDATE > to_date('20070630', 'yyyymmdd') - 1 then --参数:后日期
to_date('20070630', 'yyyymmdd') - 1 --参数:后日期
else t1.SHAREVALIDDATE - 1
end enddate,
t1.*
from tasharecurrents t1
where t1.TRANSCFMDATE <= to_date('20070630', 'yyyymmdd') - 1 --参数:后日期
and t1.SHAREVALIDDATE > to_date('20070601', 'yyyymmdd') - 1 --参数:前日期
and t1.LASTSHARES > 0
--and t1.distributorcode!='207' --直销开关参数
and (t1.TAACCOUNTID in
(select t1.taaccountid from t_cus_level_type t1,t_cus_manager_associate t2
where 1=1
and t1.orgacustype='0'
and t1.taaccountid=t2.taaccountid
and (t2.cusmanager_name in('王艺军','关子阳','陈志新','施镜葵','金国伦','朱理理','张鹏','程萍','官燕红','胡学义','李宏玥','机构客户部')
or t1.taaccountid in('076100001956','077100103892','980019617603','076100013978','077100113190','077100002000','980019023074','076100000477','077100042609','076100014855', '077100738860', '980016564504', '077100044929', '980800021323')
)
)
--or t1.taaccountid in('076100001956','077100103892','980019617603','076100013978','077100113190','077100002000','980019023074','076100000477','077100042609','076100014855', '077100738860', '980016564504')
)
) w1 --参数:基金帐号列表
) w0,
taaccoinfo w2,
tafundtype w3,
t_cus_manager_associate w4
where w0.taaccountid = w2.taaccountid
and w0.fundcode = w3.fundcode
and w0.taaccountid=w4.taaccountid
AND w4.cusmanager_type = 'M' -- 遗漏后产生乘数效应
group by w4.cusmanager_name,w2.taaccountid, w2.investorname, decode(w2.individualorinstitution,'1','个人','机构'),w3.fundname