clkCidUidRate.py

# -*- coding: UTF-8 -*-
from utils import con_sql,get_yesterday_date


class ClkCidUidRate(object):
	def __init__(self, platform, cid_type):
		"""
		platform : 'all';'ios';'android'
		cid_type : 'diary';'answer';'question';"everything"...
		"""
		if platform == "ios":
			self.platform = "='App Store'"
		elif platform == "android":
			self.platform = "!='App Store'"
		else:
			self.platform = " is not null"
		if cid_type == "everything":
			self.cid_type = " is not null"
		else:
			self.cid_type = "='" + cid_type + "'"

	def get_clk_cid_uid_rate(self, platform):
		"""
		platform : "所有";"苹果","安卓"   #方便显示
		rtype : list
		"""
		sql_clk = "select count(distinct(device_id)) from data_feed_click \
		where stat_date = '{0}' \
		and device_type{1} \
		and cid_type{2}".format(get_yesterday_date(),self.platform.replace(' ','') if self.platform[-2]=='e' else self.platform,self.cid_type)
		clk_count = con_sql(sql_clk)[0][0]

		sql_imp = "select count(distinct(device_id)) from data_feed_exposure \
		where stat_date = '{0}' \
		and device_type{1} \
		and cid_type{2}".format(get_yesterday_date(),self.platform,self.cid_type)
		imp_count = con_sql(sql_imp)[0][0]

		clk_rate = round(clk_count/imp_count,4)
		return [platform,clk_count,imp_count,clk_rate]


	def result2file(self, result_lst, fpath):
		pass



def main():
	#1.点击diary用户占比
	click_diary_all = ClkCidUidRate("all","diary").get_clk_cid_uid_rate("所有")
	click_diary_ios = ClkCidUidRate("ios","diary").get_clk_cid_uid_rate("苹果")
	click_diary_android = ClkCidUidRate("android","diary").get_clk_cid_uid_rate("安卓")
	click_diary_result = [click_diary_all,click_diary_ios,click_diary_android]
	print("已获取点击diary用户占比")
	#2.点击answer用户占比
	click_answer_all = ClkCidUidRate("all","answer").get_clk_cid_uid_rate("所有")
	click_answer_ios = ClkCidUidRate("ios","answer").get_clk_cid_uid_rate("苹果")
	click_answer_android = ClkCidUidRate("android","answer").get_clk_cid_uid_rate("安卓")
	click_answer_result = [click_answer_all,click_answer_ios,click_answer_android]
	print("已获取点击answer用户占比")
	#3.点击question用户占比(曝光表里cid类型没有question，因此下面的曝光数为0，0不能作分母)
	#click_question_all = ClkCidUidRate(1,"all","question").get_clk_cid_uid_rate("所有")
	#click_question_ios = ClkCidUidRate(1,"ios","question").get_clk_cid_uid_rate("苹果")
	#click_question_android = ClkCidUidRate(1,"android","question").get_clk_cid_uid_rate("安卓")
	#click_question_result = [click_question_all,click_question_ios,click_question_android]
	#print("已获取点击question用户占比")
	#4.有点击用户占比
	click_everything_all = ClkCidUidRate("all","everything").get_clk_cid_uid_rate("所有")
	click_everything_ios = ClkCidUidRate("ios","everything").get_clk_cid_uid_rate("苹果")
	click_everything_android = ClkCidUidRate("android","everything").get_clk_cid_uid_rate("安卓")
	click_everything_result = [click_everything_all,click_everything_ios,click_everything_android]
	print("已获取有点击用户占比")



if __name__ == '__main__':
	main()