clkCidUidRate.py 3.07 KB
Newer Older
1
# -*- coding: UTF-8 -*-
2
from utils import con_sql,get_yesterday_date
3 4 5


class ClkCidUidRate(object):
6
	def __init__(self, platform, cid_type):
7 8
		"""
		platform : 'all';'ios';'android'
9
		cid_type : 'diary';'answer';'question';"everything"...
10 11
		"""
		if platform == "ios":
12
			self.platform = "='App Store'"
13
		elif platform == "android":
14
			self.platform = "!='App Store'"
15 16
		else:
			self.platform = " is not null"
17
		if cid_type == "everything":
18 19 20
			self.cid_type = " is not null"
		else:
			self.cid_type = "='" + cid_type + "'"
21 22 23 24 25 26

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

高雅喆's avatar
高雅喆 committed
33
		sql_imp = "select count(distinct(device_id)) from data_feed_exposure \
高雅喆's avatar
高雅喆 committed
34
		where stat_date = '{0}' \
35
		and device_type{1} \
36
		and cid_type{2}".format(get_yesterday_date(),self.platform,self.cid_type)
37 38
		imp_count = con_sql(sql_imp)[0][0]

39 40
		clk_rate = round(clk_count/imp_count,4)
		return [platform,clk_count,imp_count,clk_rate]
41 42 43 44 45 46 47 48 49


	def result2file(self, result_lst, fpath):
		pass



def main():
	#1.点击diary用户占比
50 51 52
	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("安卓")
53
	click_diary_result = [click_diary_all,click_diary_ios,click_diary_android]
54
	print("已获取点击diary用户占比")
55
	#2.点击answer用户占比
56 57 58
	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("安卓")
59
	click_answer_result = [click_answer_all,click_answer_ios,click_answer_android]
60 61 62 63 64 65 66
	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用户占比")
67
	#4.有点击用户占比
68 69 70
	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("安卓")
71 72
	click_everything_result = [click_everything_all,click_everything_ios,click_everything_android]
	print("已获取有点击用户占比")
73 74 75 76 77



if __name__ == '__main__':
	main()
78