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
# -*- coding: UTF-8 -*-
from utils import con_sql,get_yesterday_date
class CidRate(object):
def __init__(self, platform, cid_type):
"""
platform : 'all';'ios';'android'
cid_type : 'diary';'answer';'question'...
"""
if platform == "ios":
self.platform = "='App Store'"
elif platform == "android":
self.platform = "!='App Store'"
else:
self.platform = " is not null"
self.cid_type = cid_type
def get_cid_clk_rate(self, platform):
"""
platform : "所有";"苹果","安卓" #方便显示
rtype : list
"""
sql_cid = "select count(cid) 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)
cid_clk_count = con_sql(sql_cid)[0][0]
sql_all = "select count(cid) from data_feed_click \
where stat_date = '{0}' \
and device_type{1}".format(get_yesterday_date(), self.platform.replace(' ','') if self.platform[-2]=='e' else self.platform)
all_clk_count = con_sql(sql_all)[0][0]
cid_clk_rate = round(cid_clk_count/all_clk_count,4)
return [platform,cid_clk_count,all_clk_count,cid_clk_rate]
def get_cid_imp_rate(self, platform):
"""
platform : "所有";"苹果","安卓" #方便显示
rtype : list
"""
sql_cid = "select count(cid) 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)
cid_imp_count = con_sql(sql_cid)[0][0]
sql_all = "select count(cid) from data_feed_exposure \
where stat_date = '{0}' \
and device_type{1}".format(get_yesterday_date(), self.platform)
all_imp_count = con_sql(sql_all)[0][0]
cid_imp_rate = round(cid_imp_count/all_imp_count,4)
return [platform,cid_imp_count,all_imp_count,cid_imp_rate]
def main():
answer_imp_rate_all = CidRate("all","answer").get_cid_imp_rate("所有")
answer_imp_rate_ios = CidRate("ios","answer").get_cid_imp_rate("苹果")
answer_imp_rate_android = CidRate("android","answer").get_cid_imp_rate("安卓")
answer_imp_rate_result = [answer_imp_rate_all,answer_imp_rate_ios,answer_imp_rate_android]
if __name__ == '__main__':
main()