getClickZeroUidRate.py 2.76 KB
Newer Older
1
# -*- coding: UTF-8 -*-
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
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 获取所有平台的0点击用户占比
def get_all_click_zero_rate():
	sql = "select count(distinct(device_id)) from data_feed_click where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	click_zero_count = con_sql(sql)
	click_zero_count = click_zero_count[0][0]
	sql = "select count(distinct(device_id)) from data_feed_exposure where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	impression_zero_count = con_sql(sql)
	impression_zero_count = impression_zero_count[0][0]
	click_zero_count = impression_zero_count-click_zero_count
	all_click_zero_rate = click_zero_count / impression_zero_count
25
	return ["所有",click_zero_count,impression_zero_count,round(all_click_zero_rate,4)]
26 27 28 29 30 31 32 33 34 35 36 37 38



#2 获取ios平台的0点击用户占比
def get_ios_click_zero_rate():
	sql = "select count(distinct(device_id)) from data_feed_click where device_type='AppStore' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	click_zero_count = con_sql(sql)
	click_zero_count = click_zero_count[0][0]
	sql = "select count(distinct(device_id)) from data_feed_exposure where device_type='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	impression_zero_count = con_sql(sql)
	impression_zero_count = impression_zero_count[0][0]
	click_zero_count = impression_zero_count-click_zero_count
	ios_click_zero_rate = click_zero_count / impression_zero_count
39
	return ["苹果",click_zero_count,impression_zero_count,round(ios_click_zero_rate,4)]
40 41 42 43 44 45 46 47 48 49 50 51


#3 获取安卓平台的0点击用户占比
def get_android_click_zero_rate():
	sql = "select count(distinct(device_id)) from data_feed_click where device_type!='AppStore' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	click_zero_count = con_sql(sql)
	click_zero_count = click_zero_count[0][0]
	sql = "select count(distinct(device_id)) from data_feed_exposure where device_type!='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)"
	impression_zero_count = con_sql(sql)
	impression_zero_count = impression_zero_count[0][0]
	click_zero_count = impression_zero_count-click_zero_count
	android_click_zero_rate = click_zero_count / impression_zero_count
52
	return ["安卓",click_zero_count,impression_zero_count,round(android_click_zero_rate,4)]
53 54 55 56 57 58 59



if __name__ == "__main__":
	all_click_zero_rate = get_all_click_zero_rate()
	ios_click_zero_rate = get_ios_click_zero_rate()
	android_click_zero_rate = get_android_click_zero_rate()