hypothesis_test.py 10.9 KB
#! -*- coding: utf8 -*-
import pandas as pd
from scipy.stats import ttest_ind
from scipy.stats import levene
import datetime
from utils import con_sql
from decimal import *

#########推荐策略前后统计指标假设检验(t检验)###############

#自动获取昨日日期
def get_yesterday_date():
	#自动获取昨天的日期,如"2018-08-08"
	"""
	:rtype : str
	"""
	today = datetime.date.today()
	yesterday = today - datetime.timedelta(days=1)
	yesterday = yesterday.strftime("%Y-%m-%d")
	return yesterday
yesterday=get_yesterday_date()
print(yesterday)
#自动获取10日前的日期
def get_somedate():
	#自动获取10日前的日期,如"2018-07-28"
	"""
	:rtype : str
	"""
	today = datetime.date.today()
	someday = today - datetime.timedelta(days=10)
	someday = someday.strftime("%Y-%m-%d")
	return someday
ten_days=get_somedate()
print(ten_days)

#获取最近10天的数据
def DATA_recently(x,y,z,q,t):
    ten_days = get_somedate()
    sql_cid = "select {0}/{1} as {2} from {3} \
    where stat_date >='{4}' ".format(x,y,z,q,t)
    CVR_DATA_recently = con_sql(sql_cid)
    return CVR_DATA_recently
#
# #获取固定时间的10天的数据
def DATA_fixed(x,y,z,q):
    sql_cid = "select {0}/{1} as {2} from {3} \
    where stat_date >='2018-11-17'  and stat_date<='2018-11-26' group by stat_date".format(x,y,z,q)
    CVR_DATA_fixed = con_sql(sql_cid)
    return CVR_DATA_fixed
#
# #新用户cvr
x_crv_new_temp=DATA_recently("diary_meigou_newUser","diary_clk_newUser","CVR_new","diary_meigou_crv",ten_days)
x_crv_new=[float(str(Decimal(x_crv_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_crv_new_temp))]
y_crv_new_temp=DATA_fixed("diary_meigou_newUser","diary_clk_newUser","CVR_new","diary_meigou_crv")
y_crv_new=[float(str(Decimal(y_crv_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_crv_new_temp))]

# #老用户cvr
x_crv_old_temp=DATA_recently("diary_meigou_oldUser","diary_clk_oldUser","CVR_old","diary_meigou_crv",ten_days)
x_crv_old=[float(str(Decimal(x_crv_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_crv_old_temp))]
y_crv_old_temp=DATA_fixed("diary_meigou_oldUser","diary_clk_oldUser","CVR_old","diary_meigou_crv")
y_crv_old=[float(str(Decimal(y_crv_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_crv_old_temp))]

#
# #新用户ct-cvr
x_ctcrv_new_temp=DATA_recently("diary_meigou_newUser","diary_exp_newUser","CT_CVR_new","diary_meigou_crv",ten_days)
x_ctcrv_new=[float(str(Decimal(x_ctcrv_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctcrv_new_temp))]
y_ctcrv_new_temp=DATA_fixed("diary_meigou_newUser","diary_exp_newUser","CT_CVR_new","diary_meigou_crv")
y_ctcrv_new=[float(str(Decimal(y_ctcrv_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctcrv_new_temp))]
#
# #老用户ct-cvr
x_ctcrv_old_temp=DATA_recently("diary_meigou_oldUser","diary_exp_oldUser","CT_CVR_old","diary_meigou_crv",ten_days)
x_ctcrv_old =[float(str(Decimal(x_ctcrv_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctcrv_old_temp))]
y_ctcrv_old_temp=DATA_fixed("diary_meigou_oldUser","diary_exp_oldUser","CT_CVR_old","diary_meigou_crv")
y_ctcrv_old=[float(str(Decimal(y_ctcrv_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctcrv_old_temp))]
#
# #新用户ctr(page_view)
x_ctr_new_temp=DATA_recently("clk_count_newUser_all","imp_count_newUser_all","ctr_new","bug_Recommendation_strategy_newUser",ten_days)
x_ctr_new=[float(str(Decimal(x_ctr_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctr_new_temp))]
y_ctr_new_temp=DATA_fixed("clk_count_newUser_all","imp_count_newUser_all","ctr_new","bug_Recommendation_strategy_newUser")
y_ctr_new=[float(str(Decimal(y_ctr_new_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctr_new_temp))]
# #
# #老用户ctr(page_view)
x_ctr_old_temp=DATA_recently("clk_count_oldUser_all","imp_count_oldUser_all","ctr_old","bug_Recommendation_strategy_temp",ten_days)
x_ctr_old=[float(str(Decimal(x_ctr_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctr_old_temp))]
y_ctr_old_temp=DATA_fixed("clk_count_oldUser_all","imp_count_oldUser_all","ctr_old","bug_Recommendation_strategy_temp")
y_ctr_old=[float(str(Decimal(y_ctr_old_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctr_old_temp))]
#
# #新用户ctr(on_click_diary_card)
x_ctr_new_o_temp=DATA_recently("clk_count_newUser_all_a","imp_count_newUser_all","ctr_new","on_click_diary_card",ten_days)
x_ctr_new_o=[float(str(Decimal(x_ctr_new_o_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctr_new_o_temp))]
y_ctr_new_o_temp=DATA_fixed("clk_count_newUser_all_a","imp_count_newUser_all","ctr_new","on_click_diary_card")
y_ctr_new_o=[float(str(Decimal(y_ctr_new_o_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctr_new_o_temp))]
#
# #老用户ctr(on_click_diary_card)
x_ctr_old_o_temp=DATA_recently("clk_count_oldUser_all_a","imp_count_oldUser_all","ctr_old","on_click_diary_card",ten_days)
x_ctr_old_o=[float(str(Decimal(x_ctr_old_o_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(x_ctr_old_o_temp))]
y_ctr_old_o_temp=DATA_fixed("clk_count_oldUser_all_a","imp_count_oldUser_all","ctr_old","on_click_diary_card")
y_ctr_old_o=[float(str(Decimal(y_ctr_old_o_temp[i][0]).quantize(Decimal('0.0000')))) for i in range(len(y_ctr_old_o_temp))]
# #
#
#
def t_test(x,y): #进行t检验

    #策略前的数据,赋值给x,策略后的数据赋值给y,均采用10日内数据
    #检验数据方差是否齐性
    a=levene(x,y)
    p_value=a[1] #结果若p_value>0.05,则认为两组数据方差是相等的,否则两组数据方差是不等的

    if p_value>0.05: #认为数据方差具有齐性,equal_var=ture
        t_test=ttest_ind(x,y,equal_var=True)
        t_p_value=t_test[1]
        # print(t_p_value)
        if t_p_value>0.05:
            print("策略前后两组数据无显著性差异,即该指标没有显著提升,p_value:{}" .format(t_p_value))
        else:
            print("策略前后两组数据有显著性差异,即该指标获得显著提升,p_value:{}" .format(t_p_value))
    else: #认为数据方差不具有齐性,equal_var=false
        t_test = ttest_ind(x, y, equal_var=False)
        t_p_value = t_test[1]
        # print(t_p_value)
        if t_p_value > 0.05:
            print("策略前后两组数据无显著性差异,即该指标没有显著提升,p_value:{}" .format(t_p_value))
        else:
            print("策略前后两组数据有显著性差异,即该指标获得显著提升,p_value:{}" .format(t_p_value))
#
# ###假设检验,判断是否具有显著性
#
#新用户cvr假设检验
print("【1】新用户CVR假设检验结果:")
crv_new_ttest=t_test(x_crv_new,y_crv_new)
#老用户cvr假设检验
print("【2】老用户CVR假设检验结果:")
crv_old_ttest=t_test(x_crv_old,y_crv_old)
#
#新用户ct_cvr假设检验
print("【3】新用户CT-CVR假设检验结果:")
ctcrv_new_ttest=t_test(x_ctcrv_new,y_ctcrv_new)
# #老用户ct_cvr假设检验
print("【4】老用户CT-CVR假设检验结果:")
ctcrv_old_ttest=t_test(x_ctcrv_old,y_ctcrv_old)
#
#
#新用户ctr假设检验
print("【5】新用户CTR假设检验结果:")
ctr_new_ttest=t_test(x_ctr_new,y_ctr_new)
#老用户ctr假设检验
print("【6】老用户CTR假设检验结果:")
ctr_old_ttest=t_test(x_ctr_old,y_ctr_old)

#新用户ctr(on_click_diary_card)假设检验
print("【7】新用户CTR假设检验(on_click_diary_card)结果:")
ctr_new_o_ttest=t_test(x_ctr_new_o,y_ctr_new_o)
#老用户ctr(on_click_diary_card)假设检验
print("【8】老用户CTR假设检验(on_click_diary_card)结果:")
ctr_old_o_ttest=t_test(x_ctr_old_o,y_ctr_old_o)
#
# ###############推荐策略不变的情况下数据假设检验##############
#
# #1 计算每日指标卡方检验
#
# #自动获取5日前的日期
def get_fivedate():
	#自动获取10日前的日期,如"2018-07-28"
	"""
	:rtype : str
	"""
	today = datetime.date.today()
	someday = today - datetime.timedelta(days=5)
	someday = someday.strftime("%Y-%m-%d")
	return someday
five_days=get_fivedate()

#获取最近5天的数据
def chi_DATA_recently(x,y,z,q,t1,t2):
    sql_cid = "select AVG({0}+{1}),AVG({2}) from {3} \
    where stat_date >= '{4}' and stat_date < '{5}' ".format(x,y,z,q,t1,t2)
    CVR_DATA_recently = con_sql(sql_cid)[0]
    return CVR_DATA_recently
def chi_DATA_yesterday(x,y,z,q,t1):
    sql_cid = "select {0}+{1},{2} from {3} where stat_date='{4}'  ".format(x,y,z,q,t1)
    CVR_DATA_yesterday = con_sql(sql_cid)[0]
    return CVR_DATA_yesterday

#整理数据
def data_cal(x,y):
    x_a = [x[0], x[1] - x[0]]
    y_a=[y[0], y[1] - y[0]]
    a_df=pd.DataFrame({'原':x_a,'测':y_a})
    return a_df

def chi_cal(data):
    data['共计'] = data.apply(lambda x: x.sum(), axis=1)
    # print(data)
    data.loc['共计'] = data.apply(lambda x: x.sum())
    print(data)
    t1=data.iloc[0]
    t2=data.iloc[1]
    t11_count=t1[0]
    print("t11:{}".format(t11_count))
    t12_count=t1[1]
    print("t12:{}".format(t12_count))
    t21_count=t2[0]
    print("t21:{}".format(t21_count))
    t22_count=t2[1]
    print("t22:{}".format(t22_count))

    ###理论值计算
    temp1=data.loc['共计']
    print("共计:{}".format(temp1))
    rate1=temp1[0]/temp1[2]
    print("rate1:{}".format(rate1))
    rate2=temp1[1]/temp1[2]
    print("rate2:{}".format(rate2))
    temp2=data.iloc[2]
    t11_theory=temp2[0]*rate1
    print("t11_theory:{}".format(t11_theory))
    t12_theory=temp2[1]*rate1
    print("t12_theory:{}".format(t12_theory))
    t21_theory = temp2[0]*rate2
    print("t21_theory:{}".format(t21_theory))
    t22_theory = temp2[1]*rate2
    print("t22_theory:{}".format(t22_theory))
    #计算卡方值
    X=(((t11_count-t11_theory)**2)/t11_theory)+(((t12_count-t12_theory)**2)/t12_theory)+(((t21_count-t21_theory)**2)/t21_theory)+(((t22_count-t22_theory)**2)/t22_theory)
    print("卡方值为:{}".format(X))
    #计算自由度
    v=(len(data)-1)*(data.columns.size-1)
    #查表发现阈值为3.84
    if X>3.84:
        print("数据波动较大,超出正常波动范围,95%可能性属于指标显著变化,请关注")
    else:
        print("数据波动较小,95%可能性属于正常波动范围")

#精准点击曝光数据(首页精选日记本列表on_click_diary_card)
chi_ctr_precise_recently=chi_DATA_recently("clk_count_oldUser_all_a","clk_count_oldUser_all_b","imp_count_oldUser_all_precise","on_click_diary_card",five_days,yesterday)
temp1=[float(str(Decimal(chi_ctr_precise_recently[i]).quantize(Decimal('0.0')))) for i in range(len(chi_ctr_precise_recently))]
# print(temp1)
chi_ctr_precise_yesterday=chi_DATA_yesterday("clk_count_oldUser_all_a","clk_count_oldUser_all_b","imp_count_oldUser_all_precise","on_click_diary_card",yesterday)
temp2=[float(chi_ctr_precise_yesterday[i]) for i in range(len(chi_ctr_precise_yesterday))]
# print(temp2)
tst=data_cal(temp1,temp2)
print(tst)
chi_cal(tst)


# print(chi_ctr_precise_recently)
# print(chi_ctr_precise_yesterday)



# chi_cvr_new=
# chi_cvr_old=
#
# chi_ctcvr_new=
# chi_ctcvr_old=
#
#
#
# def chi_cal(data):