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;