set hive.exec.parallel = true;
set dt_t=sysdate(-1);

select

time,
t.union_id,
sum(t.valid_par_ord_num) as "有效父单量" ,
sum(t.valid_sale_ord_num) as "有效子单量" ,
sum(t.valid_aft_amount) as "有效优惠后GMV" ,
sum(t.valid_user_num) as "有效下单用户数" ,
sum(t.valid_first_user_num)as "有效首次购用户数" ,
t.jd_pin as 'jd_pin',
t.parent_uin as '父账号ID',
t.ad_uin as '子账户uin',
t.plan_id as '推广计划ID',
t.plan_name as '推广计划名称',
t.adspec_id as '规格ID',
t.adspec_name as '规格名称',
t.plat as '设备',
sum(t.pv) as pv,
sum(t.uv) as uv,
sum(t.valid_uv) as '有效uv' ,
sum(t.new_reg) as '新注册用户数' ,
t.ad_id

from
(
select
f.dt as time,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
'APP'  as plat,
sum( case  when pv_sign =1 then 1 else 0 end ) as pv ,
count(distinct browser_uniq_id) as uv ,
0.0 as valid_uv,
--count(distinct (case when sequence_num > 1 then browser_uniq_id end)) as valid_uv,
count(distinct case when  g.user_log_acct is not null
then g.user_log_acct end) as new_reg,
y.ad_id as ad_id

from
(
select *
from gdm.gdm_m14_wireless_online_log where dt=${hiveconf:dt_t}
and  os_plant in ('IOS','ANDROID','IPAD','IOS-M','ANDROID-M','IPAD-M')
and chan_third_cate_cd  in ('131' ,'141' )
) f
left outer join
(select user_log_acct from gdm.gdm_m01_userinfo_basic_sum where
dt=${hiveconf:dt_t} and substr(user_reg_tm,1,10) =${hiveconf:dt_t}) g
on upper(trim(f.user_log_acct)) = upper(trim(g.user_log_acct))
left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y
on f.utm_term = y.trace_id

left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id



group by
f.dt,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id



union all

select
f.dt as time,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'M' end ) as plat,
count(mba_sid) as pv ,count(distinct mba_muid) as uv ,
0.0 as valid_uv,
--count(distinct (case when mba_seq > 1 then mba_muid end)) as valid_uv ,
count(distinct case when  g.user_log_acct is not null
then g.user_log_acct end) as new_reg,
y.ad_id as ad_id

from

(
select *
from gdm.gdm_m14_wireless_online_log where dt=${hiveconf:dt_t}
and os_plant in ('WEIXIN-M','M-M')
and chan_third_cate_cd  in ('131' ,'141' )
) f
LEFT OUTER JOIN
(select user_log_acct from gdm.gdm_m01_userinfo_basic_sum where
dt=${hiveconf:dt_t} and substr(user_reg_tm,1,10)=${hiveconf:dt_t}) g
on upper(trim(f.user_log_acct)) = upper(trim(g.user_log_acct))
left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y

on f.utm_term = y.trace_id

left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id
group by

f.dt,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id




union all

select
f.dt as time,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'PC' end ) as plat,
count(1) as pv,
count(distinct browser_uniq_id) as uv ,
0.0 as valid_uv,
--count(distinct (case when stm_max_pv_qtty > 1 then browser_uniq_id end)) as valid_uv,
count(distinct case when  g.user_log_acct is not null
then g.user_log_acct end) as new_reg,
y.ad_id as ad_id

from

(
select  *
from gdm.gdm_online_log where dt=${hiveconf:dt_t} and web_site_id = '1'
and chan_third_cate_cd  in ('131' ,'141' )

) f
LEFT OUTER JOIN
(select user_log_acct from gdm.gdm_m01_userinfo_basic_sum where
dt=${hiveconf:dt_t} and substr(user_reg_tm,1,10) =${hiveconf:dt_t}) g
on upper(trim(f.user_log_acct)) = upper(trim(g.user_log_acct))

left outer join

(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y
on f.utm_term = y.trace_id


left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id


group by
f.dt,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id

union all

select
f.dt as time,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'WQ' end ) as plat,
count(1) as pv,
count(distinct visit_key) as uv ,
0.0 as valid_uv,
--count(distinct (case when stm_max_pv_qtty > 1 then visit_key end)) as valid_uv,
count(distinct case when  g.user_log_acct is not null
then g.user_log_acct end) as new_reg,
y.ad_id as ad_id

from
(
select *
from gdm.gdm_app_wx_qq_log  where dt=${hiveconf:dt_t}
and chan_type in ('1','2','3','4')
and chan_third_cate_cd  in ('131' ,'141' )
) f
LEFT OUTER JOIN
(select user_log_acct from gdm.gdm_m01_userinfo_basic_sum where
dt=${hiveconf:dt_t} and substr(user_reg_tm,1,10) =${hiveconf:dt_t}) g
on upper(trim(f.user_log_acct)) = upper(trim(g.user_log_acct))
left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y
on f.utm_term = y.trace_id
left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id

group by
f.dt,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id

union all

select
a.dt as time,
regexp_extract(a.utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
count(distinct(a.parent_sale_ord_id)) as valid_par_ord_num ,
count(distinct(a.sale_ord_id)) as valid_sale_ord_num,
sum(a.after_prefr_amount) as valid_aft_amount,
count(distinct(a.user_log_acct)) as valid_user_num ,
count(distinct case when a.first_ord_flag = '0'
then a.parent_sale_ord_id end ) as valid_first_user_num,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
a.plat_flag as plat,
'' as pv,
'' as uv,
'' as valid_uv,
'' as new_reg,
y.ad_id as ad_id

from

(

select *
from adm.adm_m14_plat_chan_cart_ord_det_d where sale_ord_valid_flag = '1'
and   dt=${hiveconf:dt_t} and sale_ord_dt=${hiveconf:dt_t}
and chan_third_cate_cd  in ('131' ,'141' )

) a
LEFT OUTER JOIN
(
select trace_id,ad_id,realcost, accounttype,max(dt)
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and dt=${hiveconf:dt_t}
and  is_bill != '1'
group by trace_id,ad_id,realcost, accounttype
) y
on a.utm_term = y.trace_id
left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id

group by
a.dt,
regexp_extract(a.utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
a.plat_flag,
y.ad_id


union all

select
a1.dt as time,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'APP' end ) as plat,
0.0 as pv,
0.0 as uv,
 count( browser_uniq_id)  as valid_uv,
 0.0 as new_reg,
y.ad_id as ad_id
  from
  (select * from

  (
  select dt,count(1) as pv,browser_uniq_id,jdv_utm_campaign,utm_term
    from gdm.gdm_m14_wireless_online_log
    where dt=${hiveconf:dt_t}
and chan_third_cate_cd  in ('131' ,'141' )
   and os_plant in ('IOS','ANDROID','IPAD','IOS-M','ANDROID-M','IPAD-M')
        group by dt,browser_uniq_id,jdv_utm_campaign,utm_term
 )a
 where a.pv > 1
 ) a1

 left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y

on a1.utm_term = y.trace_id

left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id
 group by
a1.dt,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id


union all

select
a1.dt as time,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'M' end ) as plat,
0.0 as pv,
0.0 as uv,
count( mba_muid) as valid_uv,
0.0 as new_reg,
y.ad_id as ad_id

from
(select * from
      (
      select dt,count(1) as pv,mba_muid,jdv_utm_campaign,utm_term
        from gdm.gdm_m14_wireless_online_log
        where dt=${hiveconf:dt_t}
and chan_third_cate_cd  in ('131' ,'141' )
            and biz_type = 'mba'
            and os_plant in ('M-M', 'WEIXIN-M')
            and user_agent not like '%kepler=kepler%'
            group by dt,mba_muid,jdv_utm_campaign,utm_term
     )a
     where pv > 1
) a1
 left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y

on a1.utm_term = y.trace_id

left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id
 group by
a1.dt,
regexp_extract(jdv_utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id

 union all
select
a1.dt as time,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'PC' end ) as plat,
0.0 as pv,
0.0 as uv,
count( browser_uniq_id   ) as valid_uv,
0.0 as new_reg,
y.ad_id as ad_id


from
(select * from
(
select dt,count(1) as pv,browser_uniq_id,utm_campaign,utm_term
from gdm.gdm_online_log
where dt=${hiveconf:dt_t}
and chan_third_cate_cd  in ('131' ,'141' )
and web_site_id = '1'
group by dt,browser_uniq_id,utm_campaign,utm_term)a
where pv > 1  )a1
left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1'
) y

on a1.utm_term = y.trace_id

left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id
group by
a1.dt,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id

 union all

select
a1.dt as time,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1) as union_id,
'' as valid_par_ord_num ,
'' as valid_sale_ord_num ,
'' as valid_aft_amount ,
'' as valid_user_num ,
'' as valid_first_user_num ,
s.jd_pin as jd_pin,
s.parent_uin as parent_uin,
s.ad_uin as ad_uin,
s.plan_id as plan_id,
s.plan_name as plan_name,
s.adspec_id as adspec_id,
s.adspec_name as adspec_name,
(case when 1=1 then 'WQ' end ) as plat,
0.0 as pv,
0.0 as uv,
count ( visit_key )as valid_uv,
0.0 as new_reg,
y.ad_id as ad_id


  from
    (select * from
  (
  select dt,count(1) as pv,visit_key,utm_campaign,utm_term
    from gdm.gdm_app_wx_qq_log
 where dt=${hiveconf:dt_t}
and chan_third_cate_cd  in ('131' ,'141' )
 and chan_type in ('1','2','3','4')
 group by dt,visit_key,utm_campaign,utm_term
 )a
 where pv > 1
 ) a1
   left outer join
(select trace_id, ad_id
from
app.ad_base_click_synchro_test
where
ad_traffic_group = '9' and
dt=${hiveconf:dt_t}
and  is_bill != '1' ) y
on a1.utm_term = y.trace_id
left outer join (
	select * from tmp.tmp_szad_w_ad_info_day where dt = ${hiveconf:dt_t}
) s
on y.ad_id = s.ad_id
  group by
a1.dt,
regexp_extract(utm_campaign,'^t_([0-9]*)_(.*)$',1),
s.jd_pin ,
s.parent_uin ,
s.ad_uin ,
s.plan_id ,
s.plan_name ,
s.adspec_id ,
s.adspec_name,
y.ad_id


)t

group by
t.time,
t.union_id,
t.jd_pin,
t.parent_uin ,
t.ad_uin,
t.plan_id,
t.plan_name,
t.adspec_id ,
t.adspec_name,
t.plat,
t.ad_id