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
import pandas as pd
import pymysql
import datetime
def con_sql(db,sql):
cursor = db.cursor()
cursor.execute(sql)
result = cursor.fetchone()[0]
return result
def get_ctr():
db = pymysql.connect(host='172.16.40.158', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
yesterday = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
print(yesterday)
sql = "select count(*) from data_feed_exposure_precise where stat_date = '{}' and cid_type = 'diary'".format(yesterday)
exposures = con_sql(db, sql)
sql = "select count(*) from data_feed_click where stat_date = '{}' and cid_type = 'diary'".format(yesterday)
clicks = con_sql(db, sql)
db.close()
print(exposures)
print(clicks)
print(clicks/exposures)
def all_con_sql(db,sql):
cursor = db.cursor()
cursor.execute(sql)
result = list(cursor.fetchall())
return result
def get_feed_ctr():
db = pymysql.connect(host='172.16.40.158', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
sql = "select date_str,diary_exp,diary_click,post_exp,post_click,qa_exp,qa_click from feed_exp_click"
df = pd.DataFrame(all_con_sql(db,sql))
db.close()
print(df.head(2))
df = df.rename(columns={0: "date_str",1:"diary_exp",2:"diary_click",3:"post_exp",4:"post_click",
5:"qa_exp",6:"qa_click"})
for i in ["date_str","diary_exp","diary_click","post_exp","post_click","qa_exp","qa_click"]:
df[i] = df[i].astype("int")
df["total_exp"] = df["diary_exp"] + df["post_exp"] + df["qa_exp"]
df["total_click"] = df["diary_click"] + df["post_click"] + df["qa_click"]
df["total_ctr"] = df["total_click"]/df["total_exp"]
df["diary_ctr"] = df["diary_click"]/df["diary_exp"]
df["post_ctr"] = df["post_click"]/df["post_exp"]
df["qa_ctr"] = df["qa_click"]/df["qa_exp"]
print(df.head(2))
df.to_csv("/home/gmuser/ctr.csv",index = False)
if __name__ == "__main__":
get_feed_ctr()