func.py

from utils import con_sql,get_yesterday_date
from getClickZeroUidDetail import get_click_zero_uid_count
from getRegisterUidDetail import get_register_uid_count



#获取各个平台下的活跃用户点击率
def get_activate_uid_ctr(platform):
	"""
	platform : 'all';'ios';'android'
	rtype : list
	"""
	if platform == "ios":
		platform = "='App Store'"
	elif platform == "android":
		platform = "!='App Store'"
	else:
		platform = " is not null"
	sql_clk = "select count(device_id) from data_feed_click \
	where stat_date = '{0}' \
	and device_type{1}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform)
	clk_count = con_sql(sql_clk)[0][0]
	sql_imp = "select count(device_id) from data_feed_exposure \
	where stat_date = '{0}' \
	and device_id in \
	(select device_id from data_feed_click \
	where stat_date = '{0}' \
	and device_type{1}) \
	and device_type{2}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform, platform)
	imp_count = con_sql(sql_imp)[0][0]
	clk_rate = round(clk_count/imp_count, 4 )
	if platform == "='App Store'":
		platform = "苹果"
	elif platform == "!='App Store'":
		platform = "安卓"
	else:
		platform = "所有"
	return [platform, clk_count, imp_count, clk_rate]

#获取活跃用户平均每天曝光次数
def get_activate_uid_imp_times(city):
	"""
	city : 'beijing';'all'
	rtype : list
	"""
	if city == "beijing":
		city = "='beijing'"
	else:
		city = " is not null"
	sql_uid = "select count(distinct(device_id)) from data_feed_click \
	where stat_date = '{0}' \
	and city_id{1}".format(get_yesterday_date(),city)
	sql_uid_count = con_sql(sql_uid)[0][0]
	sql_imp = "select count(device_id) from data_feed_exposure \
	where device_id in \
		(select device_id from data_feed_click \
		where stat_date = '{0}' \
		and city_id{1}) \
	and stat_date = '{0}' \
	and city_id{1}".format(get_yesterday_date(),city)
	sql_imp_times = con_sql(sql_imp)[0][0]
	if city == "='beijing'":
		city = "北京"
	else:
		city = "所有"
	return [city,sql_uid_count,sql_imp_times,round(sql_imp_times/sql_uid_count,2)]


#获取无点击用户数分布(=无点击用户∩激活用户数 / 激活用户数) ；并且根据平台和激活日记来分
def get_click_zero_uid_rate_detail(platform):
	"""
	platform : "ios","android","all"
	rtype : dict
	"""
	dct1 = get_click_zero_uid_count(platform)
	dct2 = get_register_uid_count()
	result = {}
	for k in dct1:
		result[k] = round(dct1[k]/dct2[k],4)
	return result


#获取 (用户点击次数 : 独立用户数)
def get_click_times_to_count_uid():
	"""
	rtype : tuple
	"""
	sql = "select times,count(device_id) \
	from (select device_id,count(cid_type) as times \
		from data_feed_click \
		where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
		group by device_id) as t \
	group by times order by times"
	result = con_sql(sql)
	return result