1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
--push接收数据
select day_id as `日期`
,device_os_type as `设备类型`
,active_type as `活跃类型`
,NVL(CONCAT(ROUND(sum(case when push_type = '101' then click_dev_num end)/sum(case when push_type = '101' then received_dev_num end)*100,2),'%'),0) as `ctr帖子点击率`
,NVL(CONCAT(ROUND(sum(case when push_type = '102' then click_dev_num end)/sum(case when push_type = '102' then received_dev_num end)*100,2),'%'),0) as `ctr日记点击率`
,NVL(CONCAT(ROUND(sum(case when push_type = '103' then click_dev_num end)/sum(case when push_type = '103' then received_dev_num end)*100,2),'%'),0) as `ctr回答点击率`
,NVL(CONCAT(ROUND(sum(case when push_type = '111' then click_dev_num end)/sum(case when push_type = '111' then received_dev_num end)*100,2),'%'),0) as `非ctr帖子点击率`
,NVL(CONCAT(ROUND(sum(case when push_type = '112' then click_dev_num end)/sum(case when push_type = '112' then received_dev_num end)*100,2),'%'),0) as `非ctr日记点击率`
,NVL(CONCAT(ROUND(sum(case when push_type = '113' then click_dev_num end)/sum(case when push_type = '113' then received_dev_num end)*100,2),'%'),0) as `非ctr回答点击率`
,NVL(ROUND(sum(case when push_type = '101' then received_dev_num end)/sum(case when push_type = '111' then received_dev_num end),2),0) as `ctr帖子接收设备数/非ctr帖子接收设备数`
,NVL(ROUND(sum(case when push_type = '102' then received_dev_num end)/sum(case when push_type = '112' then received_dev_num end),2),0) as `ctr日记接收设备数/非ctr日记接收设备数`
,NVL(ROUND(sum(case when push_type = '103' then received_dev_num end)/sum(case when push_type = '113' then received_dev_num end),2),0) as `ctr回答接收设备数/非ctr回答接收设备数`
,NVL(sum(case when push_type = '101' then received_dev_num end),0) as `ctr帖子接收设备数`
,NVL(sum(case when push_type = '102' then received_dev_num end),0) as `ctr日记接收设备数`
,NVL(sum(case when push_type = '103' then received_dev_num end),0) as `ctr回答接收设备数`
,NVL(sum(case when push_type = '111' then received_dev_num end),0) as `非ctr帖子接收设备数`
,NVL(sum(case when push_type = '112' then received_dev_num end),0) as `非ctr日记接收设备数`
,NVL(sum(case when push_type = '113' then received_dev_num end),0) as `非ctr回答接收设备数`
from
(
SELECT t1.partition_day as day_id
,t1.device_os_type as device_os_type
,'老活' as active_type
,t1.push_type as push_type
,count(distinct t1.device_id) as received_dev_num
,count(t1.msg_id) as received_msg_num
,count(distinct t2.cl_id) as click_dev_num
,count(t2.msg_id) as click_msg_num
FROM
(--接收设备数
SELECT partition_day
,device_os_type
,push_type
,a.device_id
,a.msg_id
FROM
(
select partition_day
,device_id,msg_id
from bl.bl_et_bg_trackingpushlog_inc_d
where partition_day=regexp_replace(DATE_SUB(current_date,1) ,'-','')
group by partition_day,device_id,msg_id
)a
JOIN
(
select device_id,device_os_type
from online.ml_device_history_detail
where partition_date=regexp_replace(DATE_SUB(current_date,1) ,'-','')
and first_channel_source_type not in ('yqxiu1','yqxiu2','yqxiu3','yqxiu4','yqxiu5','mxyc1','mxyc2','mxyc3'
,'wanpu','jinshan','jx','maimai','zhuoyi','huatian','suopingjingling','mocha','mizhe','meika','lamabang'
,'js-az1','js-az2','js-az3','js-az4','js-az5','jfq-az1','jfq-az2','jfq-az3','jfq-az4','jfq-az5','toufang1'
,'toufang2','toufang3','toufang4','toufang5','toufang6','TF-toufang1','TF-toufang2','TF-toufang3','TF-toufang4'
,'TF-toufang5','tf-toufang1','tf-toufang2','tf-toufang3','tf-toufang4','tf-toufang5','benzhan','promotion_aso100'
,'promotion_qianka','promotion_xiaoyu','promotion_dianru','promotion_malioaso','promotion_malioaso-shequ'
,'promotion_shike','promotion_julang_jl03','promotion_zuimei')
AND first_channel_source_type not like 'promotion\_jf\_%'
)b
on a.device_id=b.device_id
JOIN
(
select msg_id,push_type,time_stamp
from online.tl_hdfs_push2_new_view --增量表
where partition_date=regexp_replace(DATE_SUB(current_date,1) ,'-','')
group by msg_id,push_type,time_stamp
union all
SELECT msg_id,regexp_replace(labels['event'], '\\s+', '') AS push_type,time_stamp
FROM online.tl_hdfs_push2_task_view
WHERE partition_date=regexp_replace(DATE_SUB(current_date,1) ,'-','')
AND labels['event_type'] = 'push'
group by msg_id,regexp_replace(labels['event'], '\\s+', ''),time_stamp
)c
on a.msg_id=c.msg_id
left join
(
select
partition_date
,device_id
from online.ml_device_day_active_status
where partition_date=regexp_replace(DATE_SUB(current_date,1) ,'-','')
AND active_type in ('1','2','3')
and device_os_type='ios'
)d
on a.partition_day=d.partition_date and a.device_id=d.device_id
where d.device_id is null
group by partition_day
,device_os_type
,push_type
,a.device_id
,a.msg_id
)t1
left join
(--点击设备数
select partition_date
,cl_id,params['message_id'] as msg_id,time_stamp
from online.bl_hdfs_maidian_updates
where partition_date=regexp_replace(DATE_SUB(current_date,1) ,'-','')
and action='notification_open'
group by partition_date,cl_id,params['message_id'],time_stamp
)t2
on t2.partition_date=t1.partition_day
and t2.msg_id=t1.msg_id
and t2.cl_id=t1.device_id
group by t1.partition_day
,t1.device_os_type
,t1.push_type
)t
where day_id>='20200626'
group by day_id
,device_os_type
,active_type
order by `日期`,`设备类型`,`活跃类型`