sql 优化前后

 insert overwrite table t_md_soft_wp7_dload partition
   (ds = 20120820)
   select g_f,
          dload_count,
          dload_user,
          tensoft_dload_count,
          tensoft_dload_user,
          outsoft_dload_count,
          outsoft_dload_user
     from (
     select temp1.g_f,
                  temp1.dload_count,
                  temp1.dload_user,
                  temp2.tensoft_dload_count,
                  temp2.tensoft_dload_user,
                  temp3.outsoft_dload_count,
                  temp3.outsoft_dload_user
             from (select g_f,
                          count(1) as dload_user,
                          sum(t1.pv) as dload_count
                     from (select g_f, cookie_id, count(1) as pv
                             from t_od_soft_wp7_dload
                            where ds = 20120820
                            group by g_f, cookie_id) t1
                    group by g_f) temp1
             left outer join (select g_f,
                                    count(1) as tensoft_dload_user,
                                    sum(tt3.login_pv) as tensoft_dload_count
                               from (select g_f,
                                            cookie_id,
                                            count(1) as login_pv
                                       from t_od_soft_wp7_dload tt1
                                       join t_rd_soft_wp7_app tt2
                                         on tt1.ds = tt2.ds
                                        and tt1.ios_soft_id = tt2.appid
                                      where tt1.ds = 20120820
                                        and tt2.is_self_rd = 1
                                      group by g_f, cookie_id) tt3
                              group by g_f) temp2
               on temp1.g_f = temp2.g_f
             left outer join (select g_f,
                                    count(1) as outsoft_dload_user,
                                    sum(tt6.login_pv) as outsoft_dload_count
                               from (select g_f,
                                            cookie_id,
                                            count(1) as login_pv
                                       from t_od_soft_wp7_dload tt4
                                       join t_rd_soft_wp7_app tt5
                                         on tt4.ds = tt5.ds
                                        and tt4.ios_soft_id = tt5.appid
                                      where tt4.ds = 20120820
                                        and tt5.is_self_rd = 0
                                      group by g_f, cookie_id) tt6
                              group by g_f) temp3
               on temp1.g_f = temp3.g_f
           union all
           select temp4.g_f,
                  temp4.dload_count,
                  temp4.dload_user,
                  temp5.tensoft_dload_count,
                  temp5.tensoft_dload_user,
                  temp6.outsoft_dload_count,
                  temp6.outsoft_dload_user
             from (select cast(‘-1‘ as bigint) as g_f,
                          count(1) as dload_user,
                          sum(tt7.pv) as dload_count
                     from (select cast(‘-1‘ as bigint) as g_f,
                                  cookie_id,
                                  count(1) as pv
                             from t_od_soft_wp7_dload
                            where ds = 20120820
                            group by g_f, cookie_id) tt7
                    group by g_f) temp4
             left outer join (select cast(‘-1‘ as bigint) as g_f,
                                    count(1) as tensoft_dload_user,
                                    sum(tt10.login_pv) as tensoft_dload_count
                               from (select cast(‘-1‘ as bigint) as g_f,
                                            cookie_id,
                                            count(1) as login_pv
                                       from t_od_soft_wp7_dload tt8
                                       join t_rd_soft_wp7_app tt9
                                         on tt8.ds = tt9.ds
                                        and tt8.ios_soft_id = tt9.appid
                                      where tt8.ds = 20120820
                                        and tt9.is_self_rd = 1
                                      group by g_f, cookie_id) tt10
                              group by g_f) temp5
               on temp4.g_f = temp5.g_f
             left outer join (select cast(‘-1‘ as bigint) as g_f,
                                    count(1) as outsoft_dload_user,
                                    sum(tt13.login_pv) as outsoft_dload_count
                               from (select cast(‘-1‘ as bigint) as g_f,
                                            cookie_id,
                                            count(1) as login_pv
                                       from t_od_soft_wp7_dload tt11
                                       join t_rd_soft_wp7_app tt12
                                         on tt11.ds = tt12.ds
                                        and tt11.ios_soft_id = tt12.appid
                                      where tt11.ds = 20120820
                                        and tt12.is_self_rd = 0
                                      group by g_f, cookie_id) tt13
                              group by g_f) temp6
               on temp4.g_f = temp6.g_f) t;

insert overwrite table t_md_soft_wp7_dload partition
  (ds = 20120820)
  select g_f,
         count(cookie_id) dload_count,
         count(distinct cookie_id) dload_user,
         count(case
                 when is_self_rd = 1 then
                  cookie_id
               end) tensoft_dload_count,
         count(distinct case
                 when is_self_rd = 1 then
                  cookie_id
               end) tensoft_dload_user,
         count(case
                 when is_self_rd = 0 then
                  cookie_id
               end) outsoft_dload_count,
         count(distinct case
                 when is_self_rd = 0 then
                  cookie_id
               end) outsoft_dload_user
    from (select g_f, cookie_id, is_self_rd
            from t_od_soft_wp7_dload t1
            left outer join (select appid, is_self_rd
                              from t_rd_soft_wp7_app
                             where ds = 20120820) t2
              on t1.ios_soft_id = t2.appid
           where t1.ds = 20120820
          union all
          select cast(‘-1‘ as bigint) as g_f, cookie_id, is_self_rd
            from t_od_soft_wp7_dload t1
            left outer join (select appid, is_self_rd
                              from t_rd_soft_wp7_app
                             where ds = 20120820) t2
              on t1.ios_soft_id = t2.appid
           where t1.ds = 20120820) t
   group by g_f;