python+powerbi+mysql：销量到底在怎样增长？(滚动线性回归)
本文要解答的问题为，当拿到销量数据后，需要判断：
1、昨天的销量相比之前销量的平均增长速度是下降了还是上升了？
2、销量的平均增长情况在发生怎样的变化，每多一天正常应该增加多少销量，实际有没有达到？每天的增长率是在上升还是下降？
先看下最终效果，非常残暴。
整体思路如下。
1、对日期进行索引排序，按照索引与销量建立线性回归方程；
2、参与线性回归建模的数据为昨天之前的前15天数据(实际可按照业务情况选择天数长短)，如昨天是3月30日，那么参与线性回归建模的则为3月15日到3月29日的数据，训练后输出回归方程，得出截距、自变量回归系数、平均绝对误差mae；
3、按照这个回归方程对昨天的数据进行预测，即对3月30日得出预测销量；
4、昨天销量实际增长率 =(昨天的销量-前天销量)/前天的销量；昨天销量预测增长率 = （预测销量-前天销量）/前天销量；昨天销量实际增长率较预测增长率 = 昨天销量实际增长率-昨天销量预测增长率；
5、建模数据滚动变化，模型每天定时运行一次。
6、保存N个昨天的预测值、截距、回归系数、mae。
7、回归系数代表日期每增长一天，销量的平均增长值；截距的变化代表销量的平均总量变化，以此判断销量的平均总量增长情况；mae的变化用来衡量模型预测效果。
数据情况如下：为了实现上述效果，需要提前建立预测销量列、截距列、回归系数列、平均绝对误差列、索引列。
步骤一：从mysql读取数据。
import pandas as pdimport timeimport pymysqlfrom sklearn.metrics import mean_absolute_errorfrom sklearn.linear_model import LinearRegression as LRimport numpy as npconn = pymysql.connect(host="localhost",port=3306,user="root",password="123456",database="sys",charset="utf8")sql = "select * from pred;"data = pd.read_sql(sql,conn)df = data.copy()colums_temp = df.columnsdf.columns = ["日期","销量","预测销量","截距","回归系数","平均绝对误差","索引"]df["索引"] = df.index+1df
步骤二：训练线性回归模型并对昨天的销量进行预测，保存昨天这个模型对应的预测销量、截距、回归系数、平均绝对误差。
这里需要注意的是，每次训练模型的时候都需要把建模的索引变换为1-15，预测值的索引变换为16。
now = time.localtime()this_datetime = pd.to_datetime(time.strftime("%Y-%m-%d", now))+pd.Timedelta(days = -0) #今天last_datetime = this_datetime+pd.Timedelta(days = -2) #前天today_ymd = this_datetime.strftime("%Y-%m-%d") #转换为年月日last_ymd = last_datetime.strftime("%Y-%m-%d") #转换为年月日df["日期"] = df["日期"].astype("str")last_index = df["索引"][df["日期"] == last_ymd] #前天的索引yes_index = df["索引"][df["日期"] == last_ymd] #昨天的索引df_train = df.loc[(df["索引"]<=list(last_index)[0])&(df["索引"]>=list(last_index)[0]-14)] #获取模型训练数据，即前15天的数据X = df_train["索引"].values.reshape(-1,1)X = X-np.min(X)+1Y = df_train["销量"].values.reshape(-1,1)reg = LR().fit(X, Y) #训练模型intercept = pd.Series(reg.intercept_)[0] #截距coef = pd.Series(reg.coef_[0])[0] #自变量系数Y_pre = intercept+ coef*(df_train["索引"]-np.min(df_train["索引"])+1) #训练集的预测值Y_pre_yes_day = reg.predict((df["索引"][list(df["索引"][yes_index])[0]-1]-np.min(df_train["索引"])+1).reshape(-1, 1)) #昨天的预测值mae = mean_absolute_error(Y,Y_pre) #平均绝对误差df["平均绝对误差"][list(df["索引"][yes_index])[0]-1] = mae #保存当天的mae值df["截距"][list(df["索引"][yes_index])[0]-1] = intercept #保存昨天预测模型的截距df["回归系数"][list(df["索引"][yes_index])[0]-1] = coef #保存昨天预测模型的回归系数df["预测销量"][list(df["索引"][yes_index])[0]-1] =list(Y_pre_yes_day)[0][0] #保存昨天预测模型的预测销量
步骤三、将这个表df与初始查出来的数据表data进行merge合并。
df.columns = colums_tempdf.dt = df.dt.astype("datetime64")df.dt = df.dt.astype("datetime64")data.dt = data.dt.astype("datetime64")df2 = pd.merge(data[["dt"]],df,on="dt",how="inner")df2
步骤四、先清空数据表。
import pymysqlcon=pymysql.connect(host="localhost",user="root",password="123456",database="sys",charset="utf8")cur=con.cursor()#执行SQL语句sql = "delete from sales_pre where sales>0"try:cur.execute(sql)con.commit() #执行成功提交数据except:con.rollback() #执行失败回滚#关闭连接cur.close()con.close()
步骤五、追加表
from sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123456@localhost:3306/sys?charset=utf8")df2.to_sql("sales_pre", engine, schema="sys", if_exists='append', index=False,chunksize=None, dtype=None)
为了展示最后的效果，我运行了15天的数据，最终结果如下。
至此，数据准备的环节做完了。
步骤六、powerbi连接mysql数据库。
步骤七、DAX求解昨天销量实际增长率、昨天销量预测增长率、销量增长加速或下降的百分比。
销售额 = sum('查询1'[sales])预测销售额 = sum('查询1'[sales_pre])回归系数 = sum('查询1'[conf])昨天销售额 = calculate('查询1'[销售额],DATEADD('查询1'[dt],-1,day))昨日销量实际增长率 = divide([销售额]-[昨天销售额],[昨天销售额])昨日销量预测增长率 = divide('查询1'[预测销售额]-'查询1'[昨天销售额],'查询1'[昨天销售额])昨日销量实际增长率较预测增长率 = [销量实际增长率]-[销量预测增长率]
步骤八、可视化，效果非常残暴了。
现在来解读这五个图：
第一个图：预测销量与实际销量总体来看比较接近，模型总体可信度高，3月21日出现明显销量异常，需要预警。
第二个图：销量的增速经历了先增长再下降再增长的一个阶段，基本都围绕在每多一天，平均销量就会增加0.6-1.24之间。
第三个图：昨天销量的实际增长率较预测增长率（即过去平均增长率）在3月17日到3月20日比较接近，3月21日到3月23日有轻微上升，23日到27日轻微下降，27日到31日比较接近。
第四个图： 总体来看，销量预测增长的基础从29.1一直增长到40.5；3月17日到3月24日，总量增长较快，而在3月24日到3月31日增长变慢，受3月23日没有按照预期增长的速度增长所影响，需要对3月23日的销售情况进行排查。
第五个图：模型总体mae在2以内，效果较好，稳定性比较可靠，在1.5附近震荡。
至此，解读完毕。