USE eagle;
set hive.execution.engine=spark;
USE eagle;
USE view_huanju;
USE kaixindou;
CREATE TEMPORARY FUNCTION urldecode AS ‘com.hiido.hive.udf.URLDecodeUDF’;
— tmp0
create temporary table tmp0 AS
select
t0.uid,
t0.ver,
t0.sys,
t0.nation,
t0.user_type,
coalesce(t1.media_source,’Organic’) media_source,
coalesce(t1.adgroup,’其他’) adgroup
from
(select ver,case when appkey=’7c74186bd3a1e25806e3e2a11c1f30b7’ then ‘android’ else ‘ios’ end sys,nation,uid,hdid, case when is_new_user=1 then ‘新用户’ else ‘老用户’ end user_type
from eagle.hago_wh_dw_appheartbeat_uid_info_original_day
where dt=’
date′)t0leftjoin(selectuid,hdid,mediasource,adgroupfromhagouidafchannelwheredt=′
d
a
t
e
′
)
t
0
l
e
f
t
j
o
i
n
(
s
e
l
e
c
t
u
i
d
,
h
d
i
d
,
m
e
d
i
a
s
o
u
r
c
e
,
a
d
g
r
o
u
p
f
r
o
m
h
a
g
o
u
i
d
a
f
c
h
a
n
n
e
l
w
h
e
r
e
d
t
=
′
{date}’ group by uid, hdid, media_source, adgroup) t1
on t0.uid=t1.uid;
— tmp1
create temporary table tmp11 as
select ver,
sys,
media_source,
adgroup,
nation
,user_type
,count(distinct if(function_id=’show’,t1.uid,null)) show_num
,count(if(function_id=’show’,1,null)) show_cnt
,count(distinct if(function_id=’show’,t1.act_uid,null)) showed_num
,count(distinct if(function_id=’head_click’,t1.uid,null)) head_click_num
,count(if(function_id=’head_click’,1,null)) head_click_cnt
,count(distinct if(function_id=’say_hi’,t1.uid,null)) say_hi_num
,count(if(function_id=’say_hi’,1,null)) say_hi_cnt
,count(distinct if(function_id=’like_click’,t1.uid,null)) like_click_num
,count(if(function_id=’like_click’,1,null)) like_click_cnt
,count(distinct if(function_id=’show’ and ent_id=’1’,t1.uid,null)) im_show_num
,count(if(function_id=’show’ and ent_id=’1’,1,null)) im_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’2’,t1.uid,null)) dp_show_num
,count(if(function_id=’show’ and ent_id=’2’,1,null)) dp_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’3’,t1.uid,null)) message_show_num
,count(if(function_id=’show’ and ent_id=’3’,1,null)) message_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’4’,t1.uid,null)) friend_show_num
,count(if(function_id=’show’ and ent_id=’4’,1,null)) friend_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’5’,t1.uid,null)) add_show_num
,count(if(function_id=’show’ and ent_id=’5’,1,null)) add_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’6’,t1.uid,null)) apply_show_num
,count(if(function_id=’show’ and ent_id=’6’,1,null)) apply_show_cnt
,count(distinct if(function_id=’show’ and ent_id=’7’,t1.uid,null)) matched_show_num
,count(if(function_id=’show’ and ent_id=’7’,1,null)) matched_show_cnt
from (
select uid
,get_json_object(urldecode(moreinfo),’
.functionid′)functionid,getjsonobject(urldecode(moreinfo),′
.
f
u
n
c
t
i
o
n
i
d
′
)
f
u
n
c
t
i
o
n
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
u
r
l
d
e
c
o
d
e
(
m
o
r
e
i
n
f
o
)
,
′
.act_uid’) act_uid
,get_json_object(urldecode(moreinfo),’
.entid′)entidfromviewhuanju.viewothermbsdkprotocoloriginalwheredt=′
.
e
n
t
i
d
′
)
e
n
t
i
d
f
r
o
m
v
i
e
w
h
u
a
n
j
u
.
v
i
e
w
o
t
h
e
r
m
b
s
d
k
p
r
o
t
o
c
o
l
o
r
i
g
i
n
a
l
w
h
e
r
e
d
t
=
′
{date}’ and eventid=’20023793’ and uid>0
) t1
left join (
select uid, ver, sys, nation, user_type, media_source, adgroup from tmp0 where uid>0
) t2
on t1.uid=t2.uid
group by ver, sys, nation, user_type, media_source, adgroup;
create temporary table tmp1 AS
select coalesce(ver,’all’) ver
,coalesce(sys,’all’) sys
,coalesce(media_source,’all’) media_source
,coalesce(adgroup,’all’) adgroup
,nation
,coalesce(user_type,’全部用户’) user_type,
sum(show_num) show_num,
sum(show_cnt) show_cnt,
sum(showed_num) showed_num,
sum(head_click_num) head_click_num,
sum(head_click_cnt) head_click_cnt,
sum(say_hi_num) say_hi_num,
sum(say_hi_cnt) say_hi_cnt,
sum(like_click_num) like_click_num,
sum(like_click_cnt) like_click_cnt,
sum(im_show_num) im_show_num,
sum(im_show_cnt) im_show_cnt,
sum(dp_show_num) dp_show_num,
sum(dp_show_cnt) dp_show_cnt,
sum(message_show_num) message_show_num,
sum(message_show_cnt) message_show_cnt,
sum(friend_show_num) friend_show_num,
sum(friend_show_cnt) friend_show_cnt,
sum(add_show_num) add_show_num,
sum(add_show_cnt) add_show_cnt,
sum(apply_show_num) apply_show_num,
sum(apply_show_cnt) apply_show_cnt,
sum(matched_show_num) matched_show_num,
sum(matched_show_cnt) matched_show_cnt
from tmp11
group by nation,ver,sys,user_type,media_source,adgroup
grouping sets((nation),(nation,ver),(nation,sys),(nation,user_type),(nation,media_source),(nation,adgroup),
(nation,ver,sys),(nation,ver,user_type),(nation,ver,media_source),(nation,ver,adgroup),(nation,sys,user_type),(nation,sys,media_source),(nation,sys,adgroup),
(nation,user_type,media_source),(nation,user_type,adgroup),(nation,media_source,adgroup),
(nation,ver,sys,user_type),(nation,ver,sys,media_source),(nation,ver,sys,adgroup),(nation,ver,user_type,media_source),(nation,ver,user_type,adgroup),
(nation,ver,media_source,adgroup),(nation,sys,user_type,media_source),(nation,sys,user_type,adgroup),(nation,sys,media_source,adgroup),
(nation,user_type,media_source,adgroup),(nation,ver,sys,user_type,media_source),(nation,ver,sys,user_type,adgroup),(nation,ver,sys,media_source,adgroup),
(nation,ver,user_type,media_source,adgroup),(nation,sys,user_type,media_source,adgroup),(nation,ver,sys,user_type,media_source,adgroup));
– tmp2
create temporary table tmp2 as
select coalesce(ver,’all’) ver
,coalesce(sys,’all’) sys
,coalesce(media_source,’all’) media_source
,coalesce(adgroup,’all’) adgroup
,nation
,coalesce(user_type,’全部用户’) user_type
,sum(dau) dau
from
(select ver
,sys
,media_source
,adgroup
,nation
,user_type
,count(distinct uid) dau
from (
select uid, ver, sys, nation, user_type, media_source, adgroup from tmp0 where uid>0
) a
group by ver,sys,nation,user_type,media_source,adgroup) t1 —
group by ver,sys,nation,user_type,media_source,adgroup
grouping sets((nation),(nation,ver),(nation,sys),(nation,user_type),(nation,media_source),(nation,adgroup),
(nation,ver,sys),(nation,ver,user_type),(nation,ver,media_source),(nation,ver,adgroup),(nation,sys,user_type),(nation,sys,media_source),(nation,sys,adgroup),
(nation,user_type,media_source),(nation,user_type,adgroup),(nation,media_source,adgroup),
(nation,ver,sys,user_type),(nation,ver,sys,media_source),(nation,ver,sys,adgroup),(nation,ver,user_type,media_source),(nation,ver,user_type,adgroup),
(nation,ver,media_source,adgroup),(nation,sys,user_type,media_source),(nation,sys,user_type,adgroup),(nation,sys,media_source,adgroup),
(nation,user_type,media_source,adgroup),(nation,ver,sys,user_type,media_source),(nation,ver,sys,user_type,adgroup),(nation,ver,sys,media_source,adgroup),
(nation,ver,user_type,media_source,adgroup),(nation,sys,user_type,media_source,adgroup),(nation,ver,sys,user_type,media_source,adgroup));
— union all result
select ‘
date:y−m−d′dt,ver,sys,nation,usertype,′人数′datatype,mediasource,adgroup,shownum,showednum,headclicknum,sayhinum,likeclicknum,imshownum,dpshownum,messageshownum,friendshownum,addshownum,applyshownum,matchedshownumfromtmp1unionallselect‘
d
a
t
e
:
y
−
m
−
d
′
d
t
,
v
e
r
,
s
y
s
,
n
a
t
i
o
n
,
u
s
e
r
t
y
p
e
,
′
人
数
′
d
a
t
a
t
y
p
e
,
m
e
d
i
a
s
o
u
r
c
e
,
a
d
g
r
o
u
p
,
s
h
o
w
n
u
m
,
s
h
o
w
e
d
n
u
m
,
h
e
a
d
c
l
i
c
k
n
u
m
,
s
a
y
h
i
n
u
m
,
l
i
k
e
c
l
i
c
k
n
u
m
,
i
m
s
h
o
w
n
u
m
,
d
p
s
h
o
w
n
u
m
,
m
e
s
s
a
g
e
s
h
o
w
n
u
m
,
f
r
i
e
n
d
s
h
o
w
n
u
m
,
a
d
d
s
h
o
w
n
u
m
,
a
p
p
l
y
s
h
o
w
n
u
m
,
m
a
t
c
h
e
d
s
h
o
w
n
u
m
f
r
o
m
t
m
p
1
u
n
i
o
n
a
l
l
s
e
l
e
c
t
‘
{date:y-m-d}’ dt
,ver
,sys
,nation
,user_type
,’次数’ data_type
,media_source
,adgroup
,show_cnt
,show_cnt showed_cnt
,head_click_cnt
,say_hi_cnt
,like_click_cnt
,im_show_cnt
,dp_show_cnt
,message_show_cnt
,friend_show_cnt
,add_show_cnt
,apply_show_cnt
,matched_show_cnt
from tmp1
union all
select ‘
date:y−m−d′dt,t1.ver,t1.sys,t1.nation,t1.usertype,′人数占比′datatype,t1.mediasource,t1.adgroup,concat(round(shownum/dau∗100,2),′,nullshowednum,concat(round(headclicknum/shownum∗100,2),′,concat(round(sayhinum/shownum∗100,2),′,concat(round(likeclicknum/shownum∗100,2),′,concat(round(imshownum/shownum∗100,2),′,concat(round(dpshownum/shownum∗100,2),′,concat(round(messageshownum/shownum∗100,2),′,concat(round(friendshownum/shownum∗100,2),′,concat(round(addshownum/shownum∗100,2),′,concat(round(applyshownum/shownum∗100,2),′,concat(round(matchedshownum/shownum∗100,2),′fromtmp1t1leftjointmp2t2ont1.ver=t2.verandt1.sys=t2.sysandt1.nation=t2.nationandt1.usertype=t2.usertypeandt1.mediasource=t2.mediasourceandt1.adgroup=t2.adgroupunionallselect‘
d
a
t
e
:
y
−
m
−
d
′
d
t
,
t
1.
v
e
r
,
t
1.
s
y
s
,
t
1.
n
a
t
i
o
n
,
t
1.
u
s
e
r
t
y
p
e
,
′
人
数
占
比
′
d
a
t
a
t
y
p
e
,
t
1.
m
e
d
i
a
s
o
u
r
c
e
,
t
1.
a
d
g
r
o
u
p
,
c
o
n
c
a
t
(
r
o
u
n
d
(
s
h
o
w
n
u
m
/
d
a
u
∗
100
,
2
)
,
′
,
n
u
l
l
s
h
o
w
e
d
n
u
m
,
c
o
n
c
a
t
(
r
o
u
n
d
(
h
e
a
d
c
l
i
c
k
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
s
a
y
h
i
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
l
i
k
e
c
l
i
c
k
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
i
m
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
d
p
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
m
e
s
s
a
g
e
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
f
r
i
e
n
d
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
a
d
d
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
a
p
p
l
y
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
,
c
o
n
c
a
t
(
r
o
u
n
d
(
m
a
t
c
h
e
d
s
h
o
w
n
u
m
/
s
h
o
w
n
u
m
∗
100
,
2
)
,
′
f
r
o
m
t
m
p
1
t
1
l
e
f
t
j
o
i
n
t
m
p
2
t
2
o
n
t
1.
v
e
r
=
t
2.
v
e
r
a
n
d
t
1.
s
y
s
=
t
2.
s
y
s
a
n
d
t
1.
n
a
t
i
o
n
=
t
2.
n
a
t
i
o
n
a
n
d
t
1.
u
s
e
r
t
y
p
e
=
t
2.
u
s
e
r
t
y
p
e
a
n
d
t
1.
m
e
d
i
a
s
o
u
r
c
e
=
t
2.
m
e
d
i
a
s
o
u
r
c
e
a
n
d
t
1.
a
d
g
r
o
u
p
=
t
2.
a
d
g
r
o
u
p
u
n
i
o
n
a
l
l
s
e
l
e
c
t
‘
{date:y-m-d}’ dt
,ver
,sys
,nation
,user_type
,’次数占比’ data_type
,media_source
,adgroup
,null show_num
,null showed_num
,concat(round(head_click_cnt/show_cnt*100,2),’%’) head_click_num
,concat(round(say_hi_cnt/show_cnt*100,2),’%’) say_hi_num
,concat(round(like_click_cnt/show_cnt*100,2),’%’) like_click_num
,concat(round(im_show_cnt/show_cnt*100,2),’%’) im_show_num
,concat(round(dp_show_cnt/show_cnt*100,2),’%’) dp_show_num
,concat(round(message_show_cnt/show_cnt*100,2),’%’) message_show_num
,concat(round(friend_show_cnt/show_cnt*100,2),’%’) friend_show_num
,concat(round(add_show_cnt/show_cnt*100,2),’%’) add_show_num
,concat(round(apply_show_cnt/show_cnt*100,2),’%’) apply_show_num
,concat(round(matched_show_cnt/show_cnt*100,2),’%’) matched_show_num
from tmp1 t1