use test1;
explain select count(*) from
            (select starttime,
                eci,
                imei,
                imsi,
                city,
                collect_set1(t_http.tac) as tac,
                collect_set1(t_http.MSISDN) as MSISDN,
                collect_set1(t_http.enodeb) as enodeb,
                collect_set1(t_http.sgw) as sgw,
                apptype,
                appwhole,
                sum(case when 200 <= u16HttpWapStat and u16HttpWapStat < 400 then http_n else 0 end) as HttpSucc,
                sum(case when u16HttpWapStat=400 then http_n else 0 end) as Http_400,
                sum(case when u16HttpWapStat=401 then http_n else 0 end) as Http_401,
                sum(case when u16HttpWapStat=402 then http_n else 0 end) as Http_402,
                sum(case when u16HttpWapStat=403 then http_n else 0 end) as Http_403,
                sum(case when u16HttpWapStat=404 then http_n else 0 end) as Http_404,
                sum(case when u16HttpWapStat=405 then http_n else 0 end) as Http_405,
                sum(case when u16HttpWapStat=406 then http_n else 0 end) as Http_406,
                sum(case when u16HttpWapStat=407 then http_n else 0 end) as Http_407,
                sum(case when u16HttpWapStat=408 then http_n else 0 end) as Http_408,
                sum(case when u16HttpWapStat=409 then http_n else 0 end) as Http_409,
                sum(case when u16HttpWapStat=410 then http_n else 0 end) as Http_410,
                sum(case when u16HttpWapStat=411 then http_n else 0 end) as Http_411,
                sum(case when u16HttpWapStat=412 then http_n else 0 end) as Http_412,
                sum(case when u16HttpWapStat=413 then http_n else 0 end) as Http_413,
                sum(case when u16HttpWapStat=414 then http_n else 0 end) as Http_414,
                sum(case when u16HttpWapStat=415 then http_n else 0 end) as Http_415,
                sum(case when u16HttpWapStat=416 then http_n else 0 end) as Http_416,
                sum(case when u16HttpWapStat=417 then http_n else 0 end) as Http_417,
                sum(case when u16HttpWapStat=500 then http_n else 0 end) as Http_500,
                sum(case when u16HttpWapStat=501 then http_n else 0 end) as Http_501,
                sum(case when u16HttpWapStat=502 then http_n else 0 end) as Http_502,
                sum(case when u16HttpWapStat=503 then http_n else 0 end) as Http_503,
                sum(case when u16HttpWapStat=504 then http_n else 0 end) as Http_504,
                sum(case when u16HttpWapStat=505 then http_n else 0 end) as Http_505,
                sum(case when (417<u16HttpWapStat and u16HttpWapStat<500) or 505<u16HttpWapStat then http_n else 0 end) as Http_other ,
                sum(http_n) as HttpAtt,
                sum(Http_D) as HttpDelay,
                sum(case when 200 <= u16HttpWapStat and u16HttpWapStat < 400 then PageDlTraffic_tmp else 0 end) as PageDlTraffic,
                sum(PageDlTraffic_tmp) as DlTraffic,sum(UlTraffic_tmp) as UlTraffic,
                sum(case when 200 <= u16HttpWapStat and u16HttpWapStat < 400 then PageTransTime_tmp else 0 end) as PageTransTime,
                sum(case when s8HttpContentType in('application/vnd.android.package-archive','application/vnd.symbian.install','application/x-msdownload','application/vnd.iphone','application/x-silverlight-app') then PageDlTraffic_tmp else 0 end) as AppDLData,
                sum(case when s8HttpContentType in('application/vnd.android.package-archive','application/vnd.symbian.install','application/x-msdownload','application/vnd.iphone','application/x-silverlight-app') then PageTransTime_tmp else 0 end) as DLTransTime,
                sum(HttpSmallDataDelay_tmp) as HttpSmallDataDelay,
                sum(HttpSmall_XDR_tmp)  as HttpSmall_XDR ,
                sum(HttpBigTransTime_tmp) as HttpBigTransTime,
                sum(HttpBigData_tmp) as HttpBigData 
                from 
                (select (u32BeginTime-u32BeginTime%900000)/900000 as starttime,
                    u32eci as eci,
                    s8imei as imei,
                    s8imsi as imsi,
                    u16citycode as city,
                    collect_set1(u16Tac) as tac,
                    collect_set1(s8MSISDN) as MSISDN,
                    collect_set1(u32ENodebIP_1) as enodeb,
                    collect_set1(u32SGWIP_1) as sgw,
                    u16AppType as apptype,
                    u16AppTypeWhole as appwhole,
                    u16HttpWapStat,
                    count(1) as http_n,
                    sum(u32HttpLastAckTime) as Http_D ,
                    sum(u32DlTraffic*8)  as PageDlTraffic_tmp,
                    sum(u32HttpLastPacketTime-u32HttpFirstRespondTime)  as PageTransTime_tmp,
                    s8HttpContentType ,
                    sum(case when (u8RAT=2 and 100>(u32DlTraffic/1024)>30 ) or (u8RAT =1 and  (u32DlTraffic/1024)>30 and (u32DlTraffic/1024) <100) or (u8RAT =6 and  (u32DlTraffic/1024)>30 and (u32DlTraffic/1024) <300) then u32HttpLastAckTime else 0 end) as HttpSmallDataDelay_tmp,
                    sum(case when (u8RAT=2 and (u32DlTraffic/1024)>30 and (u32DlTraffic/1024) <100) or (u8RAT =1 and  (u32DlTraffic/1024)>30 and (u32DlTraffic/1024) <100) or (u8RAT =6 and  (u32DlTraffic/1024)>30 and (u32DlTraffic/1024) <300) then 1 else 0 end) as HttpSmall_XDR_tmp,
                    sum(case when u8RAT=2 and (u32DlTraffic/1024)>50 and (u32DlTraffic/1024) <100 or (u8RAT =1 and  (u32DlTraffic/1024)>100 and (u32DlTraffic/1024) <500) or (u8RAT =6 and  (u32DlTraffic/1024)>300 and (u32DlTraffic/1024) <500) then u32HttpLastAckTime else 0 end) as HttpBigTransTime_tmp,
                    sum(case when (u8RAT=2 and (u32DlTraffic/1024)>50 and (u32DlTraffic/1024) <100) or (u8RAT =1 and  (u32DlTraffic/1024)>100 and (u32DlTraffic/1024) <500) or (u8RAT =6 and  (u32DlTraffic/1024)>300 and (u32DlTraffic/1024) <500) then u32DlTraffic*8 else 0 end) as HttpBigData_tmp,
                    sum(u32DlTraffic) as UlTraffic_tmp  from http group by (u32BeginTime-u32BeginTime%900000)/900000,u32eci,s8imei,s8imsi,u16citycode,u16AppType,u16AppTypeWhole,u16HttpWapStat,s8HttpContentType)t_http 
            group by starttime,
                eci,
                imei,
                imsi,
                city,
                apptype,appwhole)t3 full join
            (select (u32BeginTime-u32BeginTime%900000)/900000 as starttime,
                u32eci as eci,
                s8imei as imei,
                s8imsi as imsi,
                u16citycode as city,
                collect_set1(u16Tac) as tac,
                collect_set1(s8MSISDN) as MSISDN,
                collect_set1(u32ENodebIP_1) as enodeb,
                collect_set1(u32SGWIP_1) as sgw,
                u16AppType as apptype,
                u16AppTypeWhole as appwhole,
                sum(case when u8TCPStat=0 then 1 else 0 end) as TcpSucc,
                count(1) as TcpAtt,
                sum(u32TCPConstructLinkRespTime) as TcpSetupTime,
                sum(u8TcpSynAckNum)as Tcp12Succ,
                sum(u8TCPRetryTimes) as Tcp12Att,
                sum(u64TcpSynAckTime-u64TcpSynTime) as Tcp12SetupTime,
                sum(u8TcpAckNum) as Tcp23Succ,
                sum(u8TcpSynAckNum) as Tcp23Att,
                sum(u64TcpHandShakeTime-u64TcpSynAckTime) as Tcp23SetupTime,
                sum(case when u8TCPStat=1 then u8TCPRetryTimes-u8TcpSynAckNum else 0 end) as TCP_SYN_FAIL,
                sum(case when u8TCPStat=1 then u8TcpSynAckNum-u8TcpAckNum else 0 end) as TCP_SYN_ACK_FAIL 
            from s1_u 
            where u8L4Protocol=0  
            group by (u32BeginTime-u32BeginTime%900000)/900000,
                u32eci,
                s8imei,
                s8imsi,
                u16citycode,u16AppType,u16AppTypeWhole)t4 
        on t3.starttime=t4.starttime and t3.eci=t4.eci and t3.imsi=t4.imsi and t3.city=t4.city and t3.apptype=t4.apptype and t3.imei=t4.imei;