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