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
# -*- coding: UTF-8 -*-
from utils import con_sql
class CidRate(object):
def __init__(self, platform, cid_type, ndays=1):
"""
ndays : 1;2;3;4.. #The number of days from the current time
platform : 'all';'ios';'android'
cid_type : 'diary';'answer';'question'...
"""
self.ndays = ndays
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 from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -{0} day) \
and device_type{1} \
and cid_type='{2}'".format(self.ndays,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 from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -{0} day) \
and device_type{1}".format(self.ndays, 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 from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -{0} day) \
and device_type{1} and cid_type='{2}'".format(self.ndays,self.platform,self.cid_type)
cid_imp_count = con_sql(sql_cid)[0][0]
sql_all = "select count(cid) from data_feed_exposure \
where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -{0} day) \
and device_type{1}".format(self.ndays, 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()