cidRate.py

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



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

	def get_cid_clk_rate(self, platform):
		"""
		platform : "所有";"苹果","安卓"   #方便显示
		rtype : list
		"""
		sql_cid = "select count(cid) 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)
		cid_clk_count = con_sql(sql_cid)[0][0]
		sql_all = "select count(cid) from data_feed_click \
		where stat_date = '{0}' \
		and device_type{1}".format(get_yesterday_date(), self.platform.replace(' ','') if self.platform[-2]=='e' else self.platform)
		all_clk_count = con_sql(sql_all)[0][0]
		cid_clk_rate = round(cid_clk_count/all_clk_count,4)
		return [platform,cid_clk_count,all_clk_count,cid_clk_rate]


	def get_cid_imp_rate(self, platform):
		"""
		platform : "所有";"苹果","安卓"   #方便显示
		rtype : list
		"""
		sql_cid = "select count(cid) 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)
		cid_imp_count = con_sql(sql_cid)[0][0]
		sql_all = "select count(cid) from data_feed_exposure \
		where stat_date = '{0}' \
		and device_type{1}".format(get_yesterday_date(), self.platform)
		all_imp_count = con_sql(sql_all)[0][0]
		cid_imp_rate = round(cid_imp_count/all_imp_count,4)
		return [platform,cid_imp_count,all_imp_count,cid_imp_rate]
		


def main():
	answer_imp_rate_all = CidRate("all","answer").get_cid_imp_rate("所有")
	answer_imp_rate_ios = CidRate("ios","answer").get_cid_imp_rate("苹果")
	answer_imp_rate_android = CidRate("android","answer").get_cid_imp_rate("安卓")
	answer_imp_rate_result = [answer_imp_rate_all,answer_imp_rate_ios,answer_imp_rate_android]


if __name__ == '__main__':
	main()