getClickZeroUidDetail.py

import datetime
import pymysql


#一周之前的timestamp(7)
my_date1 = datetime.date.today() - datetime.timedelta(days=7)
my_tm1 = int(my_date1.strftime("%s"))
#二周之前的timestamp(14)
my_date2 = datetime.date.today() - datetime.timedelta(days=14)
my_tm2 = int(my_date2.strftime("%s"))
#一个月之前的timestamp(30)
my_date3 = datetime.date.today() - datetime.timedelta(days=30)
my_tm3 = int(my_date3.strftime("%s"))
#两个月之前的timestamp(60)
my_date4 = datetime.date.today() - datetime.timedelta(days=60)
my_tm4 = int(my_date4.strftime("%s"))
#三个月之前的timestamp(90)
my_date5 = datetime.date.today() - datetime.timedelta(days=90)
my_tm5 = int(my_date5.strftime("%s"))


def get_click_zero_uid_count(platform):
	"""
	platform : "ios","android","all"
	rtype : dict
	"""
	if platform == "ios":
		platform = "='App Store'"
	elif platform == "android":
		platform = "!='App Store'"
	else:
		platform = " is not null"
	db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
	cursor = db.cursor()
	sql = "select '0-7' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id not in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {2})) \
	union all \
	select '7-14' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id not in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {3}) \
	and device_id in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {2})) \
	union all \
	select '14-30' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id not in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {4}) \
	and device_id in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {3})) \
	union all \
	select '30-60' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id not in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {5}) \
	and device_id in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {4})) \
	union all \
	select '60-90' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id not in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {6}) \
	and device_id in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {5})) \
	union all \
	select '90+' as label,count(distinct(device_id)) from data_feed_exposure2 \
	where device_type{0} \
	and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
	and device_id not in \
	(select distinct(device_id) from data_feed_click2 \
	where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		and device_type{1}) \
	and device_id in \
	(select distinct(device_id) \
	from data_feed_exposure2 \
	where device_id in \
	(select distinct(device_id) from data_feed_exposure2 \
		where time < {6}))".format(platform,platform.replace(' ','') if platform[-2]=='e' else platform,my_tm1,my_tm2,my_tm3,my_tm4,my_tm5)
	cursor.execute(sql)
	result = cursor.fetchall()
	db.close()
	dct = {}
	for i in result:
		dct[i[0]] = i[1]
	return dct

	
if __name__ == '__main__':
	no_click_uid_detail_all = get_click_zero_uid_count("all")
	no_click_uid_detail_ios = get_click_zero_uid_count("ios")
	no_click_uid_detail_android = get_click_zero_uid_count("android")