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
# -*- coding: UTF-8 -*-
import pymysql
import datetime
import pandas as pd
DIRECTORY_PATH="/data2/ffm/"
def get_data():
conn2db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='eagle')
cursor = conn2db.cursor()
sql = "select device_id from eagle.ffm_diary_queue where device_id regexp '[5|6]$'"
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(list(result))
df.to_csv("/data2/ffm/10.3-10.9device_id.csv",index=None)
device_id = tuple(df[0].values.tolist())
cursor.close()
return device_id
def ctr():
device_id = get_data()
print("点击数：" + str(len(device_id)))
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
cursor = db.cursor()
a = "2018-10-0"
for i in range(3, 10):
sql_exp = "select count(cid) from data_feed_exposure " \
"where cid_type = 'diary' and stat_date <= '2018-10-09' and stat_date >= '2018-10-03' and " \
"device_id in {}".format(device_id)
cursor.execute(sql_exp)
exp = cursor.fetchone()[0]
print("曝光数："+str(exp))
if exp != 0:
print("点击率："+str(len(device_id)/exp))
if __name__ == "__main__":
ctr()