关于统计查询的优化

各位：最近在优化肿瘤系统的一个查询统计的时候碰到一个性能问题。具体的统计要求如下：相信大家在以往的工作中也碰到过类似的的统计。根据我们平常的经验做法，我们一般会把统计SQL携程类似的形式：selectg.icd_group,g.group_name,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andIS_DELETE=0andt.PRESENT_RESIDENCElike'4401%')astotal,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and1*12months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa0,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and1*12months_BETWEEN(t.diagnose_date,t.birthday)and5*12months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa0_5,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and10*12months_BETWEEN(t.diagnose_date,t.birthday)and5*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa5_10,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and15*12months_BETWEEN(t.diagnose_date,t.birthday)and10*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa10_15,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and20*12months_BETWEEN(t.diagnose_date,t.birthday)and15*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa15_20,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and25*12months_BETWEEN(t.diagnose_date,t.birthday)and20*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa20_25,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and30*12months_BETWEEN(t.diagnose_date,t.birthday)and25*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa25_30,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and35*12months_BETWEEN(t.diagnose_date,t.birthday)and30*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa30_35,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and40*12months_BETWEEN(t.diagnose_date,t.birthday)and35*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa35_40,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and45*12months_BETWEEN(t.diagnose_date,t.birthday)and40*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa40_45,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and50*12months_BETWEEN(t.diagnose_date,t.birthday)and45*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa45_50,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and55*12months_BETWEEN(t.diagnose_date,t.birthday)and50*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa50_55,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and60*12months_BETWEEN(t.diagnose_date,t.birthday)and55*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa55_60,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and65*12months_BETWEEN(t.diagnose_date,t.birthday)and60*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa60_65,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESIDENCElike'4401%'and70*12months_BETWEEN(t.diagnose_date,t.birthday)and65*12=months_BETWEEN(t.diagnose_date,t.birthday)andIS_DELETE=0)asa65_70,(selectcount(*)fromreporttwheret.INTERVIEW_DISTRIBUTE_STATE!=5andt.icd_grouplikeg.icd_group||'%'andt.IS_PASS_REVIEW=2andt.REPEATED_DISPOSE_FLAG3andt.PRESENT_RESID