# -*- 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()