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
# -*- coding: UTF-8 -*-
from utils import con_sql,get_yesterday_date
class ClkCidUidRate(object):
def __init__(self, platform, cid_type):
"""
platform : 'all';'ios';'android'
cid_type : 'diary';'answer';'question';"everything"...
"""
if platform == "ios":
self.platform = "='App Store'"
elif platform == "android":
self.platform = "!='App Store'"
else:
self.platform = " is not null"
if cid_type == "everything":
self.cid_type = " is not null"
else:
self.cid_type = "='" + cid_type + "'"
def get_clk_cid_uid_rate(self, platform):
"""
platform : "所有";"苹果","安卓" #方便显示
rtype : list
"""
sql_clk = "select count(distinct(device_id)) from data_feed_click \
where stat_date = '{0}' \
and device_type{1} \
and cid_type{2}".format(get_yesterday_date(),self.platform.replace(' ','') if self.platform[-2]=='e' else self.platform,self.cid_type)
clk_count = con_sql(sql_clk)[0][0]
sql_imp = "select count(distinct(device_id)) from data_feed_exposure \
where stat_date = '{0}' \
and device_type{1} \
and cid_type{2}".format(get_yesterday_date(),self.platform,self.cid_type)
imp_count = con_sql(sql_imp)[0][0]
clk_rate = round(clk_count/imp_count,4)
return [platform,clk_count,imp_count,clk_rate]
def result2file(self, result_lst, fpath):
pass
def main():
#1.点击diary用户占比
click_diary_all = ClkCidUidRate("all","diary").get_clk_cid_uid_rate("所有")
click_diary_ios = ClkCidUidRate("ios","diary").get_clk_cid_uid_rate("苹果")
click_diary_android = ClkCidUidRate("android","diary").get_clk_cid_uid_rate("安卓")
click_diary_result = [click_diary_all,click_diary_ios,click_diary_android]
print("已获取点击diary用户占比")
#2.点击answer用户占比
click_answer_all = ClkCidUidRate("all","answer").get_clk_cid_uid_rate("所有")
click_answer_ios = ClkCidUidRate("ios","answer").get_clk_cid_uid_rate("苹果")
click_answer_android = ClkCidUidRate("android","answer").get_clk_cid_uid_rate("安卓")
click_answer_result = [click_answer_all,click_answer_ios,click_answer_android]
print("已获取点击answer用户占比")
#3.点击question用户占比(曝光表里cid类型没有question，因此下面的曝光数为0，0不能作分母)
#click_question_all = ClkCidUidRate(1,"all","question").get_clk_cid_uid_rate("所有")
#click_question_ios = ClkCidUidRate(1,"ios","question").get_clk_cid_uid_rate("苹果")
#click_question_android = ClkCidUidRate(1,"android","question").get_clk_cid_uid_rate("安卓")
#click_question_result = [click_question_all,click_question_ios,click_question_android]
#print("已获取点击question用户占比")
#4.有点击用户占比
click_everything_all = ClkCidUidRate("all","everything").get_clk_cid_uid_rate("所有")
click_everything_ios = ClkCidUidRate("ios","everything").get_clk_cid_uid_rate("苹果")
click_everything_android = ClkCidUidRate("android","everything").get_clk_cid_uid_rate("安卓")
click_everything_result = [click_everything_all,click_everything_ios,click_everything_android]
print("已获取有点击用户占比")
if __name__ == '__main__':
main()