use test1;
select count(*) from select COALESCE(t3.starttime,t4.starttime) as starttime,
    COALESCE(t3.eci,t4.eci) as eci,
    COALESCE(t3.imei,t4.imei) as imei,
    COALESCE(t3.imsi,t4.imsi) as imsi,
    COALESCE(t3.city,t4.city) as city,
    COALESCE(t3.apptype,t4.apptype) as apptype,
    COALESCE(t3.appwhole,t4.appwhole) as appwhole,
    COALESCE(t3.tac,t4.tac) as tac,
    COALESCE(t3.MSISDN,t4.MSISDN) as MSISDN,
    COALESCE(t3.enodeb,t4.enodeb) as enodeb,
    COALESCE(t3.sgw,t4.sgw) as sgw,
            t3.HttpSucc,
            t3.Http_400,
            t3.Http_401,
            t3.Http_402,
            t3.Http_403,
            t3.Http_404,
            t3.Http_405,
            t3.Http_406,
            t3.Http_407,
            t3.Http_408,
            t3.Http_409,
            t3.Http_410,
            t3.Http_411,
            t3.Http_412,
            t3.Http_413,
            t3.Http_414,
            t3.Http_415,
            t3.Http_416,
            t3.Http_417,
            t3.Http_500,
            t3.Http_501,
            t3.Http_502,
            t3.Http_503,
            t3.Http_504,
            t3.Http_505,
            t3.Http_other,
            t3.HttpAtt,
            t3.HttpDelay,
            t3.PageDlTraffic,
            t3.DlTraffic,
            t3.UlTraffic,
            t3.PageTransTime,
            t3.AppDLData,
            t3.DLTransTime,
            t3.HttpSmallDataDelay,
            t3.HttpSmall_XDR,
            t3.HttpBigTransTime,
            t3.HttpBigData,
            t4.TcpSucc,
            t4.TcpAtt,
            t4.TcpSetupTime,
            t4.Tcp12Succ,
            t4.Tcp12Att,
            t4.Tcp12SetupTime,
            t4.Tcp23Succ,
            t4.Tcp23Att,
            t4.Tcp23SetupTime,
            t4.TCP_SYN_FAIL,
            t4.TCP_SYN_ACK_FAIL 
            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 use impala;