# -*- coding: UTF-8 -*- import pymysql def con_sql(sql): # 从数据库的表里获取数据 db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test') cursor = db.cursor() cursor.execute(sql) result = cursor.fetchall() db.close() return result #1 获取所有平台的问答曝光占比 def get_all_answer_imp_rate(): sql = "select count(cid) from data_feed_exposure where cid_type='answer' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" answer_imp_count = con_sql(sql) answer_imp_count = answer_imp_count[0][0] sql = "select count(cid) from data_feed_exposure where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" all_imp_count = con_sql(sql) all_imp_count = all_imp_count[0][0] all_answer_imp_rate = answer_imp_count / all_imp_count return ["所有",answer_imp_count,all_imp_count,round(all_answer_imp_rate,4)] #2 获取ios平台的问答曝光占比 def get_ios_answer_imp_rate(): sql = "select count(cid) from data_feed_exposure where cid_type='answer' and device_type='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" answer_imp_count = con_sql(sql) answer_imp_count = answer_imp_count[0][0] sql = "select count(cid) from data_feed_exposure where device_type='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" all_imp_count = con_sql(sql) all_imp_count = all_imp_count[0][0] ios_answer_imp_rate = answer_imp_count / all_imp_count return ["苹果",answer_imp_count,all_imp_count,round(ios_answer_imp_rate,4)] #3 获取安卓平台的问答曝光占比 def get_android_answer_imp_rate(): sql = "select count(cid) from data_feed_exposure where cid_type='answer' and device_type!='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" answer_imp_count = con_sql(sql) answer_imp_count = answer_imp_count[0][0] sql = "select count(cid) from data_feed_exposure where device_type!='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" all_imp_count = con_sql(sql) all_imp_count = all_imp_count[0][0] android_answer_imp_rate = answer_imp_count / all_imp_count return ["安卓",answer_imp_count,all_imp_count,round(android_answer_imp_rate,4)] if __name__ == "__main__": all_answer_imp_rate = get_all_answer_imp_rate() ios_answer_imp_rate = get_ios_answer_imp_rate() android_answer_imp_rate = get_android_answer_imp_rate()