from utils import con_sql,get_yesterday_date from getClickZeroUidDetail import get_click_zero_uid_count from getRegisterUidDetail import get_register_uid_count #获取各个平台下的活跃用户点击率 def get_activate_uid_ctr(platform): """ platform : 'all';'ios';'android' rtype : list """ if platform == "ios": platform = "='App Store'" elif platform == "android": platform = "!='App Store'" else: platform = " is not null" sql_clk = "select count(device_id) from data_feed_click \ where stat_date = '{0}' \ and device_type{1}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform) clk_count = con_sql(sql_clk)[0][0] sql_imp = "select count(device_id) from data_feed_exposure \ where stat_date = '{0}' \ and device_id in \ (select device_id from data_feed_click \ where stat_date = '{0}' \ and device_type{1}) \ and device_type{2}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform, platform) imp_count = con_sql(sql_imp)[0][0] clk_rate = round(clk_count/imp_count, 4 ) if platform == "='App Store'": platform = "苹果" elif platform == "!='App Store'": platform = "安卓" else: platform = "所有" return [platform, clk_count, imp_count, clk_rate] #获取活跃用户平均每天曝光次数 def get_activate_uid_imp_times(city): """ city : 'beijing';'all' rtype : list """ if city == "beijing": city = "='beijing'" else: city = " is not null" sql_uid = "select count(distinct(device_id)) from data_feed_click \ where stat_date = '{0}' \ and city_id{1}".format(get_yesterday_date(),city) sql_uid_count = con_sql(sql_uid)[0][0] sql_imp = "select count(device_id) from data_feed_exposure \ where device_id in \ (select device_id from data_feed_click \ where stat_date = '{0}' \ and city_id{1}) \ and stat_date = '{0}' \ and city_id{1}".format(get_yesterday_date(),city) sql_imp_times = con_sql(sql_imp)[0][0] if city == "='beijing'": city = "北京" else: city = "所有" return [city,sql_uid_count,sql_imp_times,round(sql_imp_times/sql_uid_count,2)] #获取无点击用户数分布(=无点击用户∩激活用户数 / 激活用户数) ;并且根据平台和激活日记来分 def get_click_zero_uid_rate_detail(platform): """ platform : "ios","android","all" rtype : dict """ dct1 = get_click_zero_uid_count(platform) dct2 = get_register_uid_count() result = {} for k in dct1: result[k] = round(dct1[k]/dct2[k],4) return result #获取 (用户点击次数 : 独立用户数) def get_click_times_to_count_uid(): """ rtype : tuple """ sql = "select times,count(device_id) \ from (select device_id,count(cid_type) as times \ from data_feed_click \ where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \ group by device_id) as t \ group by times order by times" result = con_sql(sql) return result