优化SQL脚本—求所有投资人首月投资金额

来源：互联网发布：形容女生的词语网络词编辑：程序博客网时间：2024/05/18 02:10

工作中遇到个问题：要求所有投资人首月投资的金额。举例说明：求fff4d7d1752f4950b088c5b550a82ac9的首月投资金额即求2015-12月所有的投资金额。

为实现上述需求，原本采用笛卡尔集的形式，详细SQL如下：

#统计首月投资金额SELECTC.INVESTOR AS INVEST,SUM(C.INVESTAMOUNT) AS F_M_INVESTAMOUNT,C.FLAGFROM(SELECTB.*, A.*FROMDW_ODS.ODS_HZCF_T_INVEST AS B,(SELECTINVESTOR AS MININVESTOR,MIN(DATE_FORMAT(INVESTTIME, '%Y%m')) AS MINTIME,FLAG AS MINFLAGFROMDW_ODS.ODS_HZCF_T_INVESTGROUP BYMININVESTORHAVINGMINFLAG = '30007001002') AS A) AS CWHEREC.INVESTOR = C.MININVESTORAND DATE_FORMAT(INVESTTIME, '%Y%m') = C.MINTIMEGROUP BYINVESTORHAVINGC.FLAG = '30007001002';

这样做的话非常非常非常非常慢。因此，需要对SQL进行优化。采用什么方式进行优化呐？答：临时表。详见下面的SQL：

#统计首月投资金额DROP table TEM_1;CREATE TEMPORARY TABLE TEM_1 SELECTINVESTOR AS MININVESTOR,MIN(DATE_FORMAT(INVESTTIME, '%Y%m')) AS MINTIME,FLAG AS MINFLAGFROMDW_ODS.ODS_HZCF_T_INVESTGROUP BYMININVESTORHAVINGMINFLAG = '30007001002';SELECTC.INVESTOR,C.INVESTAMt AS FIRST_MONTH_INVEST_MONEY,C.INVESTTIMEFROM(SELECTINVESTOR,INVESTTIME,SUM(INVESTAMOUNT) AS INVESTAMtFROMDW_ODS.ODS_HZCF_T_INVEST AS AJOIN TEM_1 AS B ON A.INVESTOR = B.MININVESTORAND A.FLAG = '30007001002'AND DATE_FORMAT(A.INVESTTIME, '%Y%m') = B.MINTIMEGROUP BYA.INVESTOR) AS CGROUP BY C.INVESTOR;

结语：优化后的SQL执行起来要比原来的SQL快N倍，因此，对表里的数据进行统计时，尽量别用笛卡尔集。

0 1