# -*- 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_click_diary_rate(): sql = "select count(distinct(device_id)) from data_feed_click where cid_type='diary' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" click_diary_count = con_sql(sql) click_diary_count = click_diary_count[0][0] sql = "select count(distinct(device_id)) from data_feed_exposure where cid_type='diary' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" impression_diary_count = con_sql(sql) impression_diary_count = impression_diary_count[0][0] all_click_diary_rate = click_diary_count / impression_diary_count return ["所有",click_diary_count,impression_diary_count,round(all_click_diary_rate,4)] #2 获取ios平台的点击问答用户占比 def get_ios_click_diary_rate(): sql = "select count(distinct(device_id)) from data_feed_click where cid_type='diary' and device_type='AppStore' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" click_diary_count = con_sql(sql) click_diary_count = click_diary_count[0][0] sql = "select count(distinct(device_id)) from data_feed_exposure where cid_type='diary' and device_type='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" impression_diary_count = con_sql(sql) impression_diary_count = impression_diary_count[0][0] ios_click_diary_rate = click_diary_count / impression_diary_count return ["苹果",click_diary_count,impression_diary_count,round(ios_click_diary_rate,4)] #3 获取安卓平台的点击问答用户占比 def get_android_click_diary_rate(): sql = "select count(distinct(device_id)) from data_feed_click where cid_type='diary' and device_type!='AppStore' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" click_diary_count = con_sql(sql) click_diary_count = click_diary_count[0][0] sql = "select count(distinct(device_id)) from data_feed_exposure where cid_type='diary' and device_type!='App Store' and from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day)" impression_diary_count = con_sql(sql) impression_diary_count = impression_diary_count[0][0] android_click_diary_rate = click_diary_count / impression_diary_count return ["安卓",click_diary_count,impression_diary_count,round(android_click_diary_rate,4)] if __name__ == "__main__": all_click_diary_rate = get_all_click_diary_rate() ios_click_diary_rate = get_ios_click_diary_rate() android_click_diary_rate = get_android_click_diary_rate()