基于关联规则与可平面图的商品摆放规划-----实验报告

基于关联规则与可平面图的商品摆放规划

摘要: 本文先对northwind数据库介绍与数据描述与简单分析(数据异常值处理,订单地址的文本挖掘)，然后对购买的商品使用关联规则算法，进行关联分析与商品的购买情况分析，由关联规则的发现结果，使用图论方法分析商品的摆放图。

关键词: Northwind数据库；关联规则；可平面图；

1. Northwind数据库数据介绍

图1 Northwind数据库的安装文件

       执行文件中的SQL语句，此时在你的SQL Server中就会看到Northwind数据库了。
  它有8张表，涉及客户、商品、订单。

1.1 关系型数据库

虽然网状数据库和层次数据库已经很好的解决了数据的集中和共享问题，但是在数据库独立性和抽象级别上扔有很大欠缺。用户在对这两种数据库进行存取时，仍然需要明确数据的存储结构，指出存取路径。而关系型数据库就可以较好的解决这些问题。
关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。
①　Categories: (食品类别表)
种类表
相应字段:
CategoryID :类型ID；
CategoryName:类型名;
Description:类型说明;
Picture:产品样本
主键：CategoryID
主键约束名称：PK_Categories
关系说明：该表主要用来保存食品种类信息的数据。
②　CustomerCustomerDemo:客户类型表1
相应字段：
CustomerID：客户ID；
CustomerTypeID：客户类型ID
组合主键：CustomerID、CustomerTypeID
主键约束名称：PK_CustomerCustomerDemo
外键：
1.列 CustomerTypeID引用表CustomerDemographics(CustomerTypeID)
2.列 CustomerID引用表Customers(CustomerID)
外键约束名称：
FK_CustomerCustomerDemo和FK_CustomerCustomerDemo_Customers
关系说明：该表主要用来保存消费者ID和消费者类别ID信息的数据。
③　CustomerDemographics:客户类型表2
相应字段：
CustomerTypeID：客户类型ID；
CustomerDesc：客户描述
主键：CustomerTypeID
主键约束名称：PK_CustomerDemographics
关系说明：该表主要用来存储消费者类别说明信息。
④　Customers:客户表
相应字段：
CustomerID:客户ID；
CompanyName:所在公司名称；
ContactName:客户姓名；
ContactTitle:客户头衔；
Address：联系地址；
City：所在城市;
Region:所在地区;
PostalCode:邮编;
Country:国家
Phone:电话；
Fax：传真
主键：CustomerID
主键约束名称：PK_Customers
关系说明：该表主要用来存储消费者详细信息的数据表。
⑤　Employees：员工表
相应字段：
EmployeeID:员工代号;
LastName + FirstName:员工姓名;
Title:头衔;
TitleOfCourtesy:尊称;
BirthDate:出生日期;
HireDate:雇用日期;
Address：家庭地址；
City:所在城市;
Region:所在地区;
PostalCode:邮编;
Country:国家用；
HomePhone：宅电;
Extension:分机;
Photo:手机;
notes:照片；
ReportsTo：上级;
PhotoPath:照片
主键：EmployeeID
主键约束名称：PK_Employees
外键：
1.列 ReportsTo引用表Employees(EmployeeID)
外键约束名称
FK_Employees_Employees
关系说明：该表主要用来存储雇员的详细信息数据表。

⑥　EmployeeTerritories：员工部门表
相应字段：
EmployeeID:员工编号；
TerritoryID：部门代号
主键：EmployeeID、TerritoryID
主键约束名称：PK_EmployeeTerritories
外键：
1.列 EmployeeID引用表Employees(EmployeeID)
2.列 TerritoryID引用表Territories (TerritoryID)
外键约束名称：
FK_EmployeeTerritories_Employees和FK_EmployeeTerritories_Territories
关系说明：该表主要用来存储雇员ID和其对应的销售区域ID。

⑦　Order Details：订单明细表
相应字段：
OrderID:订单编号；
ProductID：产品编号；
UnitPrice：单价;
Quantity:订购数量；
Discount:折扣
主键：OrderID、ProductID
主键约束名称：PK_Order_Details
外键：
1.列 OrderID引用表Orders(OrderID)
2.列 ProductID引用表Products(ProductID)
外键约束名称：
FK_Order_Details_Orders和FK_Order_Details_Products
关系说明：该表主要用来存储订单价格信息的数据表
1 组合、主键、聚集索引在 OrderID 和ProductID 上定义。
2 在 OrderID 上也有两个非聚集索引。
3 在 ProductID 上也有两个非聚集索引。
4 UnitPrice 的 CHECK 约束被定义为 (UnitPrice >= 0)。
5 Quantity 的 CHECK 约束被定义为 (Quantity > 0)。
表级别的 CHECK 约束被定义为 (Discount >= 0 和 Discount < = 1)。

⑧　Orders:订单表
相应字段：
OrderID:订单编号；
CustomerID:客户编号;
EmployeeID:员工编号；
OrderDate：订购日期；
RequiredDate：预计到达日期;
ShippedDate:发货日期;
ShipVia:运货商;
Freight:运费;
ShipName:货主姓名；
ShipAddress:货主地址
ShipCity:货主所在城市；
ShipRegion：货主所在地区；
ShipPostalCode：货主邮编;
ShipCountry:货主所在国家
主键：OrderID
主键约束名称：PK_Orders
外键：
1.列 CustomerID引用表Customers(CustomerID)
2.列 EmployeeID引用表Employees(EmployeeID)
3. .列 ShipVia引用表Shippers(ShipperID)
外键约束名称
FK_Orders_Customers，FK_Orders_Employees和FK_Orders_Shippers
关系说明：该表主要用来存储订单详细信息的数据
在 CustomerID上有两个非聚集索引，在EmployeeID 上有两个非聚集索引，ShipVia 上也有一个非聚集索引。

⑨　Products:产品表
相应字段：
ProductID:产品ID；
ProductName：产品名称；
SupplierID：供应商ID；
CategoryID：类型ID；
QuantityPerUnit：数量；
UnitPrice：单价;
UnitsInStock：库存数量；
UnitsOnOrder：订购量；
ReorderLevel：再次订购量;
Discontinued：中止
主键：ProductID
主键约束名称：PK_Products
外键：
1.列 CategoryID引用表Categories(CategoryID)
2.列 SupplierID引用表Suppliers(SupplierID)
外键约束名称
FK_Products_Categories和FK_Products_Suppliers
关系说明：该表主要用来存储产品详细信息的数据表。
1．在 SupplierID 上有两个非聚集索引。
2．在 CategoryID 上有两个非聚集索引。
3．UnitPrice 的 CHECK 约束被定义为 (UnitPrice >=)。
4．UnitsInStock 的 CHECK 约束被定义为 (UnitsInStock >=)
5．UnitsOnOrder 的 CHECK 约束被定义为 (UnitsOnOrder >=)。
6．ReorderLevel 的 CHECK 约束被定义为 (ReorderLevel >=)。

⑩　Region:地区表
相应字段：
RegionID：地区ID；
RegionDescription：地区描述
主键：RegionID
主键约束名称：PK_Region
关系说明：该表主要用来存储区域信息数据。
○11　Shippers:运货商
相应字段：
ShipperID:运货商ID；
CompanyName：公司名称;
Phone:联系电话
主键：ShipperID
主键约束名称：PK_Shippers
关系说明：该表主要用来存储托运公司的详细信息数据。
○12　Suppliers：供应商表
相应字段：
ShipperID：供应商ID；
CompanyName：供应商姓名；
Phone；联系电话
主键：SupplierID
主键约束名称：PK_Suppliers
关系说明：该表主要用来存储供应商详细信息数据。

○13　Territories：地域表
相应字段：
TerritoryID:地域编号；
TerritoryDescription：地域描述；
RegionID：地区编号
主键：TerritoryID
主键约束名称：PK_Territories
外键：
1.列 RegionID引用表Region(RegionID)
外键约束名称
FK_Territories_Region
关系说明：该表用来存储销售区域详细说明信息的数据。

1.2 视图分析：

图2 用数据分析服务创建数据库视图

观察创建的视图：

图四 观察数据库表之间的关联

1.2.1．Alphabetical list of products未过期食品的详细信息及其所属的类别。
1.2.2．Category Sales for 1997显示种类食品在1997年的实际销售部。
1.2.3．Current Product List显示未过期食品的信息。
1.2.4．Customer and Suppliers by City所有消费者和供货商的信息。
1.2.5．Invoices 按照订单发货的详细信息。
1.2.6．Order Details Extended订单明细的详细信息。
1.2.7．Order Subtotals 每单的实际销售额
1.2.8．Orders Qry 有订单的客户的详细订单信息及客户信息
1.2.9．Product Sales for 1997 每种食品在1997实际销售额以及食品的详细信息
1.2.10．Products Above Average Price 单位售价大于所有食品平均售价的食品清单。
1.2.11．Products by Category 未过期食品的存货情况（按照商品分类）
1.2.12．Quarterly Orders 1997年有订单的客户信息
1.2.13．Sales by Category 1997年订单食品的详细情况
1.2.14．Sales Totals by Amount 1997年每单销售额大于2500的订单的信息
1.2.15．Summary of Sales by Quarter 1997年已发货订单的发货时间
1.2.16．Summary of Sales by Year 1997年已发货订单的发货时间

1.3.数据表之间的关系展示

数据较多的表：
1.3.1.Products:产品表
相应字段：
ProductID:产品ID；
ProductName：产品名称；
SupplierID：供应商ID；
CategoryID：类型ID；
QuantityPerUnit：数量；
UnitPrice：单价;
UnitsInStock：库存数量；
UnitsOnOrder：订购量；
ReorderLevel：再次订购量;
Discontinued：中止

图五 产品表
1.3.2.　Orders:订单表
相应字段：
OrderID:订单编号；
CustomerID:客户编号;
EmployeeID:员工编号；
OrderDate：订购日期；
RequiredDate：预计到达日期;
ShippedDate:发货日期;
ShipVia:运货商;
Freight:运费;
ShipName:货主姓名；
ShipAddress:货主地址
ShipCity:货主所在城市；
ShipRegion：货主所在地区；
ShipPostalCode：货主邮编;
ShipCountry:货主所在国家

图六订单表
1.3.3.Order Details：订单明细表
相应字段：
OrderID:订单编号；
ProductID：产品编号；
UnitPrice：单价;
Quantity:订购数量；
Discount:折扣

图七 订单明细表
对每一列数据进行统计，包括计数，均值，std，各个分位数等。

图八 订单明细表的数据描述
单价的平均值为26.21
订购数量的平均值为23
折扣 的平均值为26.21

1.4 数据的抽取

pyodbc是ODBC的一个python封装，它允许任何平台上的python具有使用ODBC API的能力。那意味着，pyodbc是python语言与ODBC的一条桥梁。开放数据库连接（Open Database Connectivity，ODBC）是为解决异构数据库间的数据共享而产生的，现已成为WOSA(The Windows Open System Architecture(Windows开放系统体系结构))的主要部分和基于Windows环境的一种数据库访问接口标准ODBC 为异构数据库访问提供统一接口，允许应用程序以SQL 为数据存取标准，存取不同DBMS管理的数据；使应用程序直接操纵DB中的数据，免除随DB的改变而改变。用ODBC 可以访问各类计算机上的DB文件，甚至访问如Excel 表和ASCI I数据文件这类非数据库对象。
所有的SQL语句都用ODBC.execute函数运行或由get_df_from_db_1方法直接返回数据架构。

1.5 数据预处理

对数据中缺失值选用合适的方法填充：对数据中的空值处填入平均值

1.6 购买者 国家的文本挖掘，词频分析：

[(‘USA’, 13), (‘Germany’, 11), (‘France’, 11), (‘Brazil’, 9), (‘UK’, 7), (‘Mexico’, 5), (‘Spain’, 5), (‘Venezuela’, 4), (‘Canada’, 3), (‘Argentina’, 3), (‘Italy’, 3), (‘Sweden’, 2), (‘Switzerland’, 2), (‘Austria’, 2), (‘Portugal’, 2), (‘Belgium’, 2), (‘Denmark’, 2), (‘Finland’, 2), (‘Ireland’, 1), (‘Norway’, 1), (‘Poland’, 1)]

图九 词云图

图十 单价的分布图

打折的比例：

图十一 打折的比例

图十二 单价-折扣图

图十三 数量-折扣图

通过以上发现：‘
折扣的类型分为五种：0 0.05 0.10 0.15 0.20 0.25
不打折的概率为61.3%，其他折扣的占比基本相同。
大部分商品的单价为零到50
从数量-折扣图中可见：商品的折扣类型与数量没有明显的关系。

2. 关联规则算法与平面图论分析

2.1 Apriori算法

Apriori算法是第一个关联规则挖掘算法，也是最经典的算法。它利用逐层搜索的迭代方法找出数据库中项集的关系，以形成规则，其过程由连接（类矩阵运算）与剪枝（去掉那些没必要的中间结果）组成。该算法中项集的概念即为项的集合。包含K个项的集合为k项集。项集出现的频率是包含项集的事务数，称为项集的频率。如果某项集满足最小支持度，则称它为频繁项集，即挖掘频繁项集，通过连接产生候选项及其支持度，通过剪枝生成频繁项集

2.2 算法实施

对购买的商品使用关联规则算法 进行关联分析：

2.2.1总数据描述：

Order Details表中ID为10248至11077的829次购买记录

2.2.2：

在所有记录中选择购买前十商品的设置为一项频繁集[59,60,31,24,56,62,41,75,2,16]：
频繁集 及其支持度 产品名称
59 0.065139 54 aclette Courdavault
60 0.061520 51 Camembert Pierrot
31 0.061520 51 Gorgonzola Telino
24 0.061520 51 Guaraná Fantástica
56 0.060314 50 Gnocchi di nonna Alice
62 0.057901 48 Tarte au sucre
41 0.056695 47 Jack’s New England Clam Chowder
75 0.055488 46 Rh?nbr?u Klosterbier
2 0.053076 44 Chang
16 0.051869 43 Pavlova

一项频繁集候选产生二项频繁集（所有出现次数大于3的）：
频繁集 出现次数
[60, 31] 4
[59, 56] 4
[59, 2] 5
[31, 2] 5
[59, 16] 4
[60, 16] 4
[31, 16] 7
[62, 16] 4
[2, 16] 4
注：没有支持度大于三次的三项频繁集。

由以上数据生成关联规则并计算置信度：
60-》31 0.07
31-》60 0.07
59-》56 0.07407
56-》59 0.08
59-》2 0.0925
2 –》59 0.1136
31-》2 0.09803
2 –》31 0.09803
59-》16 0.09302
16-》59 0.09302
60-》16 0.07843
16-》60 0.07843
31-》16 0.13725
16-》31 0.16279
62-》16 0.83333
16-》62 0.09302
2 -》16 0.090909
16-》2 0.093023

2.3 图论分析：

2.3.1 数据转换

将以上的二项频繁集转换为无向图图的关系：
（如果两个商品的集合属于频繁集，则将两个点联通，如点16与60在图上邻接）

图十四 商品频繁集的联通图

从上图可见：
图的度序列为{5,3,3,3,2,1,1,0,0,0}
对应的点为{16 ,59,2,31,60,62,56,75,41,24}
商品16所联通的点最多，即点16的度数最大。在摆放商品时可将商品16放在中间。

2.3.2 平面化

可平面图(planar graph)是一类特殊的图，指同构于某一平面图的图。如果一个图能够画在平面上，使得顶点集合及边集合分别是相同的，而如果边相交仅在边的端点处，则称这个图是可嵌入平面的，或称作可平面图(planar graph)；否则称作不可平面图，可平面图G的这样一种画法称为G 的一个平面嵌入(planar embedding) 。库拉托夫斯基定理给出了可平面图的特征，其内容为：图G是可平面图当且仅当G没有同胚于完全图K5和完全二部图K3,3的子图，同胚于K5和K3,3的图称为库拉托夫斯基图。这一定理是由库拉托夫斯基(Kuratowski，K.)于1930年证明的，若可平面图G不是任何同阶其他可平面图的子图，则称G为极大可平面图，极大可平面图所对应的平面图的每个面都必为三角形；同构于外平面图的图称为外可平面图；3正则的可平面图称为3正则平面图；G是外可平面的当且仅当G没有与完全图K4同胚的子图 。
（欧拉公式）平面图： 设G是无向连通平面图，具有n 个顶点，m条边和r个面，则 n-m+r=2。

图十五 平面化的商品摆放图

在百度搜索中也能发现Pavlova的流行性，在所有的十种商品中只有16号商品Pavlova蛋糕有较多的网页信息。
Pavlova 是一款拥有奇妙口感的蛋白奶油蛋糕，从1930开始，在新西兰和澳大利亚开始风靡，虽然两国都争执是自己发明了这款美味的甜点，但是我认为，承认这款甜点人人都爱，已经是最大的胜利。
这款蛋糕的名字源自俄罗斯芭蕾舞蹈演员Anne Pavlova, 据说当时她正在巡回演出，蛋糕师看到了美丽的Pavlova之后，创造了这款美味的甜点来寓意赞美她的美丽与她的舞蹈。
制作Pavlova的材料很简单，也很常见，但是却可以风靡一时，诀窍在于她的奇妙口感。
和一般的蛋白饼干一样，所有的Meringue Batter烘培过后都会有一层酥脆的外皮，触感好像很坚硬，其实酥脆的一碰就会裂开，但是Pavlova中因为有食醋/柠檬汁的加入，使得这款Meringue Cake与众不同，在酥脆的外壳包裹下，是像棉花糖一样柔软的内芯。
Pavlova一般与奶油，新鲜水果搭配食用，上面这张成品图可以很清晰的用视觉体验显示出Pavlova的奇妙口感：
新鲜时令水果的酸甜，奶油的甜腻，配上酥脆到一碰就裂开的外皮，再加上像棉花糖一样柔软甜蜜的内芯，所有的这一切，当你一口吃下以后，在口腔中用舌头一抿便融化了，带给味蕾奇妙的体验……

图十六 Pavlova 蛋糕

图十七 Pavlova 的购买拟合曲线
输出结果：
intercept_:10251.837
coef_:19.897
Mean squared error: 330.178
Variance score: 0.994
score: 0.994
由图可见拟合效果较好，准确率为0.994，拟合直线的斜率为20.073，所以大约平均每20个顾客就有一个购买Pavlova蛋糕。
同时对其他的几种商品进行拟合得到，其他的商品也横好拟合。
而对购买小一点的进行拟合
如购买数量第50位的14号商品 进行拟合的结果为
intercept_:10208.108
coef_:33.148
Mean squared error: 5762.650
Variance score: 0.884
score: 0.884

图十八 Tofu的购买拟合曲线

2.4结果分析

1.折扣的类型分为五种：0 0.05 0.10 0.15 0.20 0.25，受数量单价的影响较小。
2.前十种购买量最多的商品中出现种类最多的是Cheeses类，
3.并且购买量高的商品有更强的规律性，用直线进行最小二乘拟合的结果得分相对也更高。
4.并且通过关联规则发现：购买Pavlova蛋糕与 Gorgonzola Telino奶酪有最大的支持度。
5.商品的摆放优化如“图十五”所示

3.结论总结

利用SQL SERVER ,python，Matlab等软件，实验实现了构建数据仓库，数据清洗方法，了解数据挖掘的几个经典算法思想；通过本次数据挖掘实验，体会了数据清洗的重要性，认识到数据挖掘算法的重要性，了解了数据立方体的构建。

部分代码与参考文献：

import pandas as pd
import pyodbc
import matplotlib.pyplot as plt
plt.rcParams[‘font.sans-serif’]=[‘SimHei’] #用来正常显示中文标签
plt.rcParams[‘font.sans-serif’]=‘SimHei’

数据加载的方法：
右键属性编辑密码，使用用户名密码连接数据库。
connection = pyodbc.connect(SERVER=“127.0.0.1”,
UID=“sa”,
PWD=“1234”,
DATABASE=“Northwind”,
DRIVER=’{SQL Server}’ )#数据库的连接字符串

def get_df_from_db_1(sql):
return pd.read_sql(sql, connection)
df=get_df_from_db_1('SELECT * from [Order Details] ')#执行选择命令
print(df)#打印dataframe

data=df[‘UnitPrice’]
data.hist(figsize=(8, 4), bins=100)
plt.show()
plt.Figure(figsize=(8,4))
x,y =df[‘Quantity’].values ,df[‘Discount’].values
plt.scatter(x,y,color = ‘r’,s=1,marker=‘o’)
#坐标设置
plt.xlabel(“质量”)
plt.ylabel(“折扣”)
#plt.legend()
plt.show()
df=get_df_from_db_1(‘SELECT * from [Order Details]’)
print(df)
groups=df.groupby([‘ProductID’]).count()
groups=groups.sort_values(by = ‘OrderID’,axis = 0,ascending = False)
print(groups)
for i in range(1,10):
print(groups.iloc[i,:])
print(groups[‘OrderID’]/2155)
TEN= [59,60,31,24,56,62,41,75,2,16]
a=[]
for i in range(0,9):
a.append(TEN[i])
a.append(TEN[i+1])
print(a)
i=0
for k in range(10248,11077):
df=get_df_from_db_1(‘SELECT ProductID from [Order Details] where OrderID=’+str(k))
b=df[‘ProductID’].values
if set(a) < set(b) :
i=i+1
print(i)
a=[]
items=TEN
N = len(items)
for i in range(2 ** N): #子集个数，每循环一次一个子集
combo = []
for j in range(N): #用来判断二进制下标为j的位置数是否为1
if(i>>j)%2:
combo.append(items[j])
#print(combo)
J=0
for k in range(10248,11077):
df=get_df_from_db_1(‘SELECT ProductID from [Order Details] where OrderID=’+str(k))
b=df[‘ProductID’].values
if set(combo) < set(b) :
J=J+1
if J>3:
print(combo)
print(J)
import pyodbc
class ODBC:
def init(self,server,uid,pwd,db,DRIVER=’{SQL Server}’):
self.server = “127.0.0.1”
self.uid = “sa”
self.pwd = “1234”
self.db = “Northwind”
self.DRIVER = DRIVER

def GetConnect(self):
    if not self.db:
        raise(NameError,'没有设置数据库信息')
    self.conn = pyodbc.connect(SERVER=self.server,UID=self.uid,PWD=self.pwd,DATABASE=self.db,DRIVER=self.DRIVER)
    cur = self.conn.cursor()
    if not cur:
        raise(NameError,'连接数据库失败')
    else:
        print("数据库连接成功")
        return cur

def ExecQuery(self,sql):
    cur = self.GetConnect()
    cur.execute(sql)
    resList = cur.fetchall()
    self.conn.close()
    return resList

def ExecNonQuery(self,sql):
    cur = self.GetConnect()
    cur.execute(sql)
    self.conn.commit()
    self.conn.close()

def main():
ms = ODBC(server=‘localhost’,uid=’’, pwd=’’, db=“new_estate_dalian”)
#sql = ms.ExecQuery('SELECT top 5 * from [Orders] ')
sql = ms.ExecQuery('SELECT Country from [Customers] ')

print(sql)

if name == ‘main’:
main()
#画饼图
labels = [‘0.00’,‘0.05’,‘0.10’,‘0.15’,‘0.20’,‘0.25’]
sizes = [1317,185,173,161,157,154]
explode = (0,0,0,0,0,0)
plt.pie(sizes,explode=explode,labels=labels,autopct=’%1.1f%%’,shadow=False,startangle=150)
plt.title(“折扣百分比”)
plt.show()
plt.axis(‘equal’)
connection = pyodbc.connect(SERVER=“127.0.0.1”,
UID=“sa”,
PWD=“1234”,
DATABASE=“Northwind”,
DRIVER=’{SQL Server}’ )

def get_df_from_db_1(sql):
return pd.read_sql(sql, connection)
a=[]
‘’’
for k in range(10248,10251):
df=get_df_from_db_1(‘SELECT ProductID from [Order Details] where OrderID=’+str(k))
b=df[‘ProductID’].values
print(df[‘ProductID’].values)
a.append(b)
print(a[1])
‘’’

df=get_df_from_db_1(‘SELECT * from [Order Details]’)
print(df)
groups=df.groupby([‘ProductID’]).count()#ProductID
groups=groups.sort_values(by = ‘OrderID’,axis = 0,ascending = False)
print(groups)
for i in range(0,60):
print(groups.iloc[i,:])
#groups/829
print(groups[‘OrderID’])

最小二乘法：
import pandas as pd
import pyodbc
import matplotlib.pyplot as plt
plt.rcParams[‘font.sans-serif’]=[‘SimHei’] #用来正常显示中文标签
plt.rcParams[‘font.sans-serif’]=‘SimHei’
connection = pyodbc.connect(SERVER=“127.0.0.1”,
UID=“sa”,
PWD=“1234”,
DATABASE=“Northwind”,
DRIVER=’{SQL Server}’ )

def get_df_from_db_1(sql):
return pd.read_sql(sql, connection)
df=get_df_from_db_1(‘SELECT * from [Order Details] where ProductID=16’)
print(df)
import numpy as np
from sklearn import datasets , linear_model
from sklearn.metrics import mean_squared_error , r2_score
from sklearn.model_selection import train_test_split
import matplotlib
import matplotlib.pyplot as plt
import sklearn
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
import matplotlib as mpl
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd

X = np.arange(0,43,1)
print(X)
X = X.reshape(X.shape[0],1)
y = df[‘OrderID’].values
print(X)
print(y)
X_train , X_test , y_train ,y_test = train_test_split(X,y,test_size=0.2,random_state=42)
#导入模型，模型参数默认
LR = linear_model.LinearRegression()
#训练模型
LR.fit(X_train,y_train)
#预测模型LR.predict(X_test),此时输出类别数据
#打印截距
print(‘intercept_:%.3f’ % LR.intercept_)
#打印模型系数
print(‘coef_:%.3f’ % LR.coef_)
#打印均方误差值
print(‘Mean squared error: %.3f’ % mean_squared_error(y_test,LR.predict(X_test)))##((y_test-LR.predict(X_test))**2).mean()
#打印R-平方
print(‘Variance score: %.3f’ % r2_score(y_test,LR.predict(X_test)))
#1-((y_test-LR.predict(X_test))**2).sum()/((y_test - y_test.mean())**2).sum
#打印准确率accuracy
print(‘score: %.3f’ % LR.score(X_test,y_test))
plt.scatter(X_test , y_test ,color =‘green’)
plt.plot(X_test ,LR.predict(X_test) ,color=‘red’,linewidth =3)
plt.show()

参考文献
[1] https://blog.csdn.net/pan15125284/java/article/details/91864509
[2] https://blog.csdn.net/lixinkuan328/java/article/details/95381694
[3https://home.meishichina.com/space-1308807-do-blog-id-532203.html
[4]https://baike.baidu.com/item/%E5%8F%AF%E5%B9%B3%E9%9D%A2%E5%9B%BE/19138688?fr=aladdin
[5] https://wenku.baidu.com/view/1e73ba671ed9ad51f01df242.html