ctr-56.py 3.34 KB
Newer Older
张彦钊's avatar
张彦钊 committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14
# -*- coding: UTF-8 -*-
import pymysql
import datetime
import pandas as pd


def get_yesterday_date():
    today = datetime.date.today()
    yesterday = today - datetime.timedelta(days=1)
    yesterday = yesterday.strftime("%Y-%m-%d")
    print(yesterday)
    return yesterday


15 16
def get_black_user():
    conn2db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
张彦钊's avatar
张彦钊 committed
17
    cursor = conn2db.cursor()
18
    sql = "select distinct device_id from blacklist"
张彦钊's avatar
张彦钊 committed
19 20
    cursor.execute(sql)
    result = cursor.fetchall()
21
    black_user = pd.DataFrame(list(result))[0].values.tolist()
张彦钊's avatar
张彦钊 committed
22
    cursor.close()
23
    conn2db.close()
24
    return black_user
25

26 27 28

def get_data():
    conn2db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='eagle')
29
    cursor = conn2db.cursor()
30
    sql = "select distinct device_id from ffm_diary_queue_temp where device_id regexp '[5|6]$'"
31 32
    cursor.execute(sql)
    result = cursor.fetchall()
33
    device = pd.DataFrame(list(result))[0].values.tolist()
34 35 36 37
    cursor.close()
    conn2db.close()

    device = tuple(set(device)-set(black))
张彦钊's avatar
张彦钊 committed
38
    return device
张彦钊's avatar
张彦钊 committed
39 40


41 42 43
def ctr_all():
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    cursor = db.cursor()
张彦钊's avatar
张彦钊 committed
44
    sql_active = "select distinct device_id from data_feed_exposure " \
45
                 "where cid_type = 'diary'" \
46
                 "and device_id regexp'[5|6]$' and stat_date = '{}';".format(date)
47
    cursor.execute(sql_active)
48
    result = cursor.fetchall()
张彦钊's avatar
张彦钊 committed
49
    tail56 = pd.DataFrame(list(result))[0].values.tolist()
50
    tail56 = set(tail56)-set(black)
张彦钊's avatar
张彦钊 committed
51 52
    print("当天尾号5或6活跃用户总数:")
    print(len(tail56))
53

54
    cover = len(tail56&set(device_id))
55 56
    print("当天尾号5或6活跃用户覆盖数:")
    print(cover)
张彦钊's avatar
张彦钊 committed
57
    cover_percent = format(cover / len(tail56), ".6f")
58 59
    print("当天尾号5或6活跃用户覆盖率:")
    print(cover_percent)
60

张彦钊's avatar
张彦钊 committed
61
    return len(tail56),cover,cover_percent
62 63


张彦钊's avatar
张彦钊 committed
64
def ctr():
张彦钊's avatar
张彦钊 committed
65 66
    sql_click = "select count(cid) from data_feed_click " \
              "where (cid_type = 'diary' or cid_type = 'diary_video') " \
67
                "and stat_date = '{}' and device_id in {};".format(date,device_id)
张彦钊's avatar
张彦钊 committed
68 69 70 71
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    cursor = db.cursor()
    cursor.execute(sql_click)
    click = cursor.fetchone()[0]
72
    print("实验用户点击数:"+str(click))
张彦钊's avatar
张彦钊 committed
73
    sql_exp = "select count(cid) from data_feed_exposure " \
张彦钊's avatar
张彦钊 committed
74
              "where cid_type = 'diary'" \
75
              "and stat_date = '{}' and device_id in {};".format(date,device_id)
张彦钊's avatar
张彦钊 committed
76 77
    cursor.execute(sql_exp)
    exp = cursor.fetchone()[0]
78 79
    print("实验用户曝光数:"+str(exp))
    print("实验用户点击率:"+str(click/exp))
张彦钊's avatar
张彦钊 committed
80

张彦钊's avatar
张彦钊 committed
81
    return click,exp,format(click/exp,".6f")
张彦钊's avatar
张彦钊 committed
82 83 84


def rate2file():
张彦钊's avatar
张彦钊 committed
85
    output_path = DIRECTORY_PATH + "56ctr.csv"
张彦钊's avatar
张彦钊 committed
86
    with open(output_path,'a+') as f:
87
        line = date.replace('-', '')+','+str(temp_data[0])+','+str(temp_data[1])+','+str(temp_data[2])+\
张彦钊's avatar
张彦钊 committed
88
               ","+str(data[0])+","+str(data[1])+","+str(data[2])+'\n'
张彦钊's avatar
张彦钊 committed
89 90
        f.write(line)

张彦钊's avatar
张彦钊 committed
91

张彦钊's avatar
张彦钊 committed
92
if __name__ == "__main__":
张彦钊's avatar
张彦钊 committed
93
    DIRECTORY_PATH = "/data/ffm/"
张彦钊's avatar
张彦钊 committed
94
    date = get_yesterday_date()
95
    black = get_black_user()
96
    device_id = get_data()
张彦钊's avatar
张彦钊 committed
97
    temp_data = ctr()
98
    data = ctr_all()
张彦钊's avatar
张彦钊 committed
99
    rate2file()