import pandas as pd from model.DataBaseUtils import CkUtils,MysqlUtils import numpy as np from model.DateUtils import DateUtils import matplotlib.pyplot as plt from scipy.optimize import curve_fit import time plt.rcParams['font.sans-serif']=['SimHei'] plt.rcParams['axes.unicode_minus'] = False ck=CkUtils() db = MysqlUtils() du=DateUtils() def run(): sql="""select book,type, round(sum(w1)/sum(if(dt>=subtractDays(today(),5),0,a1)),2) wa1, round(sum(w2)/sum(if(dt>=subtractDays(today(),12),0,a1)),2) wa2, round(sum(w3)/sum(if(dt>=subtractDays(today(),19),0,a1)),2) wa3, round(sum(w4)/sum(if(dt>=subtractDays(today(),26),0,a1)),2) wa4, round(sum(w5)/sum(if(dt>=subtractDays(today(),33),0,a1)),2) wa5, round(sum(w6)/sum(if(dt>=subtractDays(today(),40),0,a1)),2) wa6, round(sum(w7)/sum(if(dt>=subtractDays(today(),47),0,a1)),2) wa7, round(sum(w8)/sum(if(dt>=subtractDays(today(),54),0,a1)),2) wa8, round(sum(w9)/sum(if(dt>=subtractDays(today(),61),0,a1)),2) wa9, round(sum(w10)/sum(if(dt>=subtractDays(today(),68),0,a1)),2) wa10, round(sum(w11)/sum(if(dt>=subtractDays(today(),75),0,a1)),2) wa11, round(sum(w12)/sum(if(dt>=subtractDays(today(),82),0,a1)),2) wa12, round(sum(w13)/sum(if(dt>=subtractDays(today(),89),0,a1)),2) wa13, round(sum(w14)/sum(if(dt>=subtractDays(today(),96),0,a1)),2) wa14, round(sum(w15)/sum(if(dt>=subtractDays(today(),103),0,a1)),2) wa15, round(sum(w16)/sum(if(dt>=subtractDays(today(),110),0,a1)),2) wa16, round(sum(w17)/sum(if(dt>=subtractDays(today(),117),0,a1)),2) wa17, round(sum(w18)/sum(if(dt>=subtractDays(today(),124),0,a1)),2) wa18, round(sum(w19)/sum(if(dt>=subtractDays(today(),131),0,a1)),2) wa19, round(sum(w20)/sum(if(dt>=subtractDays(today(),138),0,a1)),2) wa20, round(sum(w21)/sum(if(dt>=subtractDays(today(),145),0,a1)),2) wa21, round(sum(w22)/sum(if(dt>=subtractDays(today(),152),0,a1)),2) wa22, round(sum(w23)/sum(if(dt>=subtractDays(today(),159),0,a1)),2) wa23, round(sum(w24)/sum(if(dt>=subtractDays(today(),166),0,a1)),2) wa24, round(sum(w25)/sum(if(dt>=subtractDays(today(),173),0,a1)),2) wa25, round(sum(w26)/sum(if(dt>=subtractDays(today(),180),0,a1)),2) wa26, round(sum(w27)/sum(if(dt>=subtractDays(today(),187),0,a1)),2) wa27, round(sum(w28)/sum(if(dt>=subtractDays(today(),194),0,a1)),2) wa28, round(sum(w29)/sum(if(dt>=subtractDays(today(),201),0,a1)),2) wa29, round(sum(w30)/sum(if(dt>=subtractDays(today(),208),0,a1)),2) wa30, round(sum(w31)/sum(if(dt>=subtractDays(today(),215),0,a1)),2) wa31, round(sum(w32)/sum(if(dt>=subtractDays(today(),222),0,a1)),2) wa32, round(sum(w33)/sum(if(dt>=subtractDays(today(),229),0,a1)),2) wa33, round(sum(w34)/sum(if(dt>=subtractDays(today(),236),0,a1)),2) wa34, round(sum(w35)/sum(if(dt>=subtractDays(today(),243),0,a1)),2) wa35, round(sum(w36)/sum(if(dt>=subtractDays(today(),250),0,a1)),2) wa36, round(sum(w37)/sum(if(dt>=subtractDays(today(),257),0,a1)),2) wa37, round(sum(w38)/sum(if(dt>=subtractDays(today(),264),0,a1)),2) wa38, round(sum(w39)/sum(if(dt>=subtractDays(today(),271),0,a1)),2) wa39, round(sum(w40)/sum(if(dt>=subtractDays(today(),278),0,a1)),2) wa40, round(sum(w41)/sum(if(dt>=subtractDays(today(),285),0,a1)),2) wa41, round(sum(w42)/sum(if(dt>=subtractDays(today(),292),0,a1)),2) wa42, round(sum(w43)/sum(if(dt>=subtractDays(today(),299),0,a1)),2) wa43, round(sum(w44)/sum(if(dt>=subtractDays(today(),306),0,a1)),2) wa44, round(sum(w45)/sum(if(dt>=subtractDays(today(),313),0,a1)),2) wa45, round(sum(w46)/sum(if(dt>=subtractDays(today(),320),0,a1)),2) wa46, round(sum(w47)/sum(if(dt>=subtractDays(today(),327),0,a1)),2) wa47, round(sum(w48)/sum(if(dt>=subtractDays(today(),334),0,a1)),2) wa48, round(sum(w49)/sum(if(dt>=subtractDays(today(),341),0,a1)),2) wa49, round(sum(w50)/sum(if(dt>=subtractDays(today(),348),0,a1)),2) wa50, round(sum(w51)/sum(if(dt>=subtractDays(today(),355),0,a1)),2) wa51, round(sum(w52)/sum(if(dt>=subtractDays(today(),362),0,a1)),2) wa52 from dw_daily_channel a left outer join (select toDate(formatDateTime(reg_time,'%Y-%m-%d')) as dt, channel as channel, sum(if(subtractDays(date, 1)>=toDate(reg_time),0,amount)) as a1, if(dt=toDate(reg_time),0,amount)),null) as w1, if(dt=toDate(reg_time),0,amount)),null) as w2, if(dt=toDate(reg_time),0,amount)),null) as w3, if(dt=toDate(reg_time),0,amount)),null) as w4, if(dt=toDate(reg_time),0,amount)),null) as w5, if(dt=toDate(reg_time),0,amount)),null) as w6, if(dt=toDate(reg_time),0,amount)),null) as w7, if(dt=toDate(reg_time),0,amount)),null) as w8, if(dt=toDate(reg_time),0,amount)),null) as w9, if(dt=toDate(reg_time),0,amount)),null) as w10, if(dt=toDate(reg_time),0,amount)),null) as w11, if(dt=toDate(reg_time),0,amount)),null) as w12, if(dt=toDate(reg_time),0,amount)),null) as w13, if(dt=toDate(reg_time),0,amount)),null) as w14, if(dt=toDate(reg_time),0,amount)),null) as w15, if(dt=toDate(reg_time),0,amount)),null) as w16, if(dt=toDate(reg_time),0,amount)),null) as w17, if(dt=toDate(reg_time),0,amount)),null) as w18, if(dt=toDate(reg_time),0,amount)),null) as w19, if(dt=toDate(reg_time),0,amount)),null) as w20, if(dt=toDate(reg_time),0,amount)),null) as w21, if(dt=toDate(reg_time),0,amount)),null) as w22, if(dt=toDate(reg_time),0,amount)),null) as w23, if(dt=toDate(reg_time),0,amount)),null) as w24, if(dt=toDate(reg_time),0,amount)),null) as w25, if(dt=toDate(reg_time),0,amount)),null) as w26, if(dt=toDate(reg_time),0,amount)),null) as w27, if(dt=toDate(reg_time),0,amount)),null) as w28, if(dt=toDate(reg_time),0,amount)),null) as w29, if(dt=toDate(reg_time),0,amount)),null) as w30, if(dt=toDate(reg_time),0,amount)),null) as w31, if(dt=toDate(reg_time),0,amount)),null) as w32, if(dt=toDate(reg_time),0,amount)),null) as w33, if(dt=toDate(reg_time),0,amount)),null) as w34, if(dt=toDate(reg_time),0,amount)),null) as w35, if(dt=toDate(reg_time),0,amount)),null) as w36, if(dt=toDate(reg_time),0,amount)),null) as w37, if(dt=toDate(reg_time),0,amount)),null) as w38, if(dt=toDate(reg_time),0,amount)),null) as w39, if(dt=toDate(reg_time),0,amount)),null) as w40, if(dt=toDate(reg_time),0,amount)),null) as w41, if(dt=toDate(reg_time),0,amount)),null) as w42, if(dt=toDate(reg_time),0,amount)),null) as w43, if(dt=toDate(reg_time),0,amount)),null) as w44, if(dt=toDate(reg_time),0,amount)),null) as w45, if(dt=toDate(reg_time),0,amount)),null) as w46, if(dt=toDate(reg_time),0,amount)),null) as w47, if(dt=toDate(reg_time),0,amount)),null) as w48, if(dt=toDate(reg_time),0,amount)),null) as w49, if(dt=toDate(reg_time),0,amount)),null) as w50, if(dt=toDate(reg_time),0,amount)),null) as w51, if(dt=toDate(reg_time),0,amount)),null) as w52 from order a where reg_time>'2019-03-18 00:00:00' group by toDate(formatDateTime(reg_time,'%Y-%m-%d')),channel) b using (dt,channel) where cost>0 and a1>0 and book!='' group by book,type """ col=['book','first_amount','start','end','w1','w2','w3','w4','w5','w6','w7','w8','w9','w10','w11','w12','w13','w14','w15','w16','w17','w18','w19','w20','w21','w22','w23','w24','w25','w26','w27','w28','w29','w30','w31','w32','w33','w34','w35','w36','w37','w38','w39','w40','w41','w42','w43','w44','w45','w46','w47','w48','w49','w50','w51','w52',] # df =ck.getData_pd(sql,col=col) # print(df) # df.to_csv('./book.csv',encoding='utf-8') df = ck.execute(sql) # print(df) li = [list(i) for i in df] # print(li) li2=[] for i in li: w = [] w.append(i[0]) w.append(i[1]) x=1 for j in i[2:]: if j is None: break if j>=x: w.append(j) x=j else: break li2.append(w) # print(li2) li4=[] for i in li2: li3=[] if len(i)<5: continue # print(i[0]) y = np.array(i[2:]) x = np.array([n for n in range(2,len(i))]) # print(x,y) try: popt, pcov = curve_fit(func, x, y) # print(popt[0]) if popt[0]<0: continue # print(i[0]) # yvals = func(x, popt[0], popt[1], popt[2]) # 拟合y值 li3.append(i[0]) li3.append(i[1]) li3.append(round(func(52, popt[0], popt[1], popt[2]),2)) # di[i[0]]=round(func(52, popt[0], popt[1], popt[2]),2) # print(di) li4.append(li3) # 画图 # plot1 = plt.plot(x, y, 's', label='original values') # plot2 = plt.plot(x, yvals, 'r', label='polyfit values') # plt.xlabel('x') # plt.ylabel('y') # plt.legend(loc=4) # 指定legend的位置右下角 # plt.title(i[0]) # plt.show() # time.sleep(1) except: print(i[0],i[1],'找不到解') print(li4) print(li4.__len__()) update_data(li4) def func(x,a,b,k): return 1/(a*b**x+k) def update_data(data): dt =du.getNow() for i in data: book = i[0] type= i[1] mult = i[2] db.dm.execute(f"update src_book_info set annual_mult={mult} where book='{book}' and type ='{type}'") if __name__ == '__main__': # x="" # for i in range(1,53): # x+=f"'w{i}'," # print(x) run() # print(fuc(1,2,3,4))