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
# -*- coding: UTF-8 -*-
import pymysql
def con_sql(sql):
# 从数据库的表里获取数据
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
cursor = db.cursor()
cursor.execute(sql)
result = cursor.fetchall()
db.close()
return result
#1 获取所有平台的有点击用户点击率
def get_all_click_one_rate():
sql = "select count(device_id) from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_type is not null"
click_one_count = con_sql(sql)
click_one_count = click_one_count[0][0]
sql = "select count(device_id) from data_feed_exposure where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_id in (select device_id from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day))"
impression_one_count = con_sql(sql)
impression_one_count = impression_one_count[0][0]
all_click_one_rate = click_one_count / impression_one_count
return ["所有",click_one_count,impression_one_count,round(all_click_one_rate,4)]
#2 获取ios平台的有点击用户点击率
def get_ios_click_one_rate():
sql = "select count(device_id) from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_type='AppStore'"
click_one_count = con_sql(sql)
click_one_count = click_one_count[0][0]
sql = "select count(device_id) from data_feed_exposure where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_id in (select device_id from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_type='AppStore') and device_type='App Store'"
impression_one_count = con_sql(sql)
impression_one_count = impression_one_count[0][0]
ios_click_one_rate = click_one_count / impression_one_count
return ["苹果",click_one_count,impression_one_count,round(ios_click_one_rate,4)]
#3 获取安卓平台的有点击用户点击率
def get_android_click_one_rate():
sql = "select count(device_id) from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_type!='AppStore'"
click_one_count = con_sql(sql)
click_one_count = click_one_count[0][0]
sql = "select count(device_id) from data_feed_exposure where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_id in (select device_id from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) and device_type!='AppStore') and device_type!='App Store'"
impression_one_count = con_sql(sql)
impression_one_count = impression_one_count[0][0]
android_click_one_rate = click_one_count / impression_one_count
return ["安卓",click_one_count,impression_one_count,round(android_click_one_rate,4)]
if __name__ == "__main__":
all_click_one_rate = get_all_click_one_rate()
ios_click_one_rate = get_ios_click_one_rate()
android_click_one_rate = get_android_click_one_rate()